Excel/Pre-loaded pictures macro solution?
QUESTION: Hi Damon,
I really like the ShowPicD function that you have helped so many people build.
ShowPicD works perfectly for all my needs, except that I need it to access images that are inside my workbook.
Can ShowPicD be modified to access a image inside the same sheet or would I need a macro solution?
ANSWER: Hi Jim,
I have asked myself the same question. I wanted to embed my pictures in a sheet and have a ShowPic-like function that is called on another sheet copy the pictures and paste them in the ShowPic sheet. This I found to be not possible. It requires a macro. The macro solution is not complicated, but not as clean and elegant from the user standpoint.
Now I am wondering whether pictures that are embedded in the ShowPic sheet but are invisible, can be made visible by a ShowPic-like function. This would require the ShowPic function to toggle the pictures' Visible property to make the desired picture appear (or disappear) as desired. I have not yet had time to try this. If the answer to this latter question interests you let me know and I'll find time to explore it.
---------- FOLLOW-UP ----------
QUESTION: Thanks for the help!
Yes, I would be interested in your macro solution.
I have a sheet that contains hourly weather data. Each row is one day with 24 columns for the hours. Each cell going across the row would contain a graphic illustrating the type of weather for that hour.
The weather types are:
Type 1 = sun
Type 2 = cloud
Type 3 = rain
Type 4 = snow
The data driving the graphics is already in my sheet, types 1-4. Having graphics would make it much easier to spot similar weather conditions when compare the say 10 days.
Here's code to do what you describe. Read the code comments for important assumptions.
' copies pictures of Sun, Cloud, Rain, and Snow from Type Pics sheet into table cells
' containing Type values 1-4. The table is assumed to reside in worksheet WX in B2:Y8.
' The pictures can reside anywhere in Type Pics sheet, but must be named "Sun", "Cloud",
' etc. It is assumed that the pictures and the WX table cells are sized appropriate such
' that the pictures fit in the cells. Each picture is placed with its top-left corner
' at the top-left corner of the cell it is copied to.
Dim WXsheet As Worksheet
Dim PicSheet As Worksheet
Dim PicName As String
Dim iRow As Long
Dim iCol As Integer
'Loop through all cells in WX table
Set WXsheet = Worksheets("WX")
Set PicSheet = Worksheets("Type Pics")
For iRow = 2 To 8
For iCol = 2 To 25
Select Case .Cells(iRow, iCol)
Case 1: PicName = "Sun"
Case 2: PicName = "Cloud"
Case 3: PicName = "Rain"
Case 4: PicName = "Snow"
Case Else: GoTo NoType
.Paste Destination:=.Cells(iRow, iCol)