How to hide freeforms conditionally?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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
 
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
 
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
 
Hi Ken,
Thanks again for all your help. The vba code worked great. I learned lots
from you, you are a great teacher!
DORI
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extracting values from Freeform shapes 4
when certain rows are hidden, can I also hide a combobox? 6
selecting more than one cell 3
put textbox data in sheet (sheet name selected in a listbox) 8
Freeform Object Failing 1
Colour cell (or text) depending upon IF Function result 4
Insert code 2
worksheet_calculate question 4

Back
Top