UserForm that returns information on a loop

[gschmidt82]

Hi all,

I am new to VBA, using Excel 2010. I am working on spreadsheet that I need to process every month, dealing with upwards of 2000 rows of data in 45 columns. Column A lists employee names; column S lists their current job titles. I have a For... loop that examines each job title and assigns an abbreviation. However, some employees have moved on from my department, and their job titles bear no relationship to the 15 discreet titles I've identified for our department.

I'm trying to develop a userform that appears every time my loop comes across an unrecognized title, and I want that userform to ask, "Please select [Employee]'s most recent job title in the department." I then want the userform to display a list of the 15 option buttons, each of which displays one of the discreet job titles for our department only. Once the user clicks the appropriate job title, the userform would close, and the appropriate row's cell in column S would contain the appropriate job title abbreviation.

I've tried coding this, but feel a bit lost. I know that it should be possible, but my work is embarrassing. What should the Sub look like for the macro? What should the code look like for the userform? Any help?

Thanks so much in advance!

-gschmidt82

 

[RickXL]

This might be an easier way.

Use the Excel Data validation on the Data menu to validate your entered Job Titles.

Choose "List" from the "Allow" box and place the list of valid values somewhere in the workbook. Refer to that range in the Data validation dialog.
You can then go to the Data Validation icon again and ask Excel to Circle Invalid Data. A red circle will then be drawn round all the invalid values and there will be a drop down list based on the list you entered with valid values in it.

You then have to remove the red circles manually by asking Excel to Circle Invalid Data again.