- Joined
- Mar 13, 2011
- Messages
- 2,531
- Reaction score
- 6
- Points
- 38
- Location
- Nanaimo, BC, Canada
- Website
- www.excelguru.ca
- Excel Version(s)
- Excel Office 365 Insider
We're building a dashboard for our golf course at work, and one of the things that we have implemented is a weather forecast. We pull the weather forecast from an XML data feed, and then I want to put in a picture forecast, rather than the text.
So no problem, we knock up a VLOOKUP for pictures. (This is based off a workbook from McGimpsey.)
Everything was working fine for me until I decided to put all my source pictures on a different sheet then copy them in to the first sheet when needed. I've got it working, but I'm curious if there is a better method for a part of it. Here's the main routine (called from a Worksheet_Change event):
The part that rankles me is the "select" in the middle. I can duplicate my image on the lookup sheet, then cut it and paste it to the report sheet. At that point it loses connection to the object, so I have to restablish that.
Does anyone know a way to transfer a picture from one worksheet to another without having to select it in between?
(Also, why is it that "picture" seems invisible to Intellisense in Excel 2010?)
Sample file is attached.
So no problem, we knock up a VLOOKUP for pictures. (This is based off a workbook from McGimpsey.)
Everything was working fine for me until I decided to put all my source pictures on a different sheet then copy them in to the first sheet when needed. I've got it working, but I'm curious if there is a better method for a part of it. Here's the main routine (called from a Worksheet_Change event):
Code:
Public Sub UpdateWeather(rngTarget As Range)
Dim oPic_src As Picture
Dim oPic_update As Picture
Dim oPic_new As Picture
Dim wsReport As Worksheet
Dim wsPicLookup As Worksheet
Dim lPic As Long
Application.ScreenUpdating = False
'Set worksheets
Set wsReport = Worksheets("Report")
Set wsPicLookup = Worksheets("PictureLookup")
With wsReport
'Work out which picture should be updated. (Need column of range)
lPic = Intersect(.Range("rng_weather"), rngTarget).Column - .Range("rng_weather").Cells(1, 1).Column + 1
'Set reference to pictures to replace/replace with
Set oPic_update = .Pictures("pic_day" & lPic)
Set oPic_src = wsPicLookup.Pictures(Application.WorksheetFunction.VLookup(rngTarget.Value, wsPicLookup.Range("tbl_weatherpics"), 2, False))
'Copy picture to be used and place it where old picture resides
Set oPic_new = oPic_src.Duplicate
oPic_new.Cut
.Paste
Set oPic_new = Selection
With oPic_new
.Top = oPic_update.Top
.Left = oPic_update.Left
.Name = oPic_update.Name
End With
'Delete old picture
oPic_update.Delete
End With
rngTarget.Activate
End Sub
The part that rankles me is the "select" in the middle. I can duplicate my image on the lookup sheet, then cut it and paste it to the report sheet. At that point it loses connection to the object, so I have to restablish that.
Does anyone know a way to transfer a picture from one worksheet to another without having to select it in between?
(Also, why is it that "picture" seems invisible to Intellisense in Excel 2010?)
Sample file is attached.