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

Magdoulin

Board Regular
Joined
Jan 11, 2013
Messages
73
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:

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:
Code:
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.
 
What does this mean:
I don't know what is wrong especially that your event worked just fine in new file

You earlier said my script did not work.
Now your saying it worked.

Do you have other scripts in the sheet?

Show me all the code you have in your sheet.
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
I was about to mention this to you right now
Because I noticed something even with my new test sheet
Look, M1 doesn't change manually
it's linked with ComboBox value
It'll be empty initially when the file is opened
and changes to something else when the user selects value from the ComboBox

So here, even with new file just to test your code, it doesn't work

I believe we figured out what is the issue by now
but how to solve it?

You earlier said my script did not work.
Now your saying it worked.

I was mentioning that your code worked when I put it in a blank new file
But it didn't work in my own project
And I believe this happened for the above reason I mentioned, that M1 doesn't update manually, it's by linked ComboBox
 
Last edited:
Upvote 0
So not sure if you need more help or not.

So your saying you have a combobox that changes the value in M1

Show me the code that changes the value in range M1
 
Upvote 0
No, the issue isn't solved yet I'm afraid
We just know now why it doesn't work
But we haven't reached the solution yet

Ok
So, M1 value changes by ComboBox in the same sheet which is Home
Now, the ComboBox changes M1 value not by code
I do this through the ComboBox Properties directly by using the Linked Cell field
if I open the ComboBox code it's empty, I'm not sure how this'd help, but here's its code:

Code:
Private Sub ComboBox1_Change()


End Sub

Can the Sheet Change Event work in this case by any modification?
Should we replaced M1 with the ComboBox directly in the event script? But if so, how we could do that?
 
Upvote 0
Well when I try it the way you just described it does not work.

Why exactly you want to do it this way is something I do not know.

Why do you need a combobox to enter nothing into a cell to hide or unhide a shape?

Why not enter the value manually?

I'm not familiar with using Comboboxe's with no scripts but linking them in the properties window.

Maybe someone else here at Mr. Excel will have a answer.
 
Upvote 0
Well, explaining the reason of using that way will take a while :D
All I could say that there is a valid point for that
Well, let us wait for someone then to assist with this
Thank you so much
At least you helped me detecting the issue
 
Upvote 0
Normally we use a combobox when we want to choose from a whole list of items.

But it looks like to me you want something to happen when M1 equals nothing but do this when M1 equals anything other then nothing.

Even when I put a script in the combobox it does not like operating off nothing.

Why not say if M1 equals 12 do this otherwise do this?
 
Upvote 0
Ok, I believe I got it by now
Basically, we could set the event to the ComboBox itself and it'll work in this case
For the benefit, I put the resolving code then:
Code:
Private Sub ComboBox1_Change()
If Me.ComboBox1.Value = "" Then
ActiveSheet.Shapes("Rectangle 1").Visible = False
Else
ActiveSheet.Shapes("Rectangle 1").Visible = True
End If
End Sub
 
Upvote 0
Yes this does work.
Originally you were wanting a auto sheet change event script tied to a combobox with no script.

Glad to see you know how to write code to do what you want.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top