Message Box Conditional on Cell Value

[quick_question]

I'm trying to build a message box that will appear, but is conditional based on the value of another cell.

The cell that holds the value has a formula that is reacting to a slicer selection - because it is my understanding basing a message box conditional to a slicer selection directly is either not possible or inefficient.

I've created an IF(OR()) conditional formula in this cell (AS22) to evaluate the slicer selections and either assign a 1 or 0 to AS22.

I'm trying to build a message box macro that will generate a message box if the value of AS22 is 1, but not if the value is 0.

Here is what I have put together (I have put this macro into the specific tab (Controls) macro-page:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Addess = ("$AS$22") Then
If Target.Value = 1 Then
MsgBox "Message Box Test"
End If
End If
End Sub


My problem is that when I go back to the main spreadsheet to select a slicer option, and thus trigger this macro - I continue to get the error message "Run-time error "438": Object doesn't support this property or method"

I can not figure out what is going wrong, or what to change to make this functional.

Any help is greatly appreciated!!

Thanks.

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/1/2021  1:26:28 PM  EDT
If Target.Address = "$AS$22" Then
If Target.Value = 1 Then
MsgBox "Message Box Test"
End If
End If
End Sub

 

[quick_question]

No Success - same error message

 

[My Aswer Is This]

Or you can write it this way:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/1/2021  1:32:28 PM  EDT
If Target.Address = "$AS$22" And Target.Value = 1 Then MsgBox "Message Box Test"
End Sub

 

[My Aswer Is This]

Show me the exact new script you have in your sheet.
Is there any other script in the sheet?

 

[My Aswer Is This]

In your original post you spelled address wrong
You had: Addess

And you had:
("$AS$22")

It should be:
"$AS$22"

 

[quick_question]

I have no other macro's in this sheet. The only other macros I have are: (1) macro to unlock if protected and (2) a macro to clear all slicer selections.

Yes - typo...monumentally stupid mistake...Fixed - but now I don't get an error, but I also get no action from the macro.

I play with it, selecting different slicer options which change the cell being referred to (AS22) with no message box popping up.

The slicers are on a different tab from where I'm putting the macro to watch for the change in AS22 - would this have any impact on the functionality of this macro? ie. I change a slicer option on Sheet1, and the macro is embedded within Sheet4's code page.

 

[Joe4]

The "Worksheet_Change" event procedure code only runs when cells on that particular sheet are manually updated.
Cells values that changed due to formulas or links will NOT trigger this code to run.

You would probably have to use a "Worksheet_Calculate" event procedure instead.
However, note that the "Worksheet_Calculate" event procedure has no "target" range.
It cannot identify which cell's value on the sheet change. It just knows that some cell's value changed, triggering a calculation to happen.

 

[My Aswer Is This]

So if you read post #8 you will see why.
I did not know exactly how you were making changes to the cell.
I never use slicer.

 

[Joe4]

So if you read post #8 you will see why.
I did not know exactly how you were making changes to the cell.
I never use slicer.

You must have missed it in the initial question:

The cell that holds the value has a formula that is reacting to a slicer selection - because it is my understanding basing a message box conditional to a slicer selection directly is either not possible or inefficient.