How do I link Spin Button to populate a Combo Box?

[sai03]

Hello,

I am new to Excel VBA, so please bear with my explanation.

I have a User form with a Multipage setup (3 pages) and on the 1st page, I take the user input for No. of Departments using a Spin Button.

Now I want a Combo Box to be populated based on the number selected in the Spin Button field on the 2nd page of the Multipage setup. For e.g., if a user chooses 2 departments, then I want the Combobox to have a drop-down list showing Department 1 and Department 2. If the user chooses 3 then the drop-down should have Dept 1, Dept 2, and Dept 3, so on.

How do I go about achieving this?

 

[Micron]

Do you have a textbox on the form that displays the number that the user spins to? Then in a command button click event you could loop from 1 to that number and add the values to the combo value list?

 

[sai03]

Do you have a textbox on the form that displays the number that the user spins to? Then in a command button click event you could loop from 1 to that number and add the values to the combo value list?

Yes I have a Textbox that displays the value of the Spin Button. How do I write a code for the for-loop? Sorry I'm very new to this, your help would be great.

 

[Micron]

Maybe something like

Private Sub CommandButton3_Click()
Dim i As Integer

Me.ComboBox1.Clear
For i = 1 To Me.TextBox4
     Me.ComboBox1.AddItem "Dept" & i
Next

End Sub

That assumes you will populate the combo list with the values by clicking on a command button named CommandButton3 and your textbox and combo box names are what I used. If not, use your control names instead.

 

[sai03]

Maybe something like

Private Sub CommandButton3_Click()
Dim i As Integer

Me.ComboBox1.Clear
For i = 1 To Me.TextBox4
     Me.ComboBox1.AddItem "Dept" & i
Next

End Sub

That assumes you will populate the combo list with the values by clicking on a command button named CommandButton3 and your textbox and combo box names are what I used. If not, use your control names instead.

Thank you so much! This worked for me.

 

[Micron]

Glad I could help. Can you mark this as solved? Thanks.

 

[sai03]

Hello Micron,

I have a further question about this ComboBox. The above is working fine for me, now I would like to check if all the values in the ComboBox are selected like:

Select "Dept 1" in ComboBox and enter the Dept Name in a TextBox. So if the user has 3 Depts in the ComboBox, how do I validate that the user selects and fills the names of all the 3 Depts before proceeding to the next page?

Is it possible or am I missing something?

 

[Micron]

In Access (which is what I know better) a combo box only allows user to pick one item from the list. There is a special case where it is possible to multi select, but that only applies to Access. AFAIK, if you want to multi select items from a list, it has to be a listbox.

 

[sai03]

In Access (which is what I know better) a combo box only allows user to pick one item from the list. There is a special case where it is possible to multi select, but that only applies to Access. AFAIK, if you want to multi select items from a list, it has to be a listbox.

Maybe I've explained it wrong. In the previous step, we are populating the Combo Box with a Text Box from Page 1.

For eg: In Page 1, we entered the No. of Depts as 3, and the Combo Box on Page 2 will be populated with 3 items. This is good so far, for the next step, on Page 2 itself there will be another Text Box where the user needs to enter the Name of Dept.

Now, what I would like is before entering the Name of Dept. the user will choose "Dept 1" from the Combo Box and will enter the Name. After that, he can click "Next" and go to Page 3 without entering the names of "Dept 2" and "Dept 3". I would like to prevent that from happening, I would like to validate that part where the user should enter the names of all 3 depts before proceeding to the next page.

I hope I have explained it a bit better than before. I do not need the multi-select option, I just need to validate the data.

 

[Micron]

Somewhere you must be keeping at least a 2 column list of Dept# | DeptName ? I suppose if the count of Dept# in that list doesn't match the count of name list, hide whatever it is that allows the user to go to another page? Or compare the count of names in the list to the value of the textbox showing 3 on page 1 and do the same? Or hide the pages that follow (these are what, tab control pages?).
This is getting quite difficult to visualize. Pics might be the only way to follow what you're saying if this post doesn't help.