Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Run a Macro When a Specific Cell Changes in Excel

0

Hi

The Macro provided in email update of 6th Sept 17 shown below works OK when you type an entry into the specific cell range. However if there is a formula in the cell range and the value changes in any of the specified range of cells  - the message "Hi" will not display.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A5")) Is Nothing Then

    MsgBox "Hi!"

End If

End Sub

Is there a way to write the VBA so that if the value in a range of cells changes (As a result of the formula showing a different result)  the alert will be shown?

Thanks

Pat

excel
posted by: PatFF (rep: 2) on Thu Sep 14, 2017 at 12:17 pm
Answer
Discuss

Answers

0
Selected Answer

Posted 9-15-17

I believe it may be better to have Conditional formatting (perhaps RED for not= 0, and GREEN if 'Balanced'/ =0) may be better option.  OR try summing your B/S check and have macro to alert you if it does/doesnt balance...

Sub Button1_Click()
  If Sheet10.Range("I61") <= Abs(0.95) Or Sheet10.Range("I61") >= Abs(-0.95) Then

    MsgBox "           Balance Sheet  Balanced"
    Else
    MsgBox "Balance Sheet not Balanced"

  End If
   
End Sub

Newer Comments:

I'm still not sure I understand the question.  What is the alert range? If you want, you can have Conditional formatting if the 'Check' does NOT equal 0 (put it in red).

Does your Alert range need to be expanded to include the B/S Check?

----Older comments below

It seems to be working fine for me.  Please upload your example - in case I don't understand your question.

posted by: queue (rep: 467) on Thu Sep 14, 2017 at 1:29 pm
Discuss

Discussion

Hi Thank you for getting back to me. I have uploaded a sample file with some additional notes to dropbox - see link below

https://www.dropbox.com/s/7acj5p2bly05qcj/Balance_Sheet_Alert.xlsm?dl=0

If you need more info please let me know. Pat
PatFF (rep: 2) Sep 14, '17 at 3:33 pm
Ahh, I'm starting to see it. Had to download file. I'll try to check back
queue (rep: 467) Sep 14, '17 at 5:34 pm
Hi As you may see I answered another post by mistake.
Is there any further information you require? Pat  
PatFF (rep: 2) Sep 15, '17 at 5:09 am
Hi
I have used another VBA workaround which works reasonably well
I have changed the range "alert" to cells B63:G63
Which contain a "Yes" when = 0 or "No" if not equal to 0

VBA Code

Private Sub Worksheet_Calculate()
    Dim myRange As Range
    Set myRange = ActiveSheet.Range("alert")
    Dim cell As Range

    For Each cell In myRange
        Evaluate (cell)
        If StrComp(cell, "Yes", vbTextCompare) = 0 Then
            MsgBox "Balance Sheet is out of Balance!"
        End If
        Exit For
        Next
    End Sub

One disadvantage is that the message pops up again when I undo the error and the cell value changes back to Yes"

Drop box link to updated file https://www.dropbox.com/s/ehasqmkbrerqc6l/Balance_Sheet_Alert4.xlsm?dl=0

Thanks
PatFF (rep: 2) Sep 17, '17 at 7:04 am
Add to Discussion
0

No, there is no such way. The applicable rule is that many cooks spoil the broth. Why should you use a worksheet formula to change values on a worksheet if you intend to use VBA to catch the change? Just change your setup to let VBA set the values of the cells whose change you need to be alerted to.

posted by: Variatus (rep: 4889) on Thu Sep 14, 2017 at 8:46 pm
Discuss

Discussion

Hi
This balance sheet is one sheet in a larger workbook with profit & Loss, cash flows etc for each year. The balance sheet should always balance so I need to know straight away when I do something in another part of the workbook that causes the balance sheet to go out of balance. (any of the cells in the alert range changes from zero) Otherwise, I will find it hard later on to know which action caused the problem.

Using conditional formatting to change the colour of the alert range will not help because I will be working in another worksheet and will not see it unless I keep checking the balance sheet after every single action.

Triggering an alert message will work if I can write the VBA to do it.
Pat
PatFF (rep: 2) Sep 14, '17 at 10:40 pm
You have already accepted an answer to this question. If you want my answer, please give me a question where you can award points to me.
Variatus (rep: 4889) Sep 14, '17 at 11:49 pm

My Apologies
I am new to the forum. I was just answering you post.
PatFF
PatFF (rep: 2) Sep 15, '17 at 5:07 am
I realise that, and I'm not blaming you at all, but I would like you to select my answer and since you already selected another solution, I neither should nor would post my answer here. I think the question has been changed by your recent explanation. It has become sufficiently different to be a new question, and if you ask a new question new answers are automatically invited among which you will be able to select. - Everybody happy. If you give me an opportunity to post my solution I think it may interest you.
Variatus (rep: 4889) Sep 15, '17 at 5:32 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login