Show/Hide shape based on cell value

[PCRIDE]

I've searched around and can't seem to find the exact answer. I have a shape on my sheet called Dashboard, I then have a master calculator tab which will display a message based on the values of the cells I am calculating. I need to show the shape if the value is 1, hide the shape if the value is 2.

The trouble I am having is referencing another sheet where the code is for the VB. Its probably simple I am sure. I need to reference the Sheet by names, not Active sheet if that makes sense.

How do I modify this code? My cell value 1 or 0 will be in the Calculator Sheet, in cell T10, the shape is on the Dashboard sheet called Flag1

Private Sub Worksheet_Activate()
If Range("A1").Value = "DR" Then
ActiveSheet.Shapes("Flag1").Visible = False
Else
ActiveSheet.Shapes("Flag1").Visible = True
End If
End Sub

 

[My Aswer Is This]

I'm having a hard time understanding your question

Here is a example of how part of your script should read

This script assumes your sheets are named "Master" and your shape on sheet "Master" is named "Me"
And we will be looking in Range("A1") of sheet named "Calculator"

The script will look in Range("A1") and if the value is 1 then the shape will be visible. If Range("A1") value is 2 then shape will be hidden.

Modify this script to you needs.

This script is a module script so you can test it. If you want it in a sheet activate script just copy the code.

Sub Make_Me_Visible()
'Modified 7/26/2019 11:47:22 PM  EDT
Application.ScreenUpdating = False
With Sheets("Master").Shapes("Me")
        Select Case Sheets("Calculator").Range("A1").Value
            Case 1
                .Visible = True
            Case 2
                .Visible = False
        End Select
    End With
End Sub

 

[Fluff]

How about

Private Sub Worksheet_Activate()
   With Me.Shapes("flag1")
      .Visible = Sheets("Calculator").Range("T10").Value = 1
   End With
End Sub

This needs to go in the Dashboard sheet module

 

[PCRIDE]

I'm having a hard time understanding your question

Here is a example of how part of your script should read

This script assumes your sheets are named "Master" and your shape on sheet "Master" is named "Me"
And we will be looking in Range("A1") of sheet named "Calculator"

The script will look in Range("A1") and if the value is 1 then the shape will be visible. If Range("A1") value is 2 then shape will be hidden.

Modify this script to you needs.

This script is a module script so you can test it. If you want it in a sheet activate script just copy the code.

Sub Make_Me_Visible()
'Modified 7/26/2019 11:47:22 PM  EDT
Application.ScreenUpdating = False
With Sheets("Master").Shapes("Me")
        Select Case Sheets("Calculator").Range("A1").Value
            Case 1
                .Visible = True
            Case 2
                .Visible = False
        End Select
    End With
End Sub

Thank you, I got this working, however it only works when I play the macro. Shouldn't it automatically notice the change from 1 or 2 and show/hide the shape?

 

[My Aswer Is This]

Not sure what you mean by:
I got this working. Sounds like your saying you had to modify my script to get it working.
I mentioned earloier

I'm having a hard time understanding your question.

So you want this script to activate when you open your sheet ?? and What is:

display a message based on the values of the cells I am calculating.

See you never explained what this means.


Show me the script you have that works for you and tell me what should activate the script​

​

Thank you, I got this working, however it only works when I play the macro. Shouldn't it automatically notice the change from 1 or 2 and show/hide the shape?

 

[DanteAmor]

I've searched around and can't seem to find the exact answer. I have a shape on my sheet called Dashboard, I then have a master calculator tab which will display a message based on the values of the cells I am calculating. I need to show the shape if the value is 1, hide the shape if the value is 2.

The trouble I am having is referencing another sheet where the code is for the VB. Its probably simple I am sure. I need to reference the Sheet by names, not Active sheet if that makes sense.

How do I modify this code? My cell value 1 or 0 will be in the Calculator Sheet, in cell T10, the shape is on the Dashboard sheet called Flag1

Private Sub Worksheet_Activate()
If Range("A1").Value = "DR" Then
ActiveSheet.Shapes("Flag1").Visible = False
Else
ActiveSheet.Shapes("Flag1").Visible = True
End If
End Sub

What do you have in T10, a formula? If so, you can put the formula here.

If it's a formula, then put the following event in ThisWorkbook.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
  Sheets("Dashboard").Shapes("flag1").Visible = Sheets("Calculator").Range("T10").Value = 1
End Sub

If the formula on the calculator sheet is updated, then the shape can be showed or hidden automatically without changing the sheet.

 

[PCRIDE]

Not sure what you mean by:
I got this working. Sounds like your saying you had to modify my script to get it working.
I mentioned earloier

I'm having a hard time understanding your question.

So you want this script to activate when you open your sheet ?? and What is:

display a message based on the values of the cells I am calculating.

See you never explained what this means.


Show me the script you have that works for you and tell me what should activate the script​

​

So I just need the shape to show/hide based on the cell value. The cell value changes based on drop downs and other selections in the sheet.

Sub Make_Me_Visible()
'Modified 7/26/2019 11:47:22 PM EDT
Application.ScreenUpdating = False
With Sheets("Dashboard").Shapes("Flag2")
Select Case Sheets("GraphMatrix").Range("AH25").Value
Case 1
.Visible = True
Case 2
.Visible = False
End Select
End With
End Sub

 

[DanteAmor]

If you are selecting the value 1 or 2 in cell AH25 then put the following Event code on your "GraphMatrix" sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "AH25" Then
        If Target.Count > 1 Then Exit Sub
        Select Case Sheets("GraphMatrix").Range("AH25").Value
            Case 1
                Sheets("Dashboard").Shapes("Flag2").Visible = True
            Case 2
                Sheets("Dashboard").Shapes("Flag2").Visible = False
        End Select
    End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
To install, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[My Aswer Is This]

Please be more specific. You said:

So I just need the shape to show/hide based on the cell value. The cell value changes based on drop downs and other selections in the sheet.

When what cell value changes??
​