VBA update search find routine using userform listbox selection

So someone recently helped me with implementing a way to manage data updates using a sheet change event within a lookuplist sheet and it works great...however only if the user updates values on the sheet directly. The goal is this list will be hidden and controlled using only forms allowing the user to work within the forms regardless of which sheet is selected. Many thanks to mart37 for the code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim NewValue As Variant
Dim OldValue As Variant
Dim SearchRange As Range
Dim Found As Variant
Dim FirstAddress As Variant
Dim CountReplaces As Integer

If Intersect(Target, ActiveSheet.Columns(7)) Is Nothing Then Exit Sub
If IsEmpty(Target) Then
    MsgBox "Cell is empty!"  '\\I don't know if I really need this
    Exit Sub
End If
On Error GoTo Err:
Application.EnableEvents = False
NewValue = Target
Application.Undo
OldValue = Target
Target = NewValue
Set SearchRange = Sheets(7).Range(Sheets(7).Cells(2, 2), Sheets(7).Cells(Rows.Count, 1).End(xlUp).Address)
With SearchRange
    Set Found = .Find(OldValue, LookIn:=xlValues)
    If Not Found Is Nothing Then
        FirstAddress = Found.Address
        CountReplaces = 0
        Do
            Sheets(7).Range(Found.Address) = NewValue
            CountReplaces = CountReplaces + 1
            Set Found = .FindNext(Found)
            If Found Is Nothing Then GoTo DoneFinding
        Loop While Found.Address <> FirstAddress
DoneFinding:
        If CountReplaces > 0 Then MsgBox ("This group was replaced" & CountReplaces & " times within the distribution list.")
    End If
End With
Err:
Application.EnableEvents = True
End Sub

When a value is updated (or added) when not on the lookup sheet, the error occurs on:

If Intersect(Target, Sheets(5).Columns(7)) Is Nothing Then Exit Sub

I've changed ActiveSheet to Sheets(5) - but I'm not sure if I'm referencing the sheets correctly. Regardless, it doesn't seem to make a difference.

The Change event macro only works on the host sheet.

If you want to use the macro to change another sheet then use the change event macro to call another macro.

The Second Macro should be in a normal macro module.

You can delete the Message Box, if that line is not needed.

I understand that the change event only works on the host sheet. The change is indeed made to the host sheet from the sub routine. It's when a change to the host sheet occurs, values are updated within another database within another sheet. Like I say, it works, but only if the host sheet is active. I guess I don't understand why the sheet needs to be active for the sub to fire properly. Is there an alternative, such as something in the update macro to activate the host sheet, make the update (or add), then reactivate whatever sheet the change was made from? Something like:

Sub update_click()
application.screenupdating = false
Set ws1 = activesheet
Set ws2 = Sheets(5) 'the host sheet
ws2.activate
'run code
Set ws = ws1 'whatever sheet macro was initiated from
ws1.activate
application.screenupdating = true
end sub

Could something like that work? Does it make any sense? Apologies for the simplicity, as I'm somewhat of a vba novice.

UPDATE: so I ran the below code and it worked to update the host sheet without any error:

Private Sub cmdUpdate_Click()
    
    Dim ws1 As Worksheet
    Set ws1 = ActiveSheet
    Dim ws2 As Worksheet
    Set ws2 = Worksheets(msSHEET_NAME)

    Application.ScreenUpdating = False
    'msg box code
    ws2.Activate
    'update code
    ws1.Activate
    Application.ScreenUpdating = True
    'more code
    End Sub

The value on the host sheet changed via the macro. Why is the worksheet change event not recognizing the value change even when the macro is run while the host sheet is active? It works when I change the value on my keyboard, but not through the form.

Assuming that

If Intersect(Target, ActiveSheet.Columns(7)) Is Nothing Then Exit Sub

is the problem area then try removing ActiveSheet so that line becomes:

If Intersect(Target, Columns(7)) Is Nothing Then Exit Sub

Or try

If Intersect(Target, Worksheets("Replace this with your worksheet name but leave quotes").Columns(7)) Is Nothing Then Exit Sub

UPDATE: This is an update to my problem of updating a secondary data using an update to primary data value. Originally I was using a sheet update event, but decided that the easier solution would be to just put the code within the update routine. The title has been changed to reflect the issue. I thought maybe posting my solution may help others if they were ever so inclined to have the same issue. It is only for a command button, but I believe one can get the gist.

The userform code allows updates to list of primary values (attributes) using listbox selection through text control. Range data are used to compile a secondary dataset within separate sheet, therefore can contain many copies of primary value within secondary dataset.

When user updates original primary value through the listbox selection, dataset values compiled from the list are updated to reflect updated value. Below is final code.

Private Sub cmdUpdate_Click()    

   Dim OldValue As String
   Dim NewValue As Variant
   Dim SearchRange As Range
   Dim Found As Variant
   Dim FirstAddress As Variant
   Dim CountReplaces As Integer

   Set ws1 = Worksheets(1)
   Set ws2 = Worksheets(2)
   NewValue = frmGroups.TextBox1

      With ws1
         RowCurrent = Me.ListBox1.ListIndex + 2
         OldValue = ws1.Cells(RowCurrent, 1).Value
         .Cells(RowCurrent, 1).Value = Me.TextBox1.Text
      End With
      Set SearchRange = ws2.Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp).Address)
      With SearchRange
         Set Found = .Find(OldValue, LookIn:=xlValues)
         If Not Found Is Nothing Then
            FirstAddress = Found.Address
            CountReplaces = 0
         Do
            ws2.Range(Found.Address) = NewValue
            CountReplaces = CountReplaces + 1
            Set Found = .FindNext(Found)
         If Found Is Nothing Then Exit Do
         Loop While Found.Address <> FirstAddress
         If CountReplaces > 0 Then MsgBox ("You've been replaced!")
         End If
       End With
    TextBox1.Text = ""
    LoadListBox
End Sub