MS Excel drop down

On sheet Main Menu, there are many drop down boxes. I need add drop down boxes to F 2000. How can I add more drop down boxes with the following input range Foreman!$B$2:$B$21
 and cell link that changes based on the cell. For example:
F66
F67
F68
F69
Clell-link-update.jpg
P-217---Good-Catch---Near-Miss-P.xlsm
cssc1Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
NorieConnect With a Mentor VBA ExpertCommented:
The data validation replaces the dropdown controls, so remove those controls.

See the attachment, in each cell in F11:F2000 there's a dropdown.
P-217---Good-Catch---Near-Miss-P.xlsm
0
 
NorieVBA ExpertCommented:
Instead of droddown controls you could use Data>Validation...

To do that select the range you want the dropdowns in, goto Data>Validation..., select the List option and enter this for the source.

 =Foreman!$B$2:$B$21

The value picked from the data validation dropdown will go in the cell the validation is in, so no need to set the linked cell.
0
 
Saqib Husain, SyedEngineerCommented:
You can copy down the controls all the way to 2000 and then run this macro

Sub adddb()
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 9) = "Drop Down" Then
shp.ControlFormat.LinkedCell = shp.TopLeftCell.Address
shp.TopLeftCell.Select
End If
Next shp
End Sub
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
cssc1Author Commented:
The macro locked up see attached image
lockup.jpg
lockup.jpg
0
 
Saqib Husain, SyedEngineerCommented:
How long did you wait? This is a very slow process.
0
 
cssc1Author Commented:
20 MIN
0
 
NorieVBA ExpertCommented:
If you used Data>Validation... it would take seconds.:)
0
 
cssc1Author Commented:
what is data validation?
0
 
NorieVBA ExpertCommented:
I tried to explain in an earlier post but probably made a bad job of it.

To set up data validation first select your range, F11:F2000, you can do by entering F11:F2000 in the Name box to the right of the Formula bar.

Then click the Datat tab, select Data Validation>Data Validation...

Now in the Allow box select List from the dropdown.

You should now see a box labelled Source which you should enter this in.

 =Foreman!$B$2:$B$21

Finally click OK.

Now you should have dropdowns in each cell of the range F10:F2000.

Whenever you select a value from one of the dropdowns that value will go in the corresponding cell.

Does that help?
0
 
cssc1Author Commented:
Yes, this did work. However, the "Cell Link" box on the "Control" tab of the Format Control screen all say: $F66.

The Cell Link should be equal to the row. For example: F11, F12, F13, F14.....
0
 
cssc1Author Commented:
Your expert help solved my problem.
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.