Fill Array with Userform ComboBox names

dforgacs

Board Regular
Joined
Jul 16, 2004
Messages
108
Hello!
Is it possible to count the number of comboboxes on a userform? Or better yet can I fill an Array with all the combobox names on a userform? I can count the controls on a userform but I'm trying to count just the comboboxes? Instead of all the labels and textboxes and frames.
I would like to loop through them for validation instead of doing it by each one by itself! Any help would be appreciated!
Thanks!
dforgacs
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hi,

try something like this
Code:
Private Sub CommandButton1_Click()
Dim ctrl As msforms.Control
Dim i As Long
    For Each ctrl In Me.Controls
    If TypeName(ctrl) = "ComboBox" Then i = i + 1
    Next ctrl
    
MsgBox i
End Sub
kind regards,
Erik
 
Upvote 0
Erik,
I was so close but needed the "TypeName (oCtrl) = "ComboBox" to make it work!
Thanks!
dforgacs
 
Upvote 0
Need to reopen this thread because I need to load the controls themeselves into an array not the names of the controls. The names are text in quotes and you can't use the text version to manipulate the control. Help!
dforgacs
 
Upvote 0
Strange question.
You cannot "put controls in an array" litteraly, so I guess you mean something else...

Makes me think, but just a guess, at
Code:
For i = 1 to 5
Me.Controls("Combobox" & i).dowhateveryouwant
Next i
 
Upvote 0
dforgacs

You mention validation for multiple comboboxes?

What type of validation is it you want? Perhaps you could use a class module approach.

I'm sure I've got some code kicking about that does some sort of validation using the Change event of the combobox(s).
 
Upvote 0
I was trying to put a list of actual controls not the text name into an array to loop through. I could do it manually with a array but I was trying to load it on the fly.

It is a Multipage Form with many combo boxes. I was trying to loop through the controls to verify either a numeric or text input without writing code for each inividual control. I would be interested in a class module, I don't have a lot of experience with Class modules but the ones I have used and worked with are very effective. Do you have any examples of class modules lying around that I could work into my userform for validation? I've been reading and using a book by Rob Bovey, Stephen Bullen,and John Green, called Professional Excel Development. Even though I read the chapter on Class Modules they still seem a little hard to understand how to use them effectively. It's a work in progress! I appreciate your help and guidence!
Thanks!
dforgacs
 
Upvote 0
The little example, I provided you, is not meant to be in a classmodule, but it seems to me that it answers your request.
I was trying to loop through the controls to verify either a numeric or text input without writing code for each inividual control.
Did you try it out?

About Class Modules
I've put some examples on this Board and love to credit Tom Urtis for helping me to understand this. So if ou search for "class module" and put his name, you will probably find some more examples and explanations.


http://groups.google.com/group/micr..._frm/thread/fb2a0cb8cdad568e/74c5fb2778c250e1
http://www.tushar-mehta.com/excel/vba/vba_oops_eg1.htm
Chip Pearsons site (www.cpearson.com) is usually a good place to stop for these types of tips - and he didn't disappoint this time:
http://www.cpearson.com/excel/ClassModules.htm
There's an overview of class modules in the Excel Experts E-letter (from June 2000, but still relevant):
http://www.j-walk.com/ss/excel/eee/eee019.txt
try John Walkenbach's site
among others:
http://spreadsheetpage.com/index.php/file/multiple_userform_buttons_with_one_procedure/
 
Upvote 0
Erik,
No! I haven't had a chance to try it yet, but I will tomorrow and I'll let you know. I will also look into the links you provided. Thanks again
dforgacs
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top