View Single Post
  #2   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,

After changing the names of your 6 Freeforms as below, try the
following Worksheet_Change Sub

Freeform 1.Name = Freeform 1|1
Freeform 2.Name = Freeform 2|1
Freeform 3.Name = Freeform 3|1
Freeform 4.Name = Freeform 4|2
Freeform 5.Name = Freeform 5|2
Freeform 6.Name = Freeform 6|2

Change their names in the Name box on the left of the Formula Bar
(Don't forget to press Enter after each name change.).

I've used a different naming technique to avoid any confusion with your
other shapes, just in case these freeforms are on the same sheet as the
one I've already seen.
The second last character in each of the names is the pipe that you get
from Shift + the backslash key.

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
End Sub


Hope this helps.

Ken Johnson