Fill Array with Userform ComboBox names

[dforgacs]

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

 

[Norie]

dforgacs

When do you want the validation to occur?

Are you sure you actually need code to check what's entered?

If you are dealing with comboboxes why not just use there various propertied to prevent invalid input in the first place.

If you want to try that then the first thing to do is probably only populate the comboboxes with valid entries.

You can also make it so that a user is unable to enter any value that's not on the list.

These methods should work but using a class might give you more control. eg you'll be able to display your own custom messages.

I do have some code for mutlipages with comboboxes, but it's so old I can't quite get my head round it - and it's for Word.

Could you give more details of exactly what you want to do?

Do you want to check the comboboxes before allowing the user to move to another page?

How are the comboboxes and multipages created? In design view? Or dynamically?

Exactly what validation do you want to use and which comboboxes do you want it to apply to.

Sorry, for all the questions - listening to important football game, so no time to post any code.

When I get some time I'll try and either find something or create something and post back.

 

[dforgacs]

Hi Norie,

When do you want the validation to occur?
ans? I think when the user enters the data.

Are you sure you actually need code to check what's entered?
ans: No I don't need code for all boxes other than to make sure a selection has been made.

If you are dealing with comboboxes why not just use there various propertied to prevent invalid input in the first place.
ans: I'm currently doing that where I need specific answers. But I can't use that everywhere.

If you want to try that then the first thing to do is probably only populate the comboboxes with valid entries.
ans: I agree.

You can also make it so that a user is unable to enter any value that's not on the list.
ans: Yes I have done that for the applicable boxes

These methods should work but using a class might give you more control. eg you'll be able to display your own custom messages.
ans: I like that idea and have been working on a way to loop through the combobox controls to display custom messages


I do have some code for mutlipages with comboboxes, but it's so old I can't quite get my head round it - and it's for Word.

Could you give more details of exactly what you want to do?
ans: I have a multipage form with 4 pages.
Page 1 is instructions and Definitions in text boxes.
Page 2 has 12 comboboxes, 8 are drop downs and 4 use data entry.
Page 3 has 38 comboboxes, All are Numeric or text
page 4 has 60 comboboxes, All are numeric or text

Do you want to check the comboboxes before allowing the user to move to another page?
ans: Yes

How are the comboboxes and multipages created? In design view? Or dynamically?
ans: Design view

Exactly what validation do you want to use and which comboboxes do you want it to apply to.
ans: For the none drop down boxes, it would be numeric or text and not empty or zero.

Let me know. I've been working on using some of the code from the book listed in a previous message in this thread but having trouble with some of it as well.

I still have to try Erik's last suggestion to see if it will do what I need.
Thanks!
dforgacs

 

[GazzaLDN]

Makes me think, but just a guess, at

For i = 1 to 5
Me.Controls("Combobox" & i).dowhateveryouwant
Next i

Hi Erik.

Your code above helped me solve my problem with taking values from multiple ComboBoxes and TextBoxes on a Userform and entering them into cells on a worksheet.

I had it working the long way, but with 5 columns and 13 rows of ComboBoxes and TextBoxes on my userform, it was a lot of code.

With UserForm1
    .Show
    
    Sheets("Summary").Cells(5, 89) = .ComboBox1.Text
    Sheets("Summary").Cells(6, 89) = .ComboBox2.Text
    Sheets("Summary").Cells(7, 89) = .ComboBox3.Text
' and so on

When I tried to use a for next loop to compile the ComboBox and TextBox names, it kept getting a compile error.

The key to the solution was the .Controls rather than .ComboBox!

The finished code may help anyone else searching for an answer like i was.

Private Sub CommandButton1_Click()
With UserForm1
For i = 0 To 12
Sheets("Summary").Cells(5 + i, 89) = .Controls("ComboBox" & 1 + i)
Sheets("Summary").Cells(5 + i, 92) = .Controls("ComboBox" & 14 + i)
Sheets("Summary").Cells(5 + i, 90) = .Controls("TextBox" & 1 + i)
Sheets("Summary").Cells(5 + i, 91) = .Controls("TextBox" & 14 + i)
Sheets("Summary").Cells(5 + i, 88) = .Controls("ComboBox" & 27 + i)
Next i
    
Unload Me
End With
  
End Sub

 

[erik.van.geit]

Hi, GazzaLDN, WELCOME on board!
Most of the members make their entrance with a question, but you made a "helping post"!

When I tried to use a for next loop to compile the ComboBox and TextBox names, it kept getting a compile error.

That depends on how you wrote all this stuff and how your Userform was build... Difficult to see from here, but anyway you got your solution.

kind regards and enjoy MrExcel forum!
Erik