none
problems using Worksheet_Change when I refer to other worksheets - example: updating values when validation table cells change RRS feed

  • Question

  • I select values for a range of sells that are set to use a drop-down validation list, and I'd like to update the cell values when a cell in the validation list changes. I've written the code below and put it on the worksheet that contains the validation list. The ranges of cells are set to use the drop-down validation list is on a different worksheet ("Sheet2" in this example). Some methods that refer to the other sheet work as expected and some produce errors. For example, I can retrieve or set the value property of a cell, but I can't work with a range containing multiple cells. 

    This works:

    Worksheets("Sheet2").Range("A" & PetIndex + 1).Value = NewValue

    This produces an error:

    Set PetCells = Worksheets("Sheet2").Range("A2", Range("A1").End(xlDown))

    Can I not refer to ranges on a worksheet other than the one that contains the VBA code?

    Thanks!

    Option Explicit

    Dim OldValue As String
    Dim NewValue As String
    Dim AnimalListCells As Range
    Dim PetCells As Range
    Dim PetCount As Integer
    Dim PetIndex As Integer

    Private Sub Worksheet_Change(ByVal Target As Range)
        
        'Exit if selecting multiple cells or cell not in column C
        If Target.CountLarge > 1 Or Target.Column <> 3 Then Exit Sub
        
        'Stop updating screen for faster code, and stop processing events until this is done
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        'Find range of cells used for validation table
        Set AnimalListCells = Range("C2", Range("C1").End(xlDown))
        
        'Exit if cell changed is NOT inside range of validation table
        If Not Application.Intersect(AnimalListCells, Range(Target.Address)) Is Nothing Then
            NewValue = Target.Value 'Store new value of cell changed
            Application.Undo 'Set changed cell back to original value
            OldValue = Target.Value 'Store old value of cell changed
            Target.Value = NewValue 'Set changed cell back to new value

            'Find range and number of cells hold current "pets"
            'I tried this using End(xlDown) but errors when referring to a different worksheet
            PetCount = 1
            Do While Not IsEmpty(Worksheets("Sheet2").Range("A" & PetCount + 1).Value)
                PetCount = PetCount + 1
            Loop
            PetCount = PetCount - 1
        
            'Look through range of cells containing "pets" and change old values to new values
            For PetIndex = 1 To PetCount
                If Worksheets("Sheet2").Range("A" & PetIndex + 1).Value = OldValue Then
                    Worksheets("Sheet2").Range("A" & PetIndex + 1).Value = NewValue
                End If
            Next PetIndex
            'I tried this code instead of the code above
            'but errors when range is on a different worksheet
            'PetCells.Replace What:=OldValue, Replacement:=NewValue, LookAt:=xlWhole, _
            'SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            'ReplaceFormat:=False
        End If
        
        'Start updating screen and processing events again
        Application.EnableEvents = True
        Application.ScreenUpdating = True

    End Sub

    Monday, February 24, 2020 1:42 PM

Answers

  • When referring to another worksheet, you must do so consistently. In

    Set PetCells = Worksheets("Sheet2").Range("A2", Range("A1").End(xlDown))

    Range("A1") refers to the active sheet since you don't specify otherwise. Change it to

    Set PetCells = Worksheets("Sheet2").Range("A2", Worksheets("Sheet2").Range("A1").End(xlDown))

    I would prefer to use a variable to refer to Sheet2:

    Dim ws As Worksheet
    Set ws = Worksheets("Sheet2")
    Set PetCells = ws.Range("A2", ws.Range("A1").End(xlDown))


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

    • Marked as answer by Rick8181 Monday, February 24, 2020 5:49 PM
    Monday, February 24, 2020 1:49 PM

All replies

  • When referring to another worksheet, you must do so consistently. In

    Set PetCells = Worksheets("Sheet2").Range("A2", Range("A1").End(xlDown))

    Range("A1") refers to the active sheet since you don't specify otherwise. Change it to

    Set PetCells = Worksheets("Sheet2").Range("A2", Worksheets("Sheet2").Range("A1").End(xlDown))

    I would prefer to use a variable to refer to Sheet2:

    Dim ws As Worksheet
    Set ws = Worksheets("Sheet2")
    Set PetCells = ws.Range("A2", ws.Range("A1").End(xlDown))


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

    • Marked as answer by Rick8181 Monday, February 24, 2020 5:49 PM
    Monday, February 24, 2020 1:49 PM
  • Thanks so much! That's it. That makes perfect sense. 
    Monday, February 24, 2020 5:51 PM