Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Adding CheckBoxes to a VBA Form

Posted on 2011-02-21
10
Medium Priority
?
557 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:tonelm54
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 34943911
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 34943912
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34943934
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34943939
I give up!!!! Speedy Rory at action today... lolzzz

Sid
0
 
LVL 18

Expert Comment

by:Cory Vandenberg
ID: 34944045
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34944093
Unless of course your valid values are only Integer values and you want to validate that.

Never say never. ;)
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 34944147
>>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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34944209
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
 
LVL 18

Expert Comment

by:Cory Vandenberg
ID: 34945500
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
 

Author Closing Comment

by:tonelm54
ID: 34946901
Excellent, thank you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question