Multiple Checkboxes

[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("D555").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

 

[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.

 

[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("D555").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)

 

[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!