Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Adding CheckBoxes to a VBA Form

Posted on 2011-02-21
10
Medium Priority
?
553 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

670 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