Adding CheckBoxes to a VBA Form

Good afternoon,
Ive been trying to add a bunch of checkboxes to a form Ive created in an Excel spreadsheet, I want to draw the checkboxes during run-time as the text and quantiy will change.

The code Ive done so far, which Ive putin UserForm_Activate() looks ok:-

   Dim x As Integer
   
    For x = 1 To 100 Step 1
        Dim newCheckBox  As CheckBox
       
        newCheckBox.Text = "Hello " & x
        newCheckBox.Top = x * 30
        newCheckBox.Left = 10
       
        Frame1.Controls.Add newCheckBox
    Next



However when it runs the line 'newCheckBox.Text = "Hello " & x' I get the error 'Run time error '91': Object variable or With block variable not set', but cant see anything thats not set.

Can anyone else see what I need to set to set a new checkbox up and add it to the controls on the form?

Thank you
tonelm54Asked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
There were a few issues there - what you need is:
 Dim x As Integer
    
    For x = 1 To 100 Step 1
        Dim newCheckBox  As MSForms.CheckBox
        Set newCheckBox = Me.Frame1.Controls.Add("Forms.CheckBox.1")
        newCheckBox.Caption = "Hello " & x
        newCheckBox.Top = x * 30
        newCheckBox.Left = 10
    Next

Open in new window

0
 
Patrick MatthewsCommented:
You never actually assign the newCheckBox variable, so that is why you got the error.

In any event, if you are always looking to add 100 checkboxes (which makes for a wickedly complex UserForm, but whatever), then why create them at runtime?  Why not simply design your UserForm with those controls to begin with?
0
 
SiddharthRoutCommented:
Few things...

1) You may omit the Step 1 as it is by default

2) Checkboxes do not have a .Text property. Use .Caption.

Try this

Private Sub CommandButton1_Click()
   Dim x As Integer
   
    For x = 1 To 5
        Dim newCheckBox As MSForms.CheckBox
        Set newCheckBox = Frame1.Controls.Add("Forms.CheckBox.1")
        newCheckBox.Caption = "Hello " & x
        newCheckBox.Top = x * 30
        newCheckBox.Left = 10
    Next
End Sub

Open in new window


Sid
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SiddharthRoutCommented:
I give up!!!! Speedy Rory at action today... lolzzz

Sid
0
 
Cory VandenbergSenior Risk ManagerCommented:
Let me add one thing to this discussion.

You really should NEVER use Integer types when coding in VBA.  They are converted to Long type in the VB engine, and have the unfortunate possibility of an overflow error (something you will see if looping through rows of the worksheet for instance).  Because of this conversion, it is also more efficient to use the Long type, as you avoid the conversion.

Anyways, it has nothing to do with the solution here, but just wanted to point it out.

WC
0
 
Rory ArchibaldCommented:
Unless of course your valid values are only Integer values and you want to validate that.

Never say never. ;)
0
 
Patrick MatthewsCommented:
>>Unless of course your valid values are only Integer values and you want to validate that.

Perhaps, but I should think that there are cleaner ways to validate inputs than to rely on trapping an overflow error :)
0
 
Rory ArchibaldCommented:
I was thinking more of design time. If your routines are set up to take Integers, then a design time attempt to pass a Long will be flagged up without you needing to validate at run time.
0
 
Cory VandenbergSenior Risk ManagerCommented:
My point was that if you are the one doing the "designing" of the code, it is best to avoid Integer type in VBA.  If I am the one writing the code, there is never, and I mean never, an instance where Integer cannot be replaced by Long.

A lot of times when I copy code from others I will go through and change Integer type casting to Long, even if there is no real need for it other than peace of mind and perhaps a microscopic performance enhancement.

Always appreciate the input from you though, Rory.  I've learned a lot in the past from your comments.

WC
0
 
tonelm54Author Commented:
Excellent, thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.