Thread: Selective Autoshape Hiding from data validation

Hello,

I am working working on a spread sheet that has 2 auto-shapes. The auto-shapes are labeled a and b. I have a drop down on the sheet that has the values a and b. What i am shooting for here, is that only the autoshape displayed is the same as the dropdown. So drop down to a and only the autoshape a shows and b does not. Also none show when no value is selected or the cell is blank. I am trying if statements in the vba but not sure on the else statements.
Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("B4").Value = "A" Or Range("B4").Value = "" Then
        ActiveSheet.Shapes("Rectangle 10").Visible = False
    If Range("B4").Value = "B Or Range("B4").Value = "" Then
        ActiveSheet.Shapes("Rectangle 11").Visible = False
    End If
    End If
End Sub

Originally Posted by royUK

Try this. Your shapes must be named a and b

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$B$4" Then Exit Sub
 
    Select Case Target.Value
    Case "a"
        ActiveSheet.Shapes("b").Visible = False
        ActiveSheet.Shapes("a").Visible = True
    Case "b"
        ActiveSheet.Shapes("a").Visible = False
        ActiveSheet.Shapes("b").Visible = True
    End Select
End Sub

I tried the code, and both shapes seemed to have vanished from the workbook, i then recreated them, but after putting in the code after adjusting it accordingly to names and what not, if the value of lets say A2 which i have referenced in a test sheet is blank, both boxes should be hidden until data is selected. I have a test sheet i have attached.

Here is the code i modified it to:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$2" Then Exit Sub
 
    Select Case Target.Value
    Case "A"
        ActiveSheet.Shapes("B").Visible = False
        ActiveSheet.Shapes("A").Visible = True
    Case "B"
        ActiveSheet.Shapes("A").Visible = False
        ActiveSheet.Shapes("B").Visible = True
    End Select
End Sub

Thanks for your help RoyUK, after tweaking a bit, was able to get it to work, I added an addition case "" to run for the cell in reference being blank. And it works like a charm. Thanks again

I do have a follow up question for this issue. Would the same hold true for having this apply to a command button?