Hi All,
Here is the scenario: (Excel 2003)
1) I have created 10 different shapes (Free Form 1 to 10)
2) If the value in H10 is equal to "Box" then I want the Free Form 1 appears
at P10:T15 location
3) If the value in H10 is equal to "Table" then I want the Free Form 2
appears at P10:T15 location
4) If the value in H10 is equal to "Ball" then I want the Free Form 3
appears at P10:T15 location and so on.
I want to have only one Free Form at a time.
How can I do this?
Thanks very much in advance for any comments.
DORI

Start with JE McGimpsey's program:

http://www.mcgimpsey.com/excel/lookuppics.html

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"DORI" wrote:

Hi All,
Here is the scenario: (Excel 2003)
1) I have created 10 different shapes (Free Form 1 to 10)
2) If the value in H10 is equal to "Box" then I want the Free Form 1 appears
at P10:T15 location
3) If the value in H10 is equal to "Table" then I want the Free Form 2
appears at P10:T15 location
4) If the value in H10 is equal to "Ball" then I want the Free Form 3
appears at P10:T15 location and so on.
I want to have only one Free Form at a time.
How can I do this?
Thanks very much in advance for any comments.
DORI

Hi Ron,
Thanks for the link. I tried the sample but no success:
1) I have the list of my variable texts in H10
2) I have inserted 10 sample pictures to my worksheet to try your suggestion,
2) when I choose a text from drop-down menu in H10, the corresponding text
appears in P10 (for example: Picture 1) then, all 10 images disappear from my
worksheet.
3) I have also entered the VBA code, VLook formula in P10 and Offset formula
in H10 using Data Validitation. what did I do wrong?

2) I have my own designed shapes (objects) using Excel drawing tools. How
can I use them instead of pictures?
Thanks for the help,
DORI

"Ron Coderre" wrote:

Start with JE McGimpsey's program:

http://www.mcgimpsey.com/excel/lookuppics.html

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"DORI" wrote:

Hi All,
Here is the scenario: (Excel 2003)
1) I have created 10 different shapes (Free Form 1 to 10)
2) If the value in H10 is equal to "Box" then I want the Free Form 1 appears
at P10:T15 location
3) If the value in H10 is equal to "Table" then I want the Free Form 2
appears at P10:T15 location
4) If the value in H10 is equal to "Ball" then I want the Free Form 3
appears at P10:T15 location and so on.
I want to have only one Free Form at a time.
How can I do this?
Thanks very much in advance for any comments.
DORI

Hi DORI,

If these are the only shapes on your sheet it could be done like
this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Shp As Shape
For Each Shp In Me.Shapes
Shp.Visible = Range("H10").Value = Shp.Name
Next Shp
End Sub


Before you use the code, position them all where you want them to
appear.
When you type "Box" in H10 only the Box will be visible etc

Before the code will work the way you want you will have to change each
freeform's name. To do that select a freeform then type its new name in
the Name Box on the left side of the Formula Bar, then press Enter
(Don't forget this important press of Enter).
Repeat with each freeform until you have named them all according to
the names you will be typing in H10.
You could also use a data validation dropdown list on H10 to select the
name of the freeform to show.

If you have other shapes on the sheet that you don't want the code to
make invisible, then you will have to extend the code so that those
shapes are skipped. For example say there is a shape named Rectangle 1
that has to remain visible at all times then the code should be changed
to...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Shp As Shape
For Each Shp In Me.Shapes
If Shp.Name < "Rectangle 1" Then
Shp.Visible = Range("H10").Value = Shp.Name
End If
Next Shp
End Sub

Ken Johnson

Hi Ken,
Thank you very vrey much for the VBA code. It worked great. It is amazing
how a simple code can have such an impressive result.
Thank you again,
DORI

"Ken Johnson" wrote:

Hi DORI,

If these are the only shapes on your sheet it could be done like
this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Shp As Shape
For Each Shp In Me.Shapes
Shp.Visible = Range("H10").Value = Shp.Name
Next Shp
End Sub


Before you use the code, position them all where you want them to
appear.
When you type "Box" in H10 only the Box will be visible etc

Before the code will work the way you want you will have to change each
freeform's name. To do that select a freeform then type its new name in
the Name Box on the left side of the Formula Bar, then press Enter
(Don't forget this important press of Enter).
Repeat with each freeform until you have named them all according to
the names you will be typing in H10.
You could also use a data validation dropdown list on H10 to select the
name of the freeform to show.

If you have other shapes on the sheet that you don't want the code to
make invisible, then you will have to extend the code so that those
shapes are skipped. For example say there is a shape named Rectangle 1
that has to remain visible at all times then the code should be changed
to...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Shp As Shape
For Each Shp In Me.Shapes
If Shp.Name < "Rectangle 1" Then
Shp.Visible = Range("H10").Value = Shp.Name
End If
Next Shp
End Sub

Ken Johnson

Hi Ken,
One more question.
Is it possible to create another conditional list to have a different set of
freeforms appear in another location without disappearing the freeforms in
the first location?
Thanks again,
DORI
"Ken Johnson" wrote:

Hi DORI,

If these are the only shapes on your sheet it could be done like
this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Shp As Shape
For Each Shp In Me.Shapes
Shp.Visible = Range("H10").Value = Shp.Name
Next Shp
End Sub


Before you use the code, position them all where you want them to
appear.
When you type "Box" in H10 only the Box will be visible etc

Before the code will work the way you want you will have to change each
freeform's name. To do that select a freeform then type its new name in
the Name Box on the left side of the Formula Bar, then press Enter
(Don't forget this important press of Enter).
Repeat with each freeform until you have named them all according to
the names you will be typing in H10.
You could also use a data validation dropdown list on H10 to select the
name of the freeform to show.

If you have other shapes on the sheet that you don't want the code to
make invisible, then you will have to extend the code so that those
shapes are skipped. For example say there is a shape named Rectangle 1
that has to remain visible at all times then the code should be changed
to...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Shp As Shape
For Each Shp In Me.Shapes
If Shp.Name < "Rectangle 1" Then
Shp.Visible = Range("H10").Value = Shp.Name
End If
Next Shp
End Sub

Ken Johnson

Hi DORI,

the simplest way is to have your other set of freeforms on a different
sheet. This way the code is the same, just on different sheets.
You will be able to view both sheets together by going WindowNew
Window, then going WindowArrange and choose vertical.

If this is not the way you want to go, then the code will be a bit
harder, so let me know before I try to do it for freeforms on the one
sheet.

Ken Johnson

Hi Ken,
Thanks for all your help. I tried the WindowNew
Window, but it won't work for the my worksheet. I also have some check boxes and text boxes that they have been disappeared from the worksheet after I ran the VBA. How can I see the check boxes and text boxes again? Do I have to create them again?
Would you mind to help me with the VBA for freeforms on one sheet?
Thanks so much,
DORI

"Ken Johnson" wrote:

Hi DORI,

the simplest way is to have your other set of freeforms on a different
sheet. This way the code is the same, just on different sheets.
You will be able to view both sheets together by going WindowNew
Window, then going WindowArrange and choose vertical.

If this is not the way you want to go, then the code will be a bit
harder, so let me know before I try to do it for freeforms on the one
sheet.

Ken Johnson

Hi DORI,
to get those lost check boxes and text boxes back to visible run this
macro...

Public Sub Visible()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
Shp.Visible = True
Next

Also, I anticipated you would not like my first solution so try this..

1. Rename all of your Set One freeforms so that the last two characters
of their name is 01.(that's number zero followed by number one, so for
eg Ball becomes Ball01). This is only for the name that the computer
code uses, the name typed into H10 or the other cell is still just
Ball. (by the way, as it stands it is case sensitive. I can code out
the case sensitivity if you want)
This re-naming, of course, has to be done as before - select freeform,
type name in Name Box, press Enter (again, don't forget to press Enter.
I often forget!)

2. Name all of your Set Two freeforms so that the last two characters
of their name is 02.

3. Copy and paste this code into the worksheet's code module (after
deleting the old code)...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Shp As Shape
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 "$H$10"
For Each Shp In SetOne
Shp.Visible = Target.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

Now, everything's on the one worksheet AND all of your other Shape
objects such as text boxes etc should not be affected because the last
two characters of their names should neither be "01" nor "02"

I did not know the address of the other cell into which the visible
freeforms text name is to be typed. It can't also be H10, the way the
code works means it has to be a different cell. Otherwise Excel does
not know which set of freeforms to work on.

As the code stands, that other cell is A1. To change it to the desired
cell just change this code line...

Case "$A$1"

The $'s are important. So, if the cell for Set Two freeforms is H12
then that line should read...

Case "$H$12"

I think that just about does it.

Good Luck.

Ken Johnson

Dear Ken,
Thank you so much for all your help. The VBA worked great.
One last question: I have about 50 freeforms that I have renamed them as"
Group 1101", "Group 1202" etc. (they all end to a double-digit of 01 or 02 as
you advised me). I inserted the following formula in cell H10:

="Group "&VLOOKUP(A2,Table1,2,FALSE)&VLOOKUP(A4,Table2,2,F ALSE)

Depending on the input in cells A2 and A4, the cell H10 will show for
example "Group 11" or "Group 12" etc. which I thought the VBA would recognize
it and would display the freeform "Group 1101 or "Group 1202". But it did not.
When I type the text "Group 11" or "Group 12" in cell H10, or if I put
"Group 11,Group 12" in the Data Validation to create a drop-down list in cell
H10, everything works perfect, but it does not work when I insert the above
formula in cell H10. What do I do wrong?
Thanks again for being so helpful.
DORI

"Ken Johnson" wrote:

Hi DORI,
to get those lost check boxes and text boxes back to visible run this
macro...

Public Sub Visible()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
Shp.Visible = True
Next

Also, I anticipated you would not like my first solution so try this..

1. Rename all of your Set One freeforms so that the last two characters
of their name is 01.(that's number zero followed by number one, so for
eg Ball becomes Ball01). This is only for the name that the computer
code uses, the name typed into H10 or the other cell is still just
Ball. (by the way, as it stands it is case sensitive. I can code out
the case sensitivity if you want)
This re-naming, of course, has to be done as before - select freeform,
type name in Name Box, press Enter (again, don't forget to press Enter.
I often forget!)

2. Name all of your Set Two freeforms so that the last two characters
of their name is 02.

3. Copy and paste this code into the worksheet's code module (after
deleting the old code)...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Shp As Shape
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 "$H$10"
For Each Shp In SetOne
Shp.Visible = Target.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

Now, everything's on the one worksheet AND all of your other Shape
objects such as text boxes etc should not be affected because the last
two characters of their names should neither be "01" nor "02"

I did not know the address of the other cell into which the visible
freeforms text name is to be typed. It can't also be H10, the way the
code works means it has to be a different cell. Otherwise Excel does
not know which set of freeforms to work on.

As the code stands, that other cell is A1. To change it to the desired
cell just change this code line...

Case "$A$1"

The $'s are important. So, if the cell for Set Two freeforms is H12
then that line should read...

Case "$H$12"

I think that just about does it.

Good Luck.

Ken Johnson

Hi DORI,

Sorry, but there are too many unknowns for me to be able to answer your
question.

If you email me a copy of the workbook I would stand a better chance of
solving the problem.
I'm curious to see exactly what the workbook does.

I could be wrong, but from my reading of your last reply it appears to
me as though you are trying to control the visibility of both sets of
freeforms (or groups or whatevers) from the same cell, namely H10.
The way the code is set up, the value in H10 controls the visibility of
the first set of shapes, the ones with name ending in 01, while the
second set of shapes, with name ending in 02, have to have their
visibility controlled by a value in a different cell (I made it A1 and
told you how to change it to any other cell; but not H10, which is
reserved for controlling set one's shape's visibility).
Please correct me if I have got it wrong.

If your are willing and able to email me a copy could you include a
brief description of what the user is meant to do and how the sheet is
supposed to respond.

Also, if I have I copy then I can have a close look at Table1, Table2
and the Data Validation List.

Ken Johnson

Hi Ken,
I have emailed you a sample of my work with detail explanation. Looking
forward to your input.
Thanks again,
DORI

"Ken Johnson" wrote:

Hi DORI,

Sorry, but there are too many unknowns for me to be able to answer your
question.

If you email me a copy of the workbook I would stand a better chance of
solving the problem.
I'm curious to see exactly what the workbook does.

I could be wrong, but from my reading of your last reply it appears to
me as though you are trying to control the visibility of both sets of
freeforms (or groups or whatevers) from the same cell, namely H10.
The way the code is set up, the value in H10 controls the visibility of
the first set of shapes, the ones with name ending in 01, while the
second set of shapes, with name ending in 02, have to have their
visibility controlled by a value in a different cell (I made it A1 and
told you how to change it to any other cell; but not H10, which is
reserved for controlling set one's shape's visibility).
Please correct me if I have got it wrong.

If your are willing and able to email me a copy could you include a
brief description of what the user is meant to do and how the sheet is
supposed to respond.

Also, if I have I copy then I can have a close look at Table1, Table2
and the Data Validation List.

Ken Johnson