Excel 2010 VBA – Combo Box Options– Clear cell/box in 2nd Combo when 1st Combo selected

Hello and thank you for taking the time to check this out

I have created a form in Excel 2010 which can be completed electronically, or the old fashioned way, by hand. There is no need to retain the information anywhere else after it has been completed. The user can either save it, or print it.

I have 2 dropdown dependant comboboxes based under ‘Data Validation’.
First combo box has Business Unit (3 Options: Office, Maintenance and Operations).
Second combo box “Profile” provides a list of profiles based on which Business unit has been selected.

Although this works, it’s a bit disjointed. What I mean is that when I click on the Business Unit, eg. Operations, it populates the box with Operations …. This works 
Then I have to click the Profile combo box and make a selection based on the list for Operations. This also works 

Currently, if the user wants to change Business Unit … say to “Office”, then the user will have to remember to go to the “Profile” combo box to make a new selection based on the list for “Office” (can’t rely on user’s memory LOL).

What I would ideally like is, when the Business Unit has been selected, it clears/blanks the cell value of the “Profile” cell and provides the options for the selected Business Unit automatically. This way the user can’t forget to make a selection from the “Profile” list.

I need the Business unit selection to be visible.
Please refer to the attached sample.

I’d appreciate any assistance, as I have been trawling through the web trying to find some code that I could modify to meet my needs, but I haven’t found any. My knowledge of VBA is equivalent to a toddler … small steps.

I have tried to create the form by the VBA Userform but then I had problems with the option dependent combo boxes and then come across the same problem of how to clear any previous value in that cell/combo box.

Thank you very much for your time.

Regards,
TheShyButterfly

Hi

you need a simple macro.

Change your definitions for Operations, Office and Maintenance to include row 2.
You will need to go to formulas and select name manager.

right click on your sheet name at the bottom of excel and select view code.

Paste this code in the new window and close it.

Thats it.

Please Login or Register  to view this content.

Hi Mehmetcik,

You are a champ Thank you so much ... it worked

Thank you so much for your quick response.