+ Reply to Thread
Results 1 to 3 of 3

Multiple selections from data validation macro

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-19-2013
    Location
    Yorkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    297

    Multiple selections from data validation macro

    Hi excel experts!

    I have been using this contextures page with this.

    The macro I am using currently allows the user to select multiple things from a drop down list, and currently the selections appear in the rows of the column next to cell containing the drop down list.

    So if the drop down list was in A1 the selections would appear in B1,B2,B3,B4,B5 etc etc.

    I am complete newbie when it comes to VBA- but would like the current macro I have to have the selection to start from a few rows bellow the drop down list but in the SAME column- so A4,A5,A5 etc

    The current code is:

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo exitHandler
    
    Dim rngDV As Range
    Dim lRow As Long
    Dim lCol As Long
    
    lCol = Target.Column 'column with data validation cell
    
    If Target.Count > 1 Then GoTo exitHandler
    
    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler
    If rngDV Is Nothing Then GoTo exitHandler
    If Intersect(Target, rngDV) Is Nothing Then
       'do nothing
    Else
        If Target.Value = "" Then GoTo exitHandler
        Application.EnableEvents = False
        Select Case Target.Column
           Case 16
            If Target.Offset(0, 1).Value = "" Then
               lRow = Target.Row
            Else
               lRow = Cells(Rows.Count, lCol + 1).End(xlUp).Row + 1
            End If
            Cells(lRow, lCol + 1).Value = Target.Value
            Target.ClearContents
        End Select
       
    End If
    
    exitHandler:
      Application.EnableEvents = True
      
    End Sub
    Any ideas (?)...thanks in advance...

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Multiple selections from data validation macro

    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo exitHandler
    
    Dim rngDV As Range
    Dim lRow As Long
    Dim lCol As Long
    
    lCol = Target.Column 'column with data validation cell
    
    If Target.Count > 1 Then GoTo exitHandler
    
    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler
    If rngDV Is Nothing Then GoTo exitHandler
    If Intersect(Target, rngDV) Is Nothing Then
       'do nothing
    Else
        If Target.Value = "" Then GoTo exitHandler
        Application.EnableEvents = False
        Select Case Target.Column
           Case 16
            If Target.Offset(3, 0).Value = "" Then
               lRow = Target.Row
            Else
               lRow = Cells(Rows.Count, lCol).End(xlUp).Row - 2
            End If
            Cells(lRow + 3, lCol).Value = Target.Value
            Target.ClearContents
        End Select
       
    End If
    
    exitHandler:
      Application.EnableEvents = True
      
    End Sub
    I suppose?

  3. #3
    Forum Contributor
    Join Date
    04-19-2013
    Location
    Yorkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    297

    Re: Multiple selections from data validation macro

    Awesome- that's got it!

    Thanks yudlugar for the swift and helpful response.

+ 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