Results 1 to 6 of 6

Passing Named Range as parameter to Sub()

Threaded View

  1. #1
    Registered User
    Join Date
    04-30-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    38

    Passing Named Range as parameter to Sub()

    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.
    Last edited by Leith Ross; 05-09-2013 at 09:37 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1