Graphics: Same Picture on several sheets

I want to place a picture from a bitmap on, say, sheet 5, and then have it appear in sizable boxes on sheets 1, 2, 3 & 4. This way if I wish to change it, I can simply change it in one place (sheet 5) and it will flow through to all the other pages. What is the best way (Is there a good way) to handle this?

Hi brucemc. Check out the McGimpsey site at
http://www.mcgimpsey.com/excel/lookuppics.html . You might be able to use
this to display your picture.
--
Sincerely, Michael Colvin


"brucemc" wrote:

>
> I want to place a picture from a bitmap on, say, sheet 5, and then have
> it appear in sizable boxes on sheets 1, 2, 3 & 4. This way if I wish to
> change it, I can simply change it in one place (sheet 5) and it will
> flow through to all the other pages. What is the best way (Is there a
> good way) to handle this?
>
>
> --
> brucemc
> ------------------------------------------------------------------------
> brucemc's Profile: http://www.excelforum.com/member.php...o&userid=32871
> View this thread: http://www.excelforum.com/showthread...hreadid=550040
>
>

I really liked learning from that example!

Unfortunately... Though it certainly would allow the switching of pictures I wish to achieve, unless I am missing a concept you might be presuming I already understand, I would have to have the entire collection of pictures on each sheet which I want them to appear (i.e.: the same four pictures on sheet1, sheet2, sheet3 and sheet4). I fear that will unnecessarily significantly increase file size.

I think what I want to do should be done by having the collection all on one sheet then echoed or reflected (OK, copied) onto all the other sheets on each change of the controlling variable. Unfortunately a simple copy routine won't work, even if it would copy the picture, unless I could learn how to not only copy the picture to each of the other sheets, but also individually specify the dimensions, as it does vary somewhat by sheet, and delete the previous.

I had hoped there was a way to set up a frame or shape on each sheet, then address it with VBA to change which picture that was to appear in it, with the pictures all stored as named Pictures (named ranges?) on one sheet. I will have to tinker with this, but if someone knows how to do it offhand, saving me hours of cussing is appreciated; and that is in addition to the hours of my wife cussing at me.

Not possible, eh?

I think I'm up a creek. Ain't no paddle. Never was. Never will be.

brucemc wrote:
> I think I'm up a creek. Ain't no paddle. Never was. Never will be.

Hi Bruce,
So that you don't have to have a copy of every picture on every sheet,
you can have every picture on Sheet1 with code determining which one is
visible. The code can also copy the visible picture onto every other
sheet and delete the previous visible picture.

I've used a WorksheetChange event procedure and a standard procedure to
control the visibility of all the pictures. I renamed the pictures
MyPic001, MyPic002 and MyPic003. I have limited myself to 3 pictures,
but naming them this way gives enough room for 1000 pictures (MyPic000
to MyPic999).
I renamed the pictures this way...

1. Select a picture
2. Click in the Name box on the left side of the formula bar
3. Type required name eg MyPic003
4. Press Enter (I too often forget this step. Try not to forget it,
otherwise the picture's name will not change and it will be ignored by
the code)

After all the pictures have been renamed and positioned exactly where
you want them to appear on Sheet1 the ensuing code does the
following...

If you change the value in A1 on Sheet1 to say 3 then the picture named
MyPic003 will be visible on Sheet1 while all the other pictures with a
name of the form MyPicnnn will be invisible. The visible picture is
also copied and pasted into exactly the same position on all the other
sheets after the copied MyPicnnn from the previous Sheet1 A1 value has
been deleted (deleted by the standard procedure)

If you want to try it out on a copy of your workbook...
1. Copy the following code
2. Right click the Sheet1 tab then select "View Code" from the Popup
menu
3. Paste the code into the Sheet1 code module (Both can go there)


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
'$A$1 is the address of the cell whose value determines
'which picture is visible. Change to suit your needs.
'Error will occur if $s omitted
Application.ScreenUpdating = False
Dim NewPicHeight As Single
Dim NewPicWidth As Single
Dim NewPicLeft As Single
Dim NewPicTop As Single
Dim NewPicName As String
DeleteOldPic
Dim ncPics As New Collection
Dim Shp As Shape
Dim Sht As Worksheet
For Each Shp In Me.Shapes
If Left(Shp.Name, 5) = "MyPic" Then
ncPics.Add Item:=Shp
End If
Next Shp
For Each Shp In ncPics
If Right(Shp.Name, Len(Target.Value)) _
= CStr(Target.Value) Then
Shp.Visible = True
NewPicHeight = Shp.Height
NewPicWidth = Shp.Width
NewPicLeft = Shp.Left
NewPicTop = Shp.Top
Shp.Copy
NewPicName = Shp.Name
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> Me.Name Then
With Sht
.Paste
.Activate
.Range("A1").Select
End With
Me.Activate
End If
Next Sht
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> Me.Name Then
With Sht.Shapes(NewPicName)
.Height = NewPicHeight
.Width = NewPicWidth
.Top = NewPicTop
.Left = NewPicLeft
.Name = "OldPic"
End With
End If
Next Sht
Else: Shp.Visible = False
End If
Next Shp
End If
End Sub



Public Sub DeleteOldPic()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> Worksheets(1).Name Then
On Error Resume Next
Sht.Shapes("OldPic").Delete
End If
Next Sht
End Sub

Hope this helps you with your canoe.

Ken Johnson