Help:Programmatically Adding Checkboxes with Captions

[josh@shift4.com]

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@shift4.com wrote:
>
> 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

[josh@shift4.com]

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