none
Macro for using Pictures in an Excel "Package"? RRS feed

  • Question

  • Is it possible to write a macro to use pictures contained in an Excel "package" in a userform in the same Excel file as the userform?

    I would like to have the pictures located within the Excel file, so the file can be used by multiple individuals without the need of an extra folder for them.  The pictures would be placed on the userform with a graph/chart for trend analysis. 

    Anyone up to the challenge of this type of experiment?

    Seems like I should be able to with a macro open the "package', select the file, save it to someplace common to all Windows platforms, and then select it for use it for the chart userform when it is opened and finally delete it when the chart userform is closed.

    Is this possible?  Is there a better way?

    Looking forward to the answers for this...

    Thursday, December 29, 2011 6:38 AM

Answers


  • It's easier than you think.
    Load an Image control and an ImageList control onto the userform.
    You use the ImageList control to store the pictures.
    You use the Image control to display each picture.

    In the properties window of the ImageList control, select Custom.
    The Properties dialog pops up.  On the Images tab load your pictures.

    Display a picture with...
    Dim i As Long
    i = 1
    Image1.Picture = ImageList1.ListImages(i).Picture

    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (free and commercial excel programs)

    • Marked as answer by AtlBo Monday, January 2, 2012 9:19 AM
    Thursday, December 29, 2011 2:04 PM
  • No need to use the Image list control. In a new project add two userforms. In Userform2 add some standard image controls (only image controls, no others) and add a picture to each. Add the following code to Userform1, run the form and click on the form

    Private mIdx As Long
    
    Private Sub Image1_Click()
         NextImage
    End Sub
    
    Private Sub UserForm_Click()
         NextImage
    End Sub
    
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
         Unload UserForm2
    End Sub
    
    Private Sub NextImage()
         If mIdx = UserForm2.Controls.Count Then
                 mIdx = 0
         End If
         mIdx = mIdx + 1
         Me.Image1.AutoSize = True ' or size to suit
         Me.Image1.Picture = UserForm2.Controls(mIdx - 1).Picture
            ' Userform2 will load when first called. Either unload
         ' it here each time or when this form closes, see QueryClose
    End Sub
    

    Peter Thornton

    • Marked as answer by AtlBo Monday, January 2, 2012 9:19 AM
    Thursday, December 29, 2011 3:51 PM
    Moderator

  • Both controls load for me in xl2003 and xl2010.
    There is a way to register controls, do a search, but
    give Peter T's suggestion a try, he has helped me before.

    Also, I am curious, as to how much your file size increases when the forms and pictures are finally added.
    '---
    Jim Cone

    • Marked as answer by AtlBo Monday, January 2, 2012 9:19 AM
    Thursday, December 29, 2011 4:32 PM
  • I forgot to say also add an Image control named Image1 to Userform1.

    Each you click on Userform1, the Image control should show the next picture in Userform2. Obviously the example is only a demo, but you could do say

    Dim sp As StdPicture
    Set sp = Userform2.Controls("imgMyPic1").Picture
    Me.Image1.Picture = sp

    You could also store pictures in ActiveX Image controls on a sheet an do say
    ' code in Userform1
    Set sp = ThisWorkbook.Worksheets("Sheet1").OLEObjects("Image1").Object.Picture
    Me.Image1.Picture = sp

    Peter Thornton

    • Marked as answer by AtlBo Monday, January 2, 2012 9:19 AM
    Thursday, December 29, 2011 10:13 PM
    Moderator
  • Not quite sure what you're doing but maybe you don't need to store chart pictures at all, have a look at Stephen Bullen's PastePicture demo. It copies a picture to the clipboard using APIs (in the demo a chart) and pastes to an image control on the userform.

    http://www.oaltd.co.uk/Excel/Default.htm

    Maybe store different sets of data, update a chart with the given set, and copy the chart to your Image control. The actual chart could be not visible or hidden from view. The only thing you'd need to store is the underlying data.

    Peter Thornton

    • Marked as answer by AtlBo Monday, January 2, 2012 9:19 AM
    Friday, December 30, 2011 12:12 AM
    Moderator
  • Afraid I don't follow your objective with the charts, well, at least not without putting my concentration cap on! However from what I can just about gather I expect you could set your chart up to use one or more Dynamic Ranges, controlled from a Combo; some examples

    http://peltiertech.com/Excel/Charts/Dynamics.html

    http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/

    If a dynamic range is not appropriate code (say triggered by a change event) could update the chart series with the new data.  With either approach an image of the chart can be copied to the form with "PastePicture".

    The metafile chart is very classy to me. Just wondering how difficult it would be to add a picture to the metafile chart or if that's possible.

    Yes a metafile image of a chart has the advantage that the image can be rescaled with minimal loss of quality.

    Any image can be added or rather inserted to the ChartArea, though I don't follow why you'd want to show an image of a chart on a chart rather than the actual chart.

    As I'm sure you know an image of a chart can be saved to file using the chart.Export method

    Peter Thornton

    • Marked as answer by AtlBo Monday, January 2, 2012 9:18 AM
    Friday, December 30, 2011 11:38 AM
    Moderator

All replies


  • It's easier than you think.
    Load an Image control and an ImageList control onto the userform.
    You use the ImageList control to store the pictures.
    You use the Image control to display each picture.

    In the properties window of the ImageList control, select Custom.
    The Properties dialog pops up.  On the Images tab load your pictures.

    Display a picture with...
    Dim i As Long
    i = 1
    Image1.Picture = ImageList1.ListImages(i).Picture

    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (free and commercial excel programs)

    • Marked as answer by AtlBo Monday, January 2, 2012 9:19 AM
    Thursday, December 29, 2011 2:04 PM
  • Thankyou for the suggestion Jim.  I tried the control as you detailed.  Everything goes according to plan until I click on the "Custom" property to load the pictures.  At that point I get:

    "Class not registered."

    "Looking for Object with CLSID: {registry key}"

    This is the version of the ImageList control I located and tried on this userform:

    Image List Active X Control - Version 6 (SP6)

    Copywrite 1995-2000 Microsoft Corporation

    There is another ImageList control, but it is associated with a reader software I have and won't load onto the userform.  It's version 5 (SP2) of the same control, I think.  It says it its Microsoft.  This is the message:

    "Control could not be created because it is not properly licensed."

    Any ideas why the first control doesn't show up in the registry?

     

    Thursday, December 29, 2011 3:24 PM
  • No need to use the Image list control. In a new project add two userforms. In Userform2 add some standard image controls (only image controls, no others) and add a picture to each. Add the following code to Userform1, run the form and click on the form

    Private mIdx As Long
    
    Private Sub Image1_Click()
         NextImage
    End Sub
    
    Private Sub UserForm_Click()
         NextImage
    End Sub
    
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
         Unload UserForm2
    End Sub
    
    Private Sub NextImage()
         If mIdx = UserForm2.Controls.Count Then
                 mIdx = 0
         End If
         mIdx = mIdx + 1
         Me.Image1.AutoSize = True ' or size to suit
         Me.Image1.Picture = UserForm2.Controls(mIdx - 1).Picture
            ' Userform2 will load when first called. Either unload
         ' it here each time or when this form closes, see QueryClose
    End Sub
    

    Peter Thornton

    • Marked as answer by AtlBo Monday, January 2, 2012 9:19 AM
    Thursday, December 29, 2011 3:51 PM
    Moderator

  • Both controls load for me in xl2003 and xl2010.
    There is a way to register controls, do a search, but
    give Peter T's suggestion a try, he has helped me before.

    Also, I am curious, as to how much your file size increases when the forms and pictures are finally added.
    '---
    Jim Cone

    • Marked as answer by AtlBo Monday, January 2, 2012 9:19 AM
    Thursday, December 29, 2011 4:32 PM
  • Thanks Peter.  So I am actually storing the pictures in a userform.  I would have never thought I could call a picture from a userform picture control to be used in another userform picture control, but I will give that a try.

    Jim, I ran across that bit about activating a control somewhere awhile back.  I will see if I can find it again.  I would really like to have access to that control for use.

    Thanks for the input...

    Thursday, December 29, 2011 8:38 PM
  • Peter the code hung here the first time through...any idea what I am doing wrong?

         Me.Image1.AutoSize = True

    Here is the error message:

    "Compile error:

    Method or data member not found"

    Tried converting the images to Bitmap but that didn't work either...

    Thursday, December 29, 2011 9:19 PM
  • I forgot to say also add an Image control named Image1 to Userform1.

    Each you click on Userform1, the Image control should show the next picture in Userform2. Obviously the example is only a demo, but you could do say

    Dim sp As StdPicture
    Set sp = Userform2.Controls("imgMyPic1").Picture
    Me.Image1.Picture = sp

    You could also store pictures in ActiveX Image controls on a sheet an do say
    ' code in Userform1
    Set sp = ThisWorkbook.Worksheets("Sheet1").OLEObjects("Image1").Object.Picture
    Me.Image1.Picture = sp

    Peter Thornton

    • Marked as answer by AtlBo Monday, January 2, 2012 9:19 AM
    Thursday, December 29, 2011 10:13 PM
    Moderator
  • Works like a charm Peter.

    I am just starting with a userform that is complex for me involving creating a chart on the same userform as the picture.  The picture will correspond to the data in the chart.  The whole thing will be initialized by a selection in a combobox on a worksheet which will open the userform, create the correct chart, and place the correct picture.  I will use this as my starting point...

    I obviously have some work to do!

    Jim I found instruction at Microsoft's Excel help site for registering an Active X control.  Here are the instructions:

    1. If the Developer tab is not available, display it.

    ShowDisplay the Developer tab

    1. On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click More Controls Button image.

    Controls group

    1. Click the worksheet location where you want the upper-left corner of the ActiveX control to appear.
    2. To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
    3. At the bottom of the More Controls dialog box, click Register Custom.
    4. In the Register Custom Control dialog box, locate the folder that contains the control file (.ocx file name extension) or dynamic link library file (.dll file name extension) for the control that you want to register.
    5. Select the file for your control, and then click Open.

    Using information I found in a thread at VB Wire Forums, I tried the process with a file named MSCOMCTL.ocx.  Here is the question in the thread, which also happens to contain the answer in an EDIT:

    I add Microsoft Windows Common Controls 6 SP6 - MSCOMCTL.OCX to my project.

    I add the ImageList control to my form.

    I right click on the ImageList control and go to properties.

    I see:
    Class not registered.
    Looking for object with CLSID:{C27CCE35-8596011D1-B16A-00C0F0283628}

    Any ideas? Any help is greatly appreciated!!

    Edit: Got it fixed. I just used Regedit32 to register the MSCOMCTL.OCX.

    I followed the instructions from Microsoft to "Register Control" on the add a control menu, but the process hasn't worked for me.  Not sure what I am doing wrong.  Registering this file seems to have worked for the individual in the post above, IDK...

    I would like to have this control registered moving forward.  It seems to be a very useful control...

    If anything comes to your mind right away, Jim, I would love to hear it...

    Thanks for the help to both of you...


    • Edited by AtlBo Thursday, December 29, 2011 11:24 PM
    Thursday, December 29, 2011 11:23 PM
  • Not quite sure what you're doing but maybe you don't need to store chart pictures at all, have a look at Stephen Bullen's PastePicture demo. It copies a picture to the clipboard using APIs (in the demo a chart) and pastes to an image control on the userform.

    http://www.oaltd.co.uk/Excel/Default.htm

    Maybe store different sets of data, update a chart with the given set, and copy the chart to your Image control. The actual chart could be not visible or hidden from view. The only thing you'd need to store is the underlying data.

    Peter Thornton

    • Marked as answer by AtlBo Monday, January 2, 2012 9:19 AM
    Friday, December 30, 2011 12:12 AM
    Moderator
  • Jim I managed to get pictures loaded into an ImageList control located on a worksheet.  I used a right click on the control and located on the menu was an option called "ImageListCtrl Properties".  Clicking this selection opened a pretty neat menu for adding pictures.  This method could be very useful to me,  It's similar to what I was asking with the package idea, but in a VB control designed for the purpose...much better.  I can use this with your idea to reference the control for the pics for the userform from a worksheet...

    Tried the same process in the VB editor, but the menu item is not available, and attempting to manually add the pics  through the "Custom" property brings the error message.

    Adding 50 pics took the size of the Excel file to 1.47 MB from a starting point of 7 kb.  The total size of the folder containing the pics is 1.84 MB.  The default size was 120 by 120 for the pics added to the control.  This corresponds exactly with the largest of the pics I added, so I guess the extra 370 KB of size was added from the smaller pics being upsized.

    Adding the size to the file seems like a small price to pay for me, considering this workbook is for multiple users...

    Thanks for the tips and advice

    Friday, December 30, 2011 12:34 AM
  • > considering this workbook is for multiple users...

    Don't forget the control will also need to be available and registered on your users' systems

    Peter Thornton

    Friday, December 30, 2011 12:46 AM
    Moderator
  • Great page Peter.  I love that example.

    Not sure where I am going to start.  Your idea about the data has me thinking.  Here is my project outline.  I haven't started it yet.  Just looking into my options for the picture, right now:

    Combobox charts for all Es in "DataSheet" and "Last Year's Data":

    Complex problem using the following parameters for the charts:
    Chart Y values are column Y and column Z of worksheets "DataSheet" and "Last Year's Data"
    Chart X values are column B of worksheets "DataSheet" and "Last Year's Data"

    -values in column E of "DataSheet" and "Last Year's Data" may be present more than once.
    -ranges for E for adding entries to the combobox are 18:962 for "DataSheet" and 18:962 for "Last Year's Data".  For each E represented once or more over these two ranges combined, the E value is to be placed in the combo box and all Es arranged alphabetically
    -ranges for collecting the chart Y axis values from the Y column for each E are:  18:962 for "DataSheet" and 18:(last row-225) rows for "Last Year's Data"
    -ranges for collecting the chart Y axis values from the Z column for each E are:  18:962 for "DataSheet" and 18:(last row-225) rows for "Last Year's Data"
    -the ranges in both "DataSheet" and "Last Year's Data" are arranged oldest to newest data from top to bottom
    -data for the charts should be arranged oldest to newest data, "Last Year's Data", to oldest to newest data, "DataSheet"
    -chart type to be used is 3-D area chart
    -selecting an E in the drop down menu of the combobox should open a userform containing the chart along with a picture of the E

    Combobox is to be placed in the worksheet "This Week's MM"
    Combobox is named PlayerChart
    Userform is named PlayerChartPic
    X and Y are running totals so the charts will be for trends analysis

    I love the idea of using Jim's image list control.  When design mode is turned off, the control completely disappears from view, meaning I can put it anywhere.  The chart idea in Stephen Bullen's example looks perfect, too.  Maybe I will end up using a combination of the two, IDK.  The metafile chart is very classy to me.  Just wondering how difficult it would be to add a picture to the metafile chart or if that's possible.  My original idea was to turn a ListBox on the UserForm into a spreadsheet using code and add the chart to the ListBox area...then use the standard image control for the pic.  I am extremely happy that the file will likely not have to be accompanied by a pic folder when it's downloaded.  Wouldn't work well for this project of mine...

    The challenge I am most focused on for now is the code for populating the combobox with the Es from the ranges I listed.  The code for the userform chart should be along the same lines I suppose.  But the code will only have to search out the Y and Z for the one E that has been chosen.  That should make it easier to write.  This is almost the exact same challenge I had with some search code for a search box a little while back, now that I think about it, except that the information will be converted into a chart rather than placed into the ListBox as text and numbers.

    Friday, December 30, 2011 2:00 AM
  • Afraid I don't follow your objective with the charts, well, at least not without putting my concentration cap on! However from what I can just about gather I expect you could set your chart up to use one or more Dynamic Ranges, controlled from a Combo; some examples

    http://peltiertech.com/Excel/Charts/Dynamics.html

    http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/

    If a dynamic range is not appropriate code (say triggered by a change event) could update the chart series with the new data.  With either approach an image of the chart can be copied to the form with "PastePicture".

    The metafile chart is very classy to me. Just wondering how difficult it would be to add a picture to the metafile chart or if that's possible.

    Yes a metafile image of a chart has the advantage that the image can be rescaled with minimal loss of quality.

    Any image can be added or rather inserted to the ChartArea, though I don't follow why you'd want to show an image of a chart on a chart rather than the actual chart.

    As I'm sure you know an image of a chart can be saved to file using the chart.Export method

    Peter Thornton

    • Marked as answer by AtlBo Monday, January 2, 2012 9:18 AM
    Friday, December 30, 2011 11:38 AM
    Moderator
  • Sorry Peter...the image will be of a person associated with the graph.  I would like the picture of the person to be on the chart, which is of performance related data...
    Monday, January 2, 2012 1:21 PM
  • As I mentioned no problem to insert a picture on the chart. Select the chart, and do Insert, Picture, size and position to suit.

    Alternatively you could "fill" the chart area or plot area with a picture, do Format, "Picture or texture fill, File". If filling the chart area you might want to give the plot area invisible fill.

    Peter Thornton

    Monday, January 2, 2012 2:14 PM
    Moderator