+ Reply to Thread
Results 1 to 3 of 3

Question regarding multiple vba based drop down lists in worksheet

Hybrid View

lilsnoop Question regarding multiple... 12-30-2012, 11:10 PM
Cofad Re: Question regarding... 12-30-2012, 11:28 PM
lilsnoop Re: Question regarding... 12-30-2012, 11:31 PM
  1. #1
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2021 & 365
    Posts
    977

    Question regarding multiple vba based drop down lists in worksheet

    Hello,

    I would like to have a name drop down list in column A and another list for various scheduling items in a range from B to AF. Using the code below I can get one, but not the other to work as they conflict with one another.
    Any assistance would be appreciated!

    With ws
        .Cells.Validation.Delete
    LR = .Cells(Rows.Count, 1).End(xlUp).Row
        For intCol = Columns("B").Column To Columns("AF").Column
            If .Cells(1, intCol).Value = "" Then
                With .Range(.Cells(3, intCol), .Cells(LR, intCol)).Validation
                    .Delete
                    .Add xlValidateList, xlValidAlertStop, xlBetween, "DO,XX,V,7V,6V,5V,4V,3V,2V,1V,C,7C,6C,5C,4C,3C,2C,1C,S,4S,TR,4TR,PL,4PL,SW,4SW,CT,TF,SUS,S,7S,6S,5S,4S,3S,2S,1S,WC,NP"
                End With
            End If
        Next intCol
    End With
    With ws
        .Cells.Validation.Delete
    LR = .Cells(Rows.Count, 1).End(xlUp).Row
        For intCol = Columns("a").Column To Columns("A").Column
            If .Cells(1, intCol).Value = "" Then
                With .Range(.Cells(3, intCol), .Cells(LR, intCol)).Validation
                    .Delete
                    .Add xlValidateList, xlValidAlertStop, xlBetween, "name1,name2,name3,name4,etc"
                End With
            End If
        Next intCol
    End With
    End Sub
    Last edited by lilsnoop; 12-30-2012 at 11:31 PM.

  2. #2
    Forum Contributor
    Join Date
    09-22-2012
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Question regarding multiple vba based drop down lists in worksheet

    Here's what I got:

        
    
        Set ws = ActiveSheet
        
        'Find last used row
        LR = ws.Cells(Rows.Count, 1).End(xlUp).Row
        
        'Clear validation in all cells
        ws.Cells.Validation.Delete
    
        'Set validation for column "A"
         With ws.Range("A:A").Validation
         
            .Delete
            .Add xlValidateList, xlValidAlertStop, xlBetween, "name1,name2,name3,name4,etc..."
                
        End With
    
        'Set validation for Colums "B" to "AF"
         With ws.Columns("B:AF").Validation
         
            .Delete
            .Add xlValidateList, xlValidAlertStop, xlBetween, "DO,XX,V,7V,6V,5V,4V,3V,2V,1V,C,7C,6C,5C,4C,3C,2C,1C,S,4S,TR,4TR,PL,4PL,SW,4SW,CT,TF,SUS,S,7S,6S,5S,4S,3S,2S,1S,WC,NP"
                
        End With
    Hope this helps!
    ------------------------
    W2

  3. #3
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2021 & 365
    Posts
    977

    Re: Question regarding multiple vba based drop down lists in worksheet

    Excellent! Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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