Solved

Adding CheckBoxes to a VBA Form

Posted on 2011-02-21
10
540 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 92

Expert Comment

by:Patrick Matthews
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
I give up!!!! Speedy Rory at action today... lolzzz

Sid
0
 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 85

Expert Comment

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

Never say never. ;)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
>>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
Comment Utility
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:WarCrimes
Comment Utility
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
Comment Utility
Excellent, thank you
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now