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?

 

[sai03]

So this is the setup for eg that we have and the user chooses the option from Combo Box and enters the Name in Text Box, then enters "Type of Product" then clicks on the "Add Data" button and the data gets stored in Sheet1. After the data is added, the userform is cleared to enter more products (it can be for the same dept or a diff dept).

Now after we have entered the data, how do I check if all the Dept # have been entered? Because there can be multiple entries for the same dept. This is what is confusing me on how to proceed further. How do I validate the data before proceeding to next page?

 

[Micron]

That helps, but it would help if you could show some data, even if it's fictional, that shows where the missing values are.

Somewhere you must be keeping at least a 2 column list of Dept# | DeptName ?

My knowledge of sheet level data validation in Excel is rudimentary at best. I have the impression here that the type of validation you need is to compare counts in some way, which would be different. So if anyone else is reading this and know better, please do chime in.

This is easy enough to do in Access - not sure about Excel:

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?