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
Dave Peterson - 06 Feb 2007 12:57 GMT
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.
> I do not know Visual Basic unfortunately. I started out making my
> Excel table and just wanted to put in Multiple Checkboxes. It would
[quoted text clipped - 72 lines]
>
> Help will be much appreciated. Thanks

Signature
Dave Peterson
Bob Phillips - 06 Feb 2007 12:59 GMT
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

Signature
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
>I do not know Visual Basic unfortunately. I started out making my
> Excel table and just wanted to put in Multiple Checkboxes. It would
[quoted text clipped - 72 lines]
>
> Help will be much appreciated. Thanks
leonie.meiring@agr.ecprov.gov.za - 09 Feb 2007 13:35 GMT
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!