Results 1 to 5 of 5

Drop Down From Named Range

Threaded View

  1. #1
    Forum Contributor
    Join Date
    04-16-2020
    Location
    Alberta, Canada
    MS-Off Ver
    365
    Posts
    230

    Drop Down From Named Range

    I'm trying to get a validation list drop down to be created from a named range in another workbook, but I'm having issues even getting it to do it from the same workbook.

    It's the .Add Type line, but I'm not sure how to correct it.

    Sub Test()
    Dim vArray()            As Variant
    Dim rng                 As Range
    Dim wbk                 As Workbook
    
    Set wbk = Excel.Application.Workbooks("Sample.xlsm") ' this will be Workbooks("Library.xlam")
    Set rng = wbk.Worksheets("Sheet1").Range("Test") ' this will be wbk.Worksheets("A1").Range("Test"))
    vArray = rng
        
        With ActiveSheet.Range("mm.1").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=Join(vArray, ",")
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Go to Named Range after selecting item from Drop Down
    By Howardc1001 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-30-2022, 12:30 PM
  2. [SOLVED] Dynamic Named Range and drop down list
    By VisionSmart in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-16-2021, 01:27 AM
  3. [SOLVED] Two different drop down lists (with same named range) defined by one drop down list
    By Valkmi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-29-2016, 12:06 PM
  4. Named range populates after drop down selection
    By hwtcrl in forum Excel General
    Replies: 0
    Last Post: 02-06-2013, 10:49 AM
  5. Named Range Drop Down Problem
    By TomWOTSEC in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-01-2012, 04:43 AM
  6. Named range and drop down list
    By adrianodl in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-20-2012, 09:34 PM
  7. [SOLVED] Excel Drop down List with Named Range
    By GregDAngelo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-11-2006, 10:30 AM

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