You are here:

Excel/Pre-loaded pictures macro solution?

Advertisement


Question
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?

Many Thanks!

Jim

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.

Damon

---------- 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.

Thanks again!

Jim

Answer
Hi Jim,

Here's code to do what you describe.  Read the code comments for important assumptions.

__________________________________

Sub WXpics2Table()

'  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")
  
  With WXsheet
  
     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
         End Select
         
         PicSheet.Pictures(PicName).Copy
         
         .Paste Destination:=.Cells(iRow, iCol)
         
NoType:
        Next iCol
        
     Next iRow
  
  End With
  
End Sub
______________________________________________

Keep Excelling.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.