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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

dforgacs

Board Regular
Joined
Jul 16, 2004
Messages
108
Erik,
I was so close but needed the "TypeName (oCtrl) = "ComboBox" to make it work!
Thanks!
dforgacs
 

dforgacs

Board Regular
Joined
Jul 16, 2004
Messages
108

ADVERTISEMENT

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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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).
 

dforgacs

Board Regular
Joined
Jul 16, 2004
Messages
108
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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/
 

dforgacs

Board Regular
Joined
Jul 16, 2004
Messages
108
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,040
Members
414,357
Latest member
Gemma_R

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
Top