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