HI,
I am trying to pass a parameter, a named range on another sheet, to a secondary Sub(). The named range is Slist and it is on a sheet called Setup. In my main Sub() I define
Dim MenuList As Range
Set MenuList = ThisWorkbook.Sheets("Setup").Range("SList")
and call the secondary Sub() as
Call Add_Drop_Down_Menu_Cell(MenuList)
My secondary Sub() is
Sub Add_Drop_Down_Menu_Cell(MenuList As Range)
With ThisWorkbook.Sheets("MenuData").Range("B5").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=MenuList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
I am getting application-defined or object defined error 1004. It appears that the Sub cannot locate or access my MenuList. Is there a way I can check that a correct menu list is being passed tot he Sub? Also, any ideas how to fix this? Thank you.
Bookmarks