Checkbox Controlsource in VBA

rsrc1147

Board Regular
Joined
Jul 9, 2002
Messages
100
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:

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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
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

Board Regular
Joined
Jul 9, 2002
Messages
100
Thanks that works great. Any ideas about being able to add code to the checkbox?

Thank you very much!

James
 

rsrc1147

Board Regular
Joined
Jul 9, 2002
Messages
100
Hmm.. not really. I probably should be though, right?
 

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
Try this example:
At userform's initialize event:
Code:
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

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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
I see Kostas has provided a solution.

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

Forum statistics

Threads
1,181,416
Messages
5,929,790
Members
436,694
Latest member
dpatete

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top