none
Data validation using multiple columns but showing only 1 RRS feed

  • Question

  • I have placed the following vba into my worksheet; however, I have no idea how to implement it within excel. How am i supposed to call it, use it and so on. Please help I normally use access, but have been tasked to look up data with multiple rows.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim v As Variant
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("C1:C50")) Is Nothing Then Exit Sub 'Specific range
    
    'Turn off events to keep out of loops
    Application.EnableEvents = False
    
    v = Application.Match(Target.Value, Worksheets("Lists").Range("C:C"), False)
    
    If Not IsError(v) Then
        Target.Value = Worksheets("Lists").Range("A:A").Cells(v).Value
    End If
    
    'Turn events back on to get ready for next change
    Application.EnableEvents = True
    
    End Sub

    Wednesday, September 11, 2019 5:37 PM

All replies

  • You should save the workbook as a macro-enabled workbook (.xlsm), and users should allow macros to run when they open the workbook.

    The code will then run automatically whenever the user changes the value of a (single) cell in C1:C50 on the sheet  whose module contains the code. It will replace the value entered by the user with a value looked up on the Lists sheet.

    So you don't have to run the code yourself (that isn't even possible).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, September 11, 2019 7:10 PM
  • Great. that explains a lot.   But how do I get it to run on cells C1 through C50? because that is what I though it was doing.
    Wednesday, September 11, 2019 7:26 PM
  • Private Sub Worksheet_Change(ByVal Target As Range) will run fully automatically whenever the user changes the value of one or more cells on the worksheet. When the code runs, Excel has set the Target argument to the range of all modified cells.

    The line

    If Target.Cells.Count > 1 Then Exit Sub

    exits the procedure (stops running the code) if the Target contains more than one cell, i.e. the user has modified multiple cells at once. So the code will only continue with the next line if the user modified only one cell.

    The line

    If Intersect(Target, Range("C1:C50")) Is Nothing Then Exit Sub

    exits the procedure if the Target doesn't intersect with the range C1:C50, i.e. if the modified cell is not in that range. So the code will only continue with the next line if the user has modified one of the cells in C1:C50.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, September 11, 2019 8:09 PM
  • It still seems to not be working, any suggestions?

    Friday, September 13, 2019 8:03 PM
  • Did you place the code in the worksheet module? It won't work if you place it in a standard module.

    There are two ways to activate the worksheet module:

    • From Excel: right-click the worksheet tab and select 'View Code' from the context menu.
    • From the Visual Basic Editor: double-click the name of the worksheet under Microsoft Excel Objects in the left-hand pane.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, September 13, 2019 9:19 PM