Called macro doesn't do anything

[Limone]

Hi.
I'm trying to call a macro using a Worksheet_Change Event but everytime I run it nothing happens. The event itself works great and recognized that the macro doesn't exist after I tried deleting it. I've also tried testing my macro using the Immediate Window feature and everything worked as it was supposed to.

Here are my codes, thanks in advance

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$W$6" Then
        Call MyMacro
    End If
End Sub

Sub MyMacro()
    If ThisWorkbook.Sheets("Sheet1").Range("Y8") = True Then
        MsgBox "hey"
        Else
        Exit Sub
    End If
End Sub

 

[Special-K99]

Instead of exiting from the MyMacro sub place another MsgBox in there after the Else
So you get some form of output regardless.

If you get a Hey then Sheet1!Y8=TRUE
If you get some other form of output then Sheet1!Y8 does not = TRUE
If you get no output then MyMacro is not being called.

 

[gallen]

Hello and welcome.

have you stepped through the change event and followed what happens? Does the 'Call MyMacro' line actually execute? If so does it then jump the correct sub?

 

[MARK858]

I just commented out everything bar the msgbox in the regular sub and the worksheet_change called it fine.
So the only things that I can think of is that this statement isn't true

If ThisWorkbook.Sheets("Sheet1").Range("Y8") = True

or
You haven't put the regular sub in a regular module i.e. you have put the code in either the sheet module or the ThisWorkbook module. It must be in a regular module.

 

[gallen]

Out of curiosity what value is actually in Sheets("Sheet1").Range("Y8")?

 

[Limone]

Instead of exiting from the MyMacro sub place another MsgBox in there after the Else
So you get some form of output regardless.

If you get a Hey then Sheet1!Y8=TRUE
If you get some other form of output then Sheet1!Y8 does not = TRUE
If you get no output then MyMacro is not being called.

Thanks for the suggestion. I get no input unfortunately.

Hello and welcome.

have you stepped through the change event and followed what happens? Does the 'Call MyMacro' line actually execute? If so does it then jump the correct sub?

I don't know how to do that unfortunately. I've changed my worksheet_change event code so that a message would pop up when the cell is changed and nothing happened, so I guess the problem might rely there?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$W$6" Then
        MsgBox "hey"
    End If
End Sub

I just commented out everything bar the msgbox in the regular sub and the worksheet_change called it fine.
So the only things that I can think of is that this statement isn't trueCode:
If ThisWorkbook.Sheets("Sheet1").Range("Y8") = True
or
You haven't put the regular sub in a regular module i.e. you have put the code in either the sheet module or the ThisWorkbook module. It must be in a regular module.

The statement is true. As I said, I tested it using the Immediate Window function and it worked.
I've saved the event as a Microsoft Excel Object and the macro as a module.

Out of curiosity what value is actually in Sheets("Sheet1").Range("Y8")?

A COUNTIF formula that returns either TRUE or FALSE and I want the message to pop up in case it returns TRUE.

 

[MARK858]

Try

If ThisWorkbook.Sheets("Sheet1").Range("Y8") = "TRUE"

 

[Limone]

Sorry for the awful formatting, I've never really been on a forum before and I don't have time now to get the hang of it

 

[Limone]

Try

If ThisWorkbook.Sheets("Sheet1").Range("Y8") = "TRUE"

Tried your suggestion, using both English and my native language, and it didn't work. I guess the problem might be about the event not firing (even though excel does warn me when something's wrong with the code) but I'm not sure how I can test it.

 

[Norie]

What's the formula in Y8?