Show/Hide rectangles based on cell value

[lol.xls]

Here's my scenario: I have a combobox with 4 values and 4 rectangles named after the combobox values. When the value is selected from the combobox, i want the appropriate rectangle to become visible. If the value in the combobox changes, I need the visibility of the rectangles to change accordingly as well. Here is what I have so far...any thoughts on why my rectangles aren't changing?

Below is within a Module:

Sub YourMacro()
    Dim myshape As Shape
    Dim Sshape As Shape
    Dim sh As Worksheet


    Application.ScreenUpdating = False


    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> "Sheet4" Then
            sh.Select
            For Each myshape In sh.Shapes
                If myshape.Type = msoShapeRectangle Then
                    myshape.Visible = False
                End If
            Next myshape
            
            On Error Resume Next
            Set Sshape = Nothing
            Set Sshape = sh.Shapes((Sheets("Sheet4").Range("Q7").Value))
            On Error GoTo 0


            If Not Sshape Is Nothing Then
                With sh.Shapes(Sheets("Sheet4").Range("Q7").Value)
                    .Visible = True
                End With


            End If
        End If
    Next sh
    Application.ScreenUpdating = True
End Sub

This Code is contained within the sheet to call the aforementioned code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("Q7"), Target) Is Nothing Then
        If Target.Value <> "" Then
            Call YourMacro
        End If
    End If
End Sub

 

[igold]

Hi,

Are you sure that when you change the value of the combobox that you are actually triggering the code. You could make this change that will give you a msgbox to be sure...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("Q7"), Target) Is Nothing Then
        If Target.Value <> "" Then
            MsgBox "running"
            Call YourMacro
        End If
    End If
End Sub

igold

 

[lol.xls]

Yeah...I'm dumb. The code is fine. I had renamed Sheet4, and assumed that since it was still technically Sheet4, it would work. When I renamed the sheet everything executed just fine.

 

[igold]

I am glad you got it squared away.