VBA to Hide/Unhide Shape Automatically When Cell Value Is Changed

[Magdoulin]

Hello Guys,


I'm trying to set event in my sheet that when the value of Cell "M1" in "Sheet Home" is changed to unhide specific shape.

I used the following event and code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M1")) Is Nothing Then HideUnhideShape
End Sub


Sub HideUnhideShape()
If Worksheets("Home").Range("M1") = "" Then
ActiveSheet.Shapes("Rectangle 1").Visible = False
Else
ActiveSheet.Shapes("Rectangle 1").Visible = True
End If
End Sub

I've tried this as well:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M1")) Is Nothing Then Call HideUnhideShape
End Sub


Sub HideUnhideShape()
If Worksheets("Home").Range("M1") = "" Then
ActiveSheet.Shapes("Rectangle 1").Visible = False
Else
ActiveSheet.Shapes("Rectangle 1").Visible = True
End If
End Sub

None of them worked although the code separately worked just fine, it seems that the issue is with the event

I'm saving the event in the "Home" sheet macro whereas the code in separated Module

I wish this forum allows to attach my file with the post to ease for you checking the issue, would you please help, thank you.

 

[My Aswer Is This]

Are you trying to run a sheet change event script in one sheet and have it hide a shape in a different sheet?

I see your using sheet named "Home" and then your using Active sheet.

Is the active sheet named "Home"

 

[Magdoulin]

The shape is in Home sheet yes

anyways I changed the code in the Module to be like this but it worked separately perfectly but not with the event:

Sub HideUnhideShape()
If Worksheets("Home").Range("M1") = "" Then
Worksheets("Home").Shapes("Rectangle 1").Visible = False
Else
Worksheets("Home").Shapes("Rectangle 1").Visible = True
End If
End Sub

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M1")) Is Nothing Then
    
    If Target.Value = "" Then
    ActiveSheet.Shapes("Rectangle 1").Visible = False
Else
    ActiveSheet.Shapes("Rectangle 1").Visible = True
    End If
End If
End Sub

 

[Magdoulin]

It didn't work
I'm sorry

Note
It worked in new sheet
However, it didn't work with my particular project
It seems that there's something wrong with it
This of course requires to attach the file of my project
The forum doesn't allow this
I don't know how to continue then

 

[My Aswer Is This]

What is the name of the sheet where you are changing the value in range "M1" ?
And what is the name of the sheet with the shape you want to hide?

 

[Magdoulin]

All is in Home
the sheet is called Home

 

[My Aswer Is This]

Assuming now we are dealing with two different sheets try this:
Put this script in the sheet where you plan to change the value in range "M1"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M1")) Is Nothing Then
    
    If Target.Value = "" Then
    Sheets("Home").Shapes("Rectangle 1").Visible = False
Else
    Sheets("Home").Shapes("Rectangle 1").Visible = True
    End If
End If
End Sub

 

[Magdoulin]

No, both of them in the same sheet
I don't know what is wrong especially that your event worked just fine in new file

 

[My Aswer Is This]

Well if we are only dealing with one sheet then my script should have worked.

When you say it did not work you need to explain what it did.

Are you changing the value in M1 manually of is the change being done by a formula causing the change?