Good afternoon,
I have a 3 shapes on my worksheet functioning as buttons, and I need one of those three to stay invisible unless certain criteria are met (specifically, cell AU2 equaling 1). Then, once the it becomes visible, I need it to disappear again after it is clicked (since clicking it will run another macro to zero out cell AU2). I hope this is enough information to help. Thank you in advance for your time!
DT
Re: Hide shape if cell value equals specific amount
Hello,
There are a few steps to this. In the sheet module which AU2 is located, put this code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws as worksheet
Dim ButtonName as string
Set ws = Sheets("Sheet1") 'Change this to the sheet which the shape is located in
ButtonName = "CommandButton1" 'Change this to the name of the shape
If Target.Address = Range("AU2").Address And Target.Value = 1 Then
ws.Shapes(ButtonName).Visible = True
End If
End SubIn here you will need to update the sheet name and the name of the shape which I have set as variables.
This will show the button anytime you change the value of AU2 to 1 (You have to make the change. A formula making the change wont work. Let me know if a formula or macro is updating cell AU2, if it is a formula I need to know what the input cell is which updates AU2.)
Now for the button to be hidden after it's clicked use this within the sheet module of the button:
Private Sub CommandButton1_Click()
'Execute macro here
ActiveSheets.Shapes("CommandButton1").Visible = False
End SubHere you will need to udate the shape name in the title of the sub and in the line of code.
Let me know if this doesn't work or if you have any questions.
Sincerely,
Max
Re: Hide shape if cell value equals specific amount
Hi Max,
Thank you for the help! The change of value in AU2 is indeed running from a formula. It is coming from my reference tab in cell W14 (Reference!W14). This is where the calculation is taking place to determine whether or not the button should be hidden.
Thank you!
DT
Re: Hide shape if cell value equals specific amount
Hi DT,
What's the name of the sheet with AU2? And is Reference the name of the sheet with the button?
Re: Hide shape if cell value equals specific amount
Hi Max,
The sheet with the button is called Sales LY (Monthly). Right now that is the same sheet with AU2 on it. However, if it can come straight off my reference sheet and not have to refer back, that would be nice too.
The name of my reference sheet is Reference, and the cell that AU was referencing is W14.
Thank you,
DT
Re: Hide shape if cell value equals specific amount
Hi DT,
Put this in the "Reference" Sheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim ButtonName As String
Set ws = Sheets("Sales LY (Monthly)") 'Change this to the sheet which the shape is located in
ButtonName = "CommandButton1" 'Change this to the name of the shape
If Target.Address = Range("W14").Address And ws.Range("AU2") = 1 Then
ws.Shapes(ButtonName).Visible = True
End If
End SubUse the code I gave you for the button earlier, just update the button names.
Also double check to make sure I didn't spell the sheet names wrong and update the shape name.
*Keep in mind this will not work if you are in design mode for your shapes
Sincerely,
Max
Re: Hide shape if cell value equals specific amount
Hi Max,
In the tab with the button (which is also the tab the value in cell AU2), I put the following code:
Private Sub CommandButton1_Click()
'Execute macro here
ActiveSheets.Shapes("Please press to clear data from 5th Week").Visible = False
End SubAnd in the reference tab, I put the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim ButtonName As String
Set ws = Sheets("Sales LY (Monthly)") 'Change this to the sheet which the shape is located in
ButtonName = "Please press to clear data from 5th Week" 'Change this to the name of the shape
If Target.Address = Range("W14").Address And ws.Range("AU2") = 1 Then
ws.Shapes(ButtonName).Visible = True
End If
End SubBut, it's not doing anything. Did I do all the steps correctly?
Thanks,
DT
Re: Hide shape if cell value equals specific amount
You know, I have a another workbook that does this function but I didn't make it, so I'm not sure whether or not the code can be adapted to this workbook.
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
On Error Resume Next
If Sheets("Sales LY (Monthly)").Range("AU2").Value <> PrevVal Then
Call unhidemail 'not sure what this line is... perhaps the name of the macro that unhides the button to mail the form out?
PrevVal = Sheets("Sales LY (Monthly)").Range("AU2").Value
End If
On Error Resume Next
Application.EnableEvents = True
End SubI changed parts to reference my sheet, but I'm not sure what the "Call unhidemail" part means, or what PreVal means. I'm thinking I should change the line
to say
but I'm still not getting anywhere.
Thanks,
DT
Re: Hide shape if cell value equals specific amount
Hi DT,
Could you link your workbook so I can check it? In the test workbook that I made it's working fine. Whenever I change W14 and the result of that change updates AU2 to "1" Then the button appears.
Also I think you put the wrong name for your shape. The shape's name cannot contain spaces. If you right click the shape while in designer mode, click Properties and the name is the very first property listed. Also don't forget to update the name of the shape in the title of the click button sub:
Private Sub ShapeName_Click()
'Execute macro here
ActiveSheets.Shapes("ShapeName").Visible = False
End Sub
Sincerely,
Max
Re: Hide shape if cell value equals specific amount
Hi Max,
That was precisely the problem. I just looked up my shape names and changed it in the code and now it works like a champ.
Thanks for the help!
DT
Re: Hide shape if cell value equals specific amount
Glad I could help!
Let me know if you want any of it explained!
Don’t have an account yet? Register yourself now and be a part of our community!