View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.programming
Ken Johnson Ken Johnson is offline
external usenet poster
 
Posts: 1,073
Default How to hide freeforms conditionally?

Hi DORI,

I know I shouldn't speak too soon, nevertheless, the solution wasn't
too difficult.

I just appended the first block of code with the new code, then the
only change I had to make was to delete a repetition of "Dim Shp as
Shape".

So, delete ALL old code and replace with...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cSetOne As New Collection
Dim cSetTwo As New Collection
Dim cSetThree As New Collection
Dim Shp As Shape
For Each Shp In Me.Shapes
Select Case Right(Shp.Name, 2)
Case "|1"
cSetOne.Add Item:=Shp
Case "|2"
cSetTwo.Add Item:=Shp
End Select
Next Shp
If Me.Range("A68").Value = "" _
And Me.Range("N9").Value = "" Then
For Each Shp In cSetOne
Shp.Visible = False
Next Shp
For Each Shp In cSetTwo
Shp.Visible = False
Next Shp
End If
If Me.Range("A68").Value = True Then
Select Case Range("N9").Value
Case 1
For Each Shp In cSetOne
Shp.Visible = True
Next Shp
For Each Shp In cSetTwo
Shp.Visible = False
Next Shp
Case 2
For Each Shp In cSetOne
Shp.Visible = True
Next Shp
For Each Shp In cSetTwo
Shp.Visible = True
Next Shp
End Select
End If
If VarType(Range("H10").Value) < 8 Then
Exit Sub
End If
Dim SetOne As New Collection
Dim SetTwo As New Collection
For Each Shp In Me.Shapes
Select Case Right(Shp.Name, 2)
Case "01"
SetOne.Add Item:=Shp
Case "02"
SetTwo.Add Item:=Shp
End Select
Next Shp
Select Case Target.Address
Case "$A$2"
For Each Shp In SetOne
Shp.Visible = Range("H10").Value & "01" = Shp.Name
Next Shp
Case "$A$4"
For Each Shp In SetOne
Shp.Visible = Range("H10").Value & "01" = Shp.Name
Next Shp
Case "$A$1"
For Each Shp In SetTwo
Shp.Visible = Target.Value & "02" = Shp.Name
Next Shp
End Select
End Sub

Let me know how you go.

Ken Johnson