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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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).
 

Beard

New Member
Joined
May 24, 2015
Messages
14
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.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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
 

Beard

New Member
Joined
May 24, 2015
Messages
14

ADVERTISEMENT

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.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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?
 

Beard

New Member
Joined
May 24, 2015
Messages
14
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:

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
Thanks for the update.

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

Watch MrExcel Video

Forum statistics

Threads
1,130,108
Messages
5,640,139
Members
417,127
Latest member
shakilk

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
Top