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

sai03

New Member
Joined
Jun 1, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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?
 

Attachments

  • 1.JPG
    1.JPG
    55.1 KB · Views: 17

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,199
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 1, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
4,199
Office Version
  1. 365
Platform
  1. Windows
Maybe something like
VBA Code:
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.
 
Solution

sai03

New Member
Joined
Jun 1, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Maybe something like
VBA Code:
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

Well-known Member
Joined
Jun 3, 2015
Messages
4,199
Office Version
  1. 365
Platform
  1. Windows
Glad I could help. Can you mark this as solved? Thanks.
 

sai03

New Member
Joined
Jun 1, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
4,199
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 1, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
4,199
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,181,633
Messages
5,931,098
Members
436,775
Latest member
Taproot007

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