Help:Programmatically Adding Checkboxes with Captions

[josh]

Programmatically Adding Checkboxes with Captions

Here is what I am trying to accomplish:

I have "Sheet1" which the user has the ability to select options
etc.

Once they are done, a vbscript is triggered:
1) creates a new worksheet.
2) Adds a dynamic number of checkboxes(depends on user interaction from
sheet1)
This is where I'm having trouble. I can use a loop and create as
many checkboxes as needed, but I can't add a caption to them. Here
is the code I'm using:

'VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV

If dePartment = "7" Then
sopSteps = 46
Else
sopSteps = 47
End If

ThisWorkbook.Sheets.Add

For myCounter = 1 To sopSteps

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False,
_
DisplayAsIcon:=False, Left:=10, Top:=spaceBetweenCheckboxes,
Width:=108, Height:= _
19.5).Select

spaceBetweenCheckboxes = spaceBetweenCheckboxes + 25

Next myCounter

'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The checkboxes are created and added to the sheet just fine, but if I
try to add 'Caption:="test"', for example, I get an error
telling me this is not valid. (by the way I am going to use an array
to assign the captions - but this is an example to keep it simple).

Is there a parameter for this method that lets me add a caption? Any
ideas or other ways to add a checkbox programmatically that will let me
set the caption?

Thanks in advance
Josh

 

[Dave Peterson]

This skinnied down version worked ok for me:

Option Explicit
Sub testme2()

Dim sopSteps As Long
Dim spaceBetweenCheckboxes As Double
Dim OLEObj As OLEObject
Dim myCounter As Long

sopSteps = 5
spaceBetweenCheckboxes = 14.4

ThisWorkbook.Sheets.Add

For myCounter = 1 To sopSteps

Set OLEObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=10, _
Top:=spaceBetweenCheckboxes, _
Width:=108, Height:=19.5)

OLEObj.Object.Caption = "Hi there"

spaceBetweenCheckboxes = spaceBetweenCheckboxes + 25

Next myCounter
End Sub

 

[josh]

Thanks a lot Dave! I haven't done much scripting in Excel and I'm not
too familiar with OLEObjects. I was able to implement the array and it
works like a champ. Thanks again,

Josh