CODEWITHSUNDEEP
arraysexcelvba
bkraffa
bkraffa

Reputation: 17

Hide/Show multiple objects based on a specific cell

I created flags to combine in order to hide and show graphics according to the value of a specific cell.

The cell is U47 and graphics I need to appear according to the possible combinations are:

11, 21, 31 -> Graphic PreSeg

12, 22, 32 -> Graphic PosSeg

41 -> Graphic PreTot

42 -> Graphic PosTot

I tried the code below and the graphics PreTot and PosTot are working, but when I click the button to change the Flag to 11, 21, 31, 12, 22 or 32 the graphics PreSeg and PosSeg won't show up.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

If UCase(ActiveSheet.Cells(47, 21)) = "41" Then
    ActiveSheet.Shapes.Range(Array("PreTot")).Visible = True
Else
    ActiveSheet.Shapes.Range(Array("PreTot")).Visible = False
End If
            
If UCase(ActiveSheet.Cells(47, 21)) = "42" Then
    ActiveSheet.Shapes.Range(Array("PosTot")).Visible = True
Else
    ActiveSheet.Shapes.Range(Array("PosTot")).Visible = False
End If

If UCase(ActiveSheet.Cells(47, 21)) = "11" Then
    ActiveSheet.Shapes.Range(Array("PreSeg")).Visible = True
Else
    ActiveSheet.Shapes.Range(Array("PreSeg")).Visible = False
End If

If UCase(ActiveSheet.Cells(47, 21)) = "21" Then
    ActiveSheet.Shapes.Range(Array("PreSeg")).Visible = True
Else
    ActiveSheet.Shapes.Range(Array("PreSeg")).Visible = False
End If

If UCase(ActiveSheet.Cells(47, 21)) = "31" Then
    ActiveSheet.Shapes.Range(Array("PreSeg")).Visible = True
Else
    ActiveSheet.Shapes.Range(Array("PreSeg")).Visible = False
End If

If UCase(ActiveSheet.Cells(47, 21)) = "12" Then
    ActiveSheet.Shapes.Range(Array("PosSeg")).Visible = True
Else
    ActiveSheet.Shapes.Range(Array("PosSeg")).Visible = False
End If

If UCase(ActiveSheet.Cells(47, 21)) = "22" Then
    ActiveSheet.Shapes.Range(Array("PosSeg")).Visible = True
Else
    ActiveSheet.Shapes.Range(Array("PosSeg")).Visible = False
End If

If UCase(ActiveSheet.Cells(47, 21)) = "32" Then
    ActiveSheet.Shapes.Range(Array("PosSeg")).Visible = True
Else
    ActiveSheet.Shapes.Range(Array("PosSeg")).Visible = False
End If

End Sub

Upvotes: 0

Views: 222

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

Try this code: 

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$U$47" Then

        'Hide all shapes first. May need to add condition if
        'some shapes shouldn't be hidden.
        Dim shp As Shape
        For Each shp In ActiveSheet.Shapes
            shp.Visible = False
        Next shp

        Select Case Target.Value
        'Select Case ActiveSheet.Cells(47, 21).Value
            Case 41
                ActiveSheet.Shapes("PreTot").Visible = True
            Case 42
                ActiveSheet.Shapes("PosTot").Visible = True
            Case 11, 21, 31
                ActiveSheet.Shapes("PreSeg").Visible = True
            Case 12, 22, 32
                ActiveSheet.Shapes("PosSeg").Visible = True
        End Select
    End If

End Sub

Upvotes: 2

Related Questions