Mattia has attended:
Excel VBA Intermediate course
Drop down, Excel VBA
Hello,
Is it possible to have a dropdown list in several worksheets; when an item is selected in the dropdown in any of the worksheets, all other dropdowns get automatically updated with the same value?
Basically I have a workbook with several worksheets. each worksheet contains information about a product and is assigned to a different team.
In there i have a drop down to select where the physical part is.
I basically want the dropdowns for the location to interact with one another, so that all sheets report the same location for the part when a location is selected in the dropdown.
Thank you infinitely!
RE: 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
