I need a macro made. I have a list of cities in a dropdown box. There are 4 cities: Houston, Denver, Los Angeles and New York. When a city is selected, I want the selected city to appear in the cell B14. The dropdown box needs to be in Column G of the same worksheet.
Next to the city on the cell B14, I want the state to automatically fill in once a city is selected in the dropdown box in Column G.
Â
Hi,
First you need to put a dropdown box in you sheet, you can do this by selecting ,Developer Tab, then Insertion then select the dropdown box, then put the control on you sheet, your sheet contains also the list of the cities and their states
Let’s suppose that the list of your cities is in (G7:G10) the list of the stats is in (H2:H5), you need to put the selected city in (B14), and the stat in (C14).
Once the control (dropdown box) is on your sheet, right click on it and select (Control Format) in the popup menu, you will see then a window, select the control Tab, in this tab, you can select the range where you have entered you cities (G7:G10) , and you can select the cell that will be liked to your control (G5), this cell will receive the index of the city when you select it.
Now let’s write a small code (macro) that will put the value of the selected city in (B14) and the corresponding stat in (C14).
Right click on the dropdown and select (Affect a Macro) and select New.
Then in the VBA Editor write:
——————————————————————————————————-
Sub Zonecombinée1_QuandChangement()
Dim i As Integer
i = Range("g5").Value + 6
Range("b14").Value = Range("g" & i).Value
Range("c14").Value = Range("h" & i).Value
End Sub
——————————————————————————————————
Thank you
