Drop down, Excel VBA

Hi Mattia,

Thank you for your question. You don’t actually need VBA for this. You can use combo box drop-downs in your sheets. Here are the steps:

1.Enable the Developer tab (File – Options – Customize Ribbon – tick Developer on right)
2.In Developer, click Insert, then click Combo Box (second on top row)
3.Draw a combo box on the first sheet (click & drag)
4.In the Controls group, click Properties
5.Click inside the Input Range field, then highlight the list of locations (these should be entered into a range of cells)
6.Click inside the Cell Link field, then select a cell which will reflect the chosen location as a number. (You won’t be doing anything with this number but it makes the drop-down work)
7.Set the Drop down lines number according to the number of locations, then click OK
8.Test to see whether the drop-down works
9.Select, copy and paste the combo box onto the second sheet
10.Repeat step 5, highlighting the exact same list
11.Repeat step 6, selecting the exact same cell on the first sheet for the cell link
12.Repeat step 7
13.Repeat the procedure for all sheets in the workbook

If all goes to plan, you should be able to use any drop-down and all the others will display the same choice.

I hope this helps.

Kind regards
Marius Barnard
Excel Trainer