Checkbox Controlsource in VBA

[rsrc1147]

Hello!

I have a userform which gets populated by checkboxes on the fly. For each cel in a list on a sheet, a checkbox is made. I am trying to get the control source for each checkbox to be the cell in the next column to the current cel selected. Unfortunatley, the controlsource just isn't working in the code I have written. Here's my code:

for each cel in offices

set mylabel = userform1.controls.add("Forms.label.1")
mylabel.caption = cel.value

Set myCB = userform1.controls.add("forms.checkbox.1")
myCB. controlsource= cel.offset(0,1)

Like I say, it all works fine except for the control source.

Incidentally, what if I wanted to add code to each checkbox's change event - is this possible by adding something to this code? So If I already had a sub with the code I wanted to execute, could I add change event code to the checkbox I just made?

Thank you for anyone who can help with all this - I think you ROCK!


James

 

[ktab]

try it like that (untested but probably what you need)
myCB. controlsource= cel.offset(0,1).address

EDIT:

the controlsource in properties is like
sheetname & "!" & range address.
So in order to work it should look like:
E.g.: if your sheet's name (not codename) is Sheet1 then "Sheet1!" & cel.offset(0,1).address

 

[rsrc1147]

Thanks that works great. Any ideas about being able to add code to the checkbox?

Thank you very much!

James

 

[ktab]

Are you familiar with classes?

 

[rsrc1147]

Hmm.. not really. I probably should be though, right?

 

[ktab]

Try this example:
At userform's initialize event:

Option Explicit
Dim CheckBoxes() As New Class1

Private Sub UserForm_Initialize()
Dim CCount As Long, c As Control
'place here the code that creates your checkboxes, or elsewhere,
'but surelly before the following procedure
CCount = 0
For Each c In Me.Controls
    If TypeOf c Is MSForms.CheckBox Then
        CCount = CCount + 1
        ReDim Preserve CheckBoxes(1 To CCount)
        Set CheckBoxes(CCount).CheckGroup = c
    End If
Next c

End Sub

Now create a new class module; leave name as class1.
Paste following code

Option Explicit

Public WithEvents CheckGroup As MSForms.CheckBox

Private Sub CheckGroup_Change()
MsgBox CheckGroup.Value
End Sub

Replace msgbox e.c.t. with your procedure.

 

[rsrc1147]

Dude you rock! Thank you so much!

 

[Norie]

I see Kostas has provided a solution.

But I would like to make a suggestion - use a listbox instead of adding controls programatticaly.