Trigger worksheet change from another sheet

Beard

New Member
Joined
May 24, 2015
Messages
14
Hi,

Pardon my faulty English as it is not my native language.

I have a few sheets, where one (sheet 3) has a Worksheet_Change set up which works fine.
However, this change doesn't trigger if I programmatically from (sheet 1) trigger a change to (sheet 3).

I have tried looking online and in the help provided with Excel, but I am non the wiser as to why this change doesn't trigger the code in (sheet 3).

Does VBA not allow for this?

Any information is gratefully accepted.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Welcome to Mr Excel!

You'll need to use the Worksheet_Calculate event, and check whether the particular cell(s) you're interested in have changed value(s).
 
Upvote 0
Welcome to Mr Excel!

You'll need to use the Worksheet_Calculate event, and check whether the particular cell(s) you're interested in have changed value(s).


I assume you mean that I change the Worksheet_Change to Worksheet_Calculate?

I tried this and it still does not trigger, which leads me to think that my code is lacking or that I am changing the wrong event.

Any further advice would be very useful.

Thank you.
 
Upvote 0
Try this, where you've defined "MonitoredRange" as the range where you want to check if values have changed:

Code:
'In Code Module
Public rngMonitored As Range
Public vMonitored As Variant
'In ThisWorkbook
Private Sub Workbook_Open()

    Set rngMonitored = Range("MonitoredRange")
    vMonitored = rngMonitored.Value

End Sub
'In Sheet Module
Private Sub Worksheet_Calculate()
    
    Dim r As Long, c As Long
    
    For r = 1 To rngMonitored.Rows.Count
        For c = 1 To rngMonitored.Columns.Count
            If rngMonitored(r, c) <> vMonitored(r, c) Then
                MsgBox "Cell " & rngMonitored(r, c).Address & " has changed!"
                'Do other things
                vMonitored(r, c) = rngMonitored(r, c)
            End If
        Next c
    Next r

End Sub
 
Upvote 0
Try this, where you've defined "MonitoredRange" as the range where you want to check if values have changed:

Code:
'In Code Module
Public rngMonitored As Range
Public vMonitored As Variant
'In ThisWorkbook
Private Sub Workbook_Open()

    Set rngMonitored = Range("MonitoredRange")
    vMonitored = rngMonitored.Value

End Sub
'In Sheet Module
Private Sub Worksheet_Calculate()
    
    Dim r As Long, c As Long
    
    For r = 1 To rngMonitored.Rows.Count
        For c = 1 To rngMonitored.Columns.Count
            If rngMonitored(r, c) <> vMonitored(r, c) Then
                MsgBox "Cell " & rngMonitored(r, c).Address & " has changed!"
                'Do other things
                vMonitored(r, c) = rngMonitored(r, c)
            End If
        Next c
    Next r

End Sub

This looked promising, but, it didn't work. Or rather, it worked flawlessly, but did nothing.
I'm stumped.


Here's the code for (sheet 5) if anyone has any ideas.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)


Dim KArray(), CArray(), ksize, kloopvalue As Long


ksize = Sheet3.UsedRange.Rows.Count


ReDim KArray(ksize)


For i = 1 To ksize


    If IsNumeric(Sheet3.Range("A" & i).Value) = True And Len(Sheet3.Range("A" & i).Value) > 0 Then


        kloopvalue = kloopvalue + 1


        KArray(kloopvalue) = (Sheet3.Range("A" & i))


    End If


Next


ReDim Preserve KArray(kloopvalue)


CArray = Array("$C$15", "$C$16", "$C$18", "$C$19", "$C$21", "$C$22", "$C$24", "$C$25", "$C$27", "$C$28", "$C$30", "$C$31", "$C$33", "$C$34", "$C$36", "$C$37", _
                "$H$15", "$H$16", "$H$18", "$H$19", "$H$21", "$H$22", "$H$24", "$H$25", "$H$27", "$H$28", "$H$30", "$H$31")


For c = 0 To UBound(CArray)


    If CArray(c) = Target.Address Then
    
        For i = 0 To UBound(KArray)
        
            If Target.Value = KArray(i) Then
            
                Target.Offset(0, 2) = Sheet3.Columns(1).Find(what:=KArray(i)).Offset(2, 0)
                
            End If
        
        Next
        
    End If
    
Next


End Sub

There's no doubt this code can be cleaner as well, but right now I just want the functionality to work.

As always, grateful for any and all advice.
 
Upvote 0
I'm assuming you want to monitor changes in C15, C16, C18, ... H30, H31 in Sheet 5?

Try this code

Code:
'In Code Module
Public rngMonitored As Range
Public vMonitored As Variant
'In ThisWorkbook
Private Sub Workbook_Open()
    
    Dim i As Long
    
    Set rngMonitored = Sheet5.Range("C15, C16, C18, C19, C21, C22, C24, C25, C27," _
        & "C28, C30, C31, C33, C34, C36, C37, H15, H16, H18, H19, H21, H22, H24, H25, H27, H28, H30, H31")
    ReDim vMonitored(1 To rngMonitored.Count)
    
    For i = 1 To rngMonitored.Count
        vMonitored(i) = rngMonitored(i).Value
    Next i

End Sub
'In Sheet5 Module
Private Sub Worksheet_Calculate()
    
    Dim i As Long
    
    For i = 1 To rngMonitored.Count
        If rngMonitored(i) <> vMonitored(i) Then
            MsgBox "Cell " & rngMonitored(i).Address & " has changed!"
            vMonitored(i) = rngMonitored(i)
            'Do stuff here
        End If
    Next i

End Sub

Set Sheet5!C15 = Sheet1!A1 (say).

Close and re-open the workbook, or just run Sub Workbook_Open, to initialise

Then change Sheet1!A1.

The Worksheet_Calculate macro should trigger?
 
Upvote 0
I'm assuming you want to monitor changes in C15, C16, C18, ... H30, H31 in Sheet 5?

Try this code

Code:
'In Code Module
Public rngMonitored As Range
Public vMonitored As Variant
'In ThisWorkbook
Private Sub Workbook_Open()
    
    Dim i As Long
    
    Set rngMonitored = Sheet5.Range("C15, C16, C18, C19, C21, C22, C24, C25, C27," _
        & "C28, C30, C31, C33, C34, C36, C37, H15, H16, H18, H19, H21, H22, H24, H25, H27, H28, H30, H31")
    ReDim vMonitored(1 To rngMonitored.Count)
    
    For i = 1 To rngMonitored.Count
        vMonitored(i) = rngMonitored(i).Value
    Next i

End Sub
'In Sheet5 Module
Private Sub Worksheet_Calculate()
    
    Dim i As Long
    
    For i = 1 To rngMonitored.Count
        If rngMonitored(i) <> vMonitored(i) Then
            MsgBox "Cell " & rngMonitored(i).Address & " has changed!"
            vMonitored(i) = rngMonitored(i)
            'Do stuff here
        End If
    Next i

End Sub

Set Sheet5!C15 = Sheet1!A1 (say).

Close and re-open the workbook, or just run Sub Workbook_Open, to initialise

Then change Sheet1!A1.

The Worksheet_Calculate macro should trigger?

I found a fault elsewhere in my code that stopped this from working, I apologise for wasting your time.

I shall post again when I have figured out this other problem.

Thank you for the help.
 
Last edited:
Upvote 0
Thanks for the update.

Not a problem. Just post back when you're ready.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top