Worksheet Change help

[andrewb90]

I am trying to make certain objects hide automatically based on the number typed into a cell. I thought I was doing okay, but the second part of my code doesn't seem to be functioning.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address(False, False) = "A12" Then
        If Len(Target.Value) = 1 Then
            ActiveSheet.Shapes("G_1").Visible = True
            ActiveSheet.Shapes("G_2").Visible = False
            ActiveSheet.Shapes("G_3").Visible = False
            ActiveSheet.Shapes("G_4").Visible = False
        End If
    End If
    If Target.Address(False, False) = "A12" Then
        If Len(Target.Value) = 2 Then
            ActiveSheet.Shapes("G_1").Visible = False
            ActiveSheet.Shapes("G_2").Visible = True
            ActiveSheet.Shapes("G_3").Visible = False
            ActiveSheet.Shapes("G_4").Visible = False
        End If
    End If
End Sub

I'm hoping this is just something pretty simple that I am not doing correctly.

 

[andrewb90]

I should add that I am trying to hide/unhide shapes that all have the same name. I don't know if there is an alternative way to hide what I want.

 

[VoG]

Perhaps

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) <> "A12" Then Exit Sub
Select Case Len(Target.Value)
    Case 1
        ActiveSheet.Shapes("G_1").Visible = True
        ActiveSheet.Shapes("G_2").Visible = False
        ActiveSheet.Shapes("G_3").Visible = False
        ActiveSheet.Shapes("G_4").Visible = False
    Case 2
        ActiveSheet.Shapes("G_1").Visible = False
        ActiveSheet.Shapes("G_2").Visible = True
        ActiveSheet.Shapes("G_3").Visible = False
        ActiveSheet.Shapes("G_4").Visible = False
End Select
End Sub

 

[andrewb90]

I didn't seem to get anything to happen with that. Is it supposed to run the code for case 1 if the cell contents are '1'?

 

[VoG]

It is based on your original - i.e. the number of characters. Entering 1 or 2 or A should trigger Case 1 and so on.

 

[andrewb90]

I am still encountering the same issue with case 2. When case 1 runs G_2 gets hidden, but when I try to run case 2, it does not reappear.

 

[bstory84]

I tested VOG's code it works just fine.
you are currently using the "LEN" length function . this counts the number of characters in the cell.
if you want to be able to type values "1" or "2" which are both 1 character in length. you need to modify your code to this.
really just remove the LEN function.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) <> "A12" Then Exit Sub
Select Case Target.Value
    Case 1
        ActiveSheet.Shapes("G_1").Visible = True
        ActiveSheet.Shapes("G_2").Visible = False
        ActiveSheet.Shapes("G_3").Visible = False
        ActiveSheet.Shapes("G_4").Visible = False
    Case 2
        ActiveSheet.Shapes("G_1").Visible = False
        ActiveSheet.Shapes("G_2").Visible = True
        ActiveSheet.Shapes("G_3").Visible = False
        ActiveSheet.Shapes("G_4").Visible = False
End Select
End Sub