How to hide freeforms conditionally?
 

Dear all,
I would like to make freeform1, freeform2 and freeform3 visible if the input
in cells A68=True and N9=1
If the input in cells A68=True and N9=2 then I like to make freeforms
1,2,3,4,5, and 6 visible.
If there is no input in cells A68 and N9 then I like to hide all 6 freeforms.
Can any one help me how I can do this with vba codes?
Thanks,
DORI

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

How to hide freeforms conditionally?
 

Hi DORI,

Just a trivial change to the code.
At one stage I was toying with the idea of three separate shapes
collections and I've accidentally left the third collection's Dim
statement in the code.
You can safely remove the line that is not needed...

Dim cSetThree As New Collection

Ken Johnson

How to hide freeforms conditionally?
 

G'day Ken,

I'm new to this group. I found your code works great and has helped me
to better understand and work with shapes. Thanks.

Techuser

How to hide freeforms conditionally?
 

You're welcome Techuser.
Thanks for the feedback.
Ken Johnson

How to hide freeforms conditionally?
 

Hi Ken,
Thank you so much for the vba code. Your trick was very smart, your
explanation was very clear and the code worked perfect.
I know it may sound dum, but I have another worksheet_Change sub that you
gave me earlier which I have it on the same sheet. How should I change
"worksheet_Change" in order to prevent the error message?
Compile error: Ambiguous name detected: worksheet_Change

Thanks again,
DORI

"Ken Johnson" wrote:

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

How to hide freeforms conditionally?
 

Hi DORI,

Sounds like you might be trying to put two separate worksheet change
subs on the one sheet.
You're only allowed the one, so I'll have to merge the two into one.
I'll get back to on that.

Ken Johnson

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

How to hide freeforms conditionally?
 

Hi Ken,
Thanks again for all your help. The vba code worked great. I learned lots
from you, you are a great teacher!
DORI

"Ken Johnson" wrote:

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

How to hide freeforms conditionally?
 

Hi DORI,

Thanks for the feedback.

I accidentally reproduced that unnecessary "Dim cSetThree As New
Collection" in the last reply.
It's harmless but could cause confusion in the future.

Ken Johnson

How to hide freeforms conditionally?
 

Hi Ken,
I noticed it and have removed it from the program.
Thanks,
DORI

"Ken Johnson" wrote:

Hi DORI,

Thanks for the feedback.

I accidentally reproduced that unnecessary "Dim cSetThree As New
Collection" in the last reply.
It's harmless but could cause confusion in the future.

Ken Johnson

How to hide freeforms conditionally?
 

Hi DORI,

I wish all my students were as obedient as you;-)

Ken Johnson