PDA

View Full Version : Adding checkboxes to forms at runtime



Ouka
02-20-2009, 04:46 PM
Hi all,
Hoping someone can help me out.
I am trying to get the following code snippet that works to add command buttons dynamically to a user form to work for checkboxes:


Private Sub UserForm_Activate()
Dim c As Range
Set c = ActiveSheet.Range("A2")
While (c.Value <> "")
Dim button As CommandButton
Set button = Me.Controls.Add("Forms.commandbutton.1", "CharacterName" & c.Value, True)

With button
.Height = 20
.Width = 90
.Top = c.Row * (.Height + 10)
.Left = 10
.Caption = c.Value
End With

Set c = c.EntireColumn.Rows(c.Row + 1)
Wend
End Sub

This works just fine. But when I change the control type to checkbox:


Private Sub UserForm_Activate()
Dim c As Range
Set c = ActiveSheet.Range("A2")
While (c.Value <> "")
Dim cbox As CheckBox
Set cbox = Me.Controls.Add("Forms.CheckBox.1", "CharacterName" & c.Value, True)

With cbox
.Height = 20
.Width = 90
.Top = c.Row * (.Height + 10)
.Left = 10
.Caption = c.Value
End With

Set c = c.EntireColumn.Rows(c.Row + 1)
Wend
End Sub

I get a type mismatch error on the line:

Set cbox = frmSelectChars.Controls.Add("Forms.CheckBox.1", "CharacterName" & c.Value, True)

Can anyone help me? I'm guessing there is something I'm missing syntax-wise in the .Add properties.

Am I incorrect in assuming the proper format is .add("control name string", "object instance name", visability) ?

Thanks,
--Ouka

Ouka
02-20-2009, 05:53 PM
got an answer from another forum, figured I'd post solution here just in case someone else runs into the same problem.

Solution:

Instead of


Dim cbox as CheckBox

declare as:


Dim cbox as MSForms.CheckBox

This makes it work perfectly. But for my own enlightment, can anyone tell me why the CheckBox control requires the MSForm declaration and the CoomandButton does not?

MaximS
02-20-2009, 09:28 PM
answer is simple you can have command button in pure Excel while CheckBox is part of UserForm controls thats why you need to declare it that way