I need help editing a macro in Excel 2007 that creates a dropdown menu. Hi. I am

Question

I need help editing a macro in Excel 2007 that creates a dropdown menu.

Hi. I am trying to get help with editing a macro that autopopulates a dropdown menu in Excel 2007. The macro currently populates 3 dropdown menus based on 3 different ranges of data in another sheet. I need to make two additional dropdown menus so that the macro produces a total of 5 dropdown menus. Does anyone know how to do this?   

Here is the code for the macro as is. Thank you very much for your help

Sub SolutionsTestButton()
'
' Macro21 Macro
'
Application.ScreenUpdating = False
Sheets("Business Assessment").Select
Range("R2:V2000").Select
Selection.Copy
Sheets("Solutions and Indicators").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.SpecialCells(xlCellTypeBlanks).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
'ActiveWindow.SmallScroll Down:=-15
'Range("B2:E26").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeConstants, 1).Select
Selection.Delete Shift:=xlUp
Columns("B:F").Select
Columns("B:F").EntireColumn.AutoFit
Columns("B:B").Select
ActiveSheet.Range("B:B").RemoveDuplicates Columns:=1, Header:=xlNo
'Alphabetize
Range("B2:B75").Select
ActiveWorkbook.Worksheets("Solutions and Indicators").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Solutions and Indicators").Sort.SortFields.Add Key:=Range("B2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Solutions and Indicators").Sort
.SetRange Range("B2:B75")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("C:C").Select
ActiveSheet.Range("C:C").RemoveDuplicates Columns:=1, Header:=xlNo
'Alphabetize
Range("C2:C75").Select
ActiveWorkbook.Worksheets("Solutions and Indicators").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Solutions and Indicators").Sort.SortFields.Add Key:=Range("C2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Solutions and Indicators").Sort
.SetRange Range("C2:C75")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("D:D").Select
ActiveSheet.Range("D:D").RemoveDuplicates Columns:=1, Header:=xlNo
'Alphabetize
Range("D2:D75").Select
ActiveWorkbook.Worksheets("Solutions and Indicators").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Solutions and Indicators").Sort.SortFields.Add Key:=Range("D2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Solutions and Indicators").Sort
.SetRange Range("D2:D75")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("E:E").Select
ActiveSheet.Range("E:E").RemoveDuplicates Columns:=1, Header:=xlNo
'Alphabetize
Range("E2:E75").Select
ActiveWorkbook.Worksheets("Solutions and Indicators").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Solutions and Indicators").Sort.SortFields.Add Key:=Range("E2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Solutions and Indicators").Sort
.SetRange Range("E2:E75")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("F:F").Select
ActiveSheet.Range("F:F").RemoveDuplicates Columns:=1, Header:=xlNo
'Alphabetize
Range("F2:F75").Select
ActiveWorkbook.Worksheets("Solutions and Indicators").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Solutions and Indicators").Sort.SortFields.Add Key:=Range("F2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Solutions and Indicators").Sort
.SetRange Range("F2:F75")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
  
'CreateDropDowns Macro
' Creates dropdown cells for additional recommendations
'

'
Sheets("Additional Recommendations").Select
  
ActiveWorkbook.Names("SV").RefersTo = "='Solutions and Indicators'!F2:F30"
ActiveWorkbook.Names("SF").RefersTo = "='Solutions and Indicators'!E2:E30"
ActiveWorkbook.Names("BAL").RefersToR1C1 = "='Solutions and Indicators'!R2C4:R50C4"
ActiveWorkbook.Names("LHF").RefersToR1C1 = "='Solutions and Indicators'!R2C2:R50C2"
ActiveWorkbook.Names("TI").RefersToR1C1 = "='Solutions and Indicators'!R2C3:R50C3"
  
Application.ScreenUpdating = False

End Sub
  
Sub MakeRange1()
ActiveWorkbook.Names.Add _
Name:="SV", _
RefersTo:="=Solutions and Indicators!F$2:F$50"
End Sub
  
Sub MakeRange2()
ActiveWorkbook.Names.Add _
Name:="SF", _
RefersTo:="=Additional Recommendations!A$32:A$40"
End Sub

Explanation / Answer

SDFoes t5he drop ar4row only ap0pear w2hen you select the relevant cell?

- if so it's almost certainly a data validation dropdown.
- to create a similar one for SV in a different cell, select that cell, Data > Data Validation > Data Validation > Allow: List; Source =SV > OK

Otherwise, if you right-click one of the current dropdowns, do you get a short-cut menu including Assign Macro?

- If so, it's a forms control.

- To create a new one you need the Developer tab on the ribbon > Insert > Forms Controls: click the dropdown icon (the second from the left in the top row) and drag your mouse on the worksheet.
Then right-click it > Format Control > Control > Input Range: SV > OK

Otherwise it must be an ActiveX Control

- To create a new one you need the Developer tab on the ribbon > Insert > ActiveX Controls: click the dropdown icon (the second from the left in the top row) and drag your mouse on the worksheet.
Then right-click it >Properties > Listfillrange: SV. Then click Developer > Design mode to turn design mode off

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.