Macro Not running when cell value change

[Samfoo]

Hi,

I am trying to do something like this :

>>> use code - tags <<<

Sub RecordData()
xVal = Range(“A1”).value
If  cal <>0 Then
   Msg “hello”
End if
End

In A1 I have a formula to set it to 1 based on a condition.

I call RecordData from trigger Worksheet_Change().

But when A1 becomes 1, worksheet_change() is not getting invoked.

Any help is greatly appreciated. If you want me to put all the code let me know.

 

[Logit]

Post your code. Attach file if possible.

 

[Samfoo]

Hi Logit,

Thank you for replying back. Attaching file with the Sheet/Module level code and the formula used to set a cell.

 

[ExcelGoogler]

Samfoo,

I am relatively new to answering questions here but rather than trying to understand your code, it may be better for me to answer your implied question. There are too many assumptions to make from your Codebase.txt file.

Your issue seems to stem from your assertion that "worksheet_change() is not getting invoked". If you have provided "ALL" the code in this excel file then I don't believe this is the case. To help you see that the event is triggered I will also try to help with the first half of your post.

Add 1 new line below the change event and above your code in your worksheet class. Don't alter anything else from what you have in codebase.txt. Like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    TestChange Target
' All your code stays the same below this....

Then to return a message box after the change in VBA add a new module with the following:

Option Explicit

Public Sub TestChange(ByVal ChangedTarget As Range)
    If ChangedTarget.Cells.CountLarge = 1 Then
        MsgBox "Hello!  You have changed cell " & ChangedTarget.Address & _
               " to a value of '" & ChangedTarget.Value & "'." _
               , vbOKOnly + vbInformation, "Change Event Triggered"
    End If
End Sub

I hope this helps!
Cheers!

 

[NoSparks]

Cells containing formulas do not trigger the Worksheet_Change event.
Despite how the results of the formula may change what Excel sees in the cell (ie: the formula) does not change.

Maybe this thread from a few years ago will be of some use.

 

[ExcelGoogler]

Awesome catch NoSparks!! I guess I spent too much time trying to figure out what was going on in the file and missed the formula statement.

So, Samfoo, based on your original post, just add this to your worksheet class to see when A1 is calculated to 1:

Private Sub Worksheet_Calculate()
    If Range("A1") = 1 Then TestChange Range("A1")
End Sub

 

[NoSparks]

@ExcelGoogler,
Without knowing what the value of A1 is/was prior to recalculation of the sheet triggering the Worksheet_Calculate event
you have nothing to compare the current value with to know if it has changed.

 

[ExcelGoogler]

@ExcelGoogler,
Without knowing what the value of A1 is/was prior to recalculation of the sheet triggering the Worksheet_Calculate event
you have nothing to compare the current value with to know if it has changed.

100% true and clearly addressed by both suggestions in the thread you've provided. Samfoo could capture it and compare if needed, not so sure from the code provided, using either of their methods or even a property. There are many ways to approach it depending on what the final data model will need. However, the OP seems primarily focused on the value in A1 having a value of one to run a subsequent routine.

@Samfoo
As @NoSparks points out here, if you do not relieve the condition that sets A1 to a value of 1 in either "TestChange" or "RecordData" you will get that message box for any other cells calculated while A1=1. That could be a lot of message boxes...

I would highly recommend that you layout/plan from beginning to end what it is you intend to build for yourself or your client/employer, then try to build it step by step. There are many decisions to make in this process but a major one is what skillset will be needed to change/maintain/update what you build. There are always costs to consider. However, as it seems you are doing, have lots of fun learning and being creative.

Cheers!