Solved

Adding CheckBoxes to a VBA Form

Posted on 2011-02-21
10
544 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
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 500 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 92

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

920 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

14 Experts available now in Live!

Get 1:1 Help Now