Multiple Checkboxes

L

leonie.meiring

I do not know Visual Basic unfortunately. I started out making my
Excel table and just wanted to put in Multiple Checkboxes. It would
have helped if one could just copy them like regular text. So I
searched the web and found the following code:

To add a control to a worksheet programmatically, use the Add method
of the OLEObjects collection, or the AddOLEObject method of the Shapes
collection. For example, the following code adds a column of check
boxes to a worksheet. To see the code in action, make sure you are in
run mode, and then click the Add Checkboxes button on the Checkboxes
worksheet in ActiveXL.xls.

Private Sub cmdAddCheck_Click()
Dim ws As Worksheet
Dim cellUnder As Range
Dim cb As OLEObject
Set ws = ActiveSheet

' Must deactivate clicked control for code to run.
ws.Range("a1").Activate
For Each c In ws.Range("DailyTasks")
Set cellUnder = c.Offset(0, 1)
' The next line adds the control and sizes and
' positions the control over a cell in the
' DailyTasks named range.
Set cb = ws.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Left:=cellUnder.Left + 1, _
Top:=cellUnder.Top + 1, _
Width:=cellUnder.Width - 2, _
Height:=cellUnder.Height - 2)
With cb
'This lets each check box stay with its row during sorts.
.Placement = xlMove
With .Object
.BackColor = &H80000005
.BackStyle = fmBackStyleTransparent
.Caption = ""
End With
End With
Next
' Reactivate the clicked control.
cmdAddCheck.Activate
End Sub

It sounds Greek to me but I tried it and as soon as I click Run it
gives an error:
Run-Time error '1004' Application defined or object-defined error.

Then I found the following code from a search (code belonging to Dave
Peterson) which worked excellent but the only thing is, I do not want
the True and False text that appears in the adjacent column when the
checbox is clicked. How do I remove that from the Visual Basic code?

Option Explicit
Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range


With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For Each myCell In ActiveSheet.Range("D5:D55").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Offset(0,
1).Address(external:=True)
.Caption = "" 'or whatever you want
End With
End With
Next myCell
End With
End Sub

Help will be much appreciated. Thanks
 
D

Dave Peterson

To remove the linkedcell business (using the checkbox from the Forms toolbar),
just remove this line:

.LinkedCell = myCell.Offset(0, 1).Address(external:=True)

If you decide that you like the linked cell (it could be nice for counting the
number of checked boxes), you could hide that column or even give it a custom
number format of:
;;;
Three semicolons. This makes the cell look empty, but you can still see the
value in the formula bar.



======
For your other problem...

It sounds like you're running xl97.

rightclick on the button that owns this code and choose properties. Change the
..takefocusonclick property to false.

It's a bug in xl97, but it was fixed in xl2k.
 
B

Bob Phillips

Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range


With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For Each myCell In ActiveSheet.Range("D5:D55").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.Caption = "" 'or whatever you want
End With
End With
Next myCell
End With
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
L

leonie.meiring

Thanks very much Dave and Bob for the code - it worked!

As for the first code, Dave, there wasn't a button associated with the
code - I just copied and pasted it into the Visual Basic Module.
However, after your advice I tried putting in a command button in the
Active sheet and even in a User Form and there I changed
the .takefocusonclick property to false but on running the code
associated with the button I still got the same 1004 error. We are
running Excel 2002 and we have the latest service packs loaded.

But thanks anyway, the other code works great!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top