creating a checkbox control on a user form using VBA
 

Hi,

I would like to create a form containing checkboxes 'on-the-fly' from a list
of values in a spreadsheet range, so after

"userform_initialise()"

i want something like

"do until the last cell in the range ="""

"me.controls.add...... a checkbox named after the value of the active cell"

"activecell.offset(1,0)"

"loop"

the bit i can't do is create the checkbox control on the userform (i guess
i'd also liketo control whereabouts on the form the controls go as they are
added).

greatly for any help,

Tim

creating a checkbox control on a user form using VBA
 

Tim,

Creating checkboxes dynamically is easy enough as this code shows

With Me

.Controls.Add ("forms.Checkbox.1")
With .Controls("Checkbox1")
.Caption = "Hello"
.Name = "chkHello"
.Top = 12
End With

.Controls.Add ("forms.Checkbox.1")
With .Controls("Checkbox1")
.Caption = "Goodbye"
.Name = "chkGoodbye"
.Top = 24
End With

End With

You may have to set a lot more properties though to get it exactly as you
want.

However, there is another issue you need to manage, and that is there is no
event code to handle the checkbox status, so you will need to add this as
well. Take a look at Chip's site to get some ideas on adding code
dynamically

http://www.cpearson.com/excel/vbe.htm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tim Marsh" wrote in message
...
Hi,

I would like to create a form containing checkboxes 'on-the-fly' from a
list
of values in a spreadsheet range, so after

"userform_initialise()"

i want something like

"do until the last cell in the range ="""

"me.controls.add...... a checkbox named after the value of the active
cell"

"activecell.offset(1,0)"

"loop"

the bit i can't do is create the checkbox control on the userform (i guess
i'd also liketo control whereabouts on the form the controls go as they
are
added).

greatly for any help,

Tim

creating a checkbox control on a user form using VBA
 

Thanks Bob. I'm thinking of using column offset to set checkbox status as
the adjacent column contains values, that - if present - should set the
control to 'true'. i'm sure that if i can't work out how to do this, i'll
be posting back on here soon!

Cheers,

Tim

"Bob Phillips" wrote in message
...
Tim,

Creating checkboxes dynamically is easy enough as this code shows

With Me

.Controls.Add ("forms.Checkbox.1")
With .Controls("Checkbox1")
.Caption = "Hello"
.Name = "chkHello"
.Top = 12
End With

.Controls.Add ("forms.Checkbox.1")
With .Controls("Checkbox1")
.Caption = "Goodbye"
.Name = "chkGoodbye"
.Top = 24
End With

End With

You may have to set a lot more properties though to get it exactly as you
want.

However, there is another issue you need to manage, and that is there is
no
event code to handle the checkbox status, so you will need to add this as
well. Take a look at Chip's site to get some ideas on adding code
dynamically

http://www.cpearson.com/excel/vbe.htm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tim Marsh" wrote in message
...
Hi,

I would like to create a form containing checkboxes 'on-the-fly' from a
list
of values in a spreadsheet range, so after

"userform_initialise()"

i want something like

"do until the last cell in the range ="""

"me.controls.add...... a checkbox named after the value of the active
cell"

"activecell.offset(1,0)"

"loop"

the bit i can't do is create the checkbox control on the userform (i
guess
i'd also liketo control whereabouts on the form the controls go as they
are
added).

greatly for any help,

Tim

creating a checkbox control on a user form using VBA
 

Tim,

Something like this in your form code should do it. I haven't tested it in
this form but I have something similar running in other routines.

Dim cTemp as Control
Dim shInput as Worksheet
Dim lRowCounter as Long

'Start your loop, no need to select the cells

Set shInput = thisworkbook.sheets("InputSheetNameHere")

lRowCounter = 1

Do While not isempty(shInput.cells(lRowCounter,1).value)

Set cTemp = Me.Controls.Add("Forms.CheckBox.1")

with cTemp

.top = 'your top pos
.visible = true
.left = 'your left pos
.width = 'your width
.name = shInput.cells(lrowcounter,1).value
.caption = 'your caption , e.g. shInput.Cells(lrowcounter,2).value

end with

lrowcounter = lrowcounter +1

loop

Robin Hammond
www.enhanceddatasystems.com



"Tim Marsh" wrote in message
...
Hi,

I would like to create a form containing checkboxes 'on-the-fly' from a
list
of values in a spreadsheet range, so after

"userform_initialise()"

i want something like

"do until the last cell in the range ="""

"me.controls.add...... a checkbox named after the value of the active
cell"

"activecell.offset(1,0)"

"loop"

the bit i can't do is create the checkbox control on the userform (i guess
i'd also liketo control whereabouts on the form the controls go as they
are
added).

greatly for any help,

Tim