Solved

Load picture from worksheet into Excel Userform using combobox selection

Posted on 2010-08-13
11
7,164 Views
1 Endorsement
Last Modified: 2012-05-10
I have four pictures pasted into a worksheet (there will be more later)
I have named these pictures and put the names into a list that populates a combobox
I want to open the userform and be able to make a selection from the combobox - this will load the matching picture into the userform.  I cant figure out the vba code to allow the string from the combobox to be used to select the picture.  Any pointers would be welcomed.  (a bonus would be the code to move through the pictures using the next and previous buttons.
PictureLoader.xls
1
Comment
Question by:JohnNZExcel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +2
11 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 33428179
JohnNZExcel,

Use the code below to load a picture into the Userform - adapt to your setup.

Essentially you need to:

1. Store all the pictures in a folder of their own and then use their names in the string

2. Add the picture file type to the string - if needed

3. Use the picture file names in the worksheet and in the ComboBox - with or without the file extension - depending on whether you add it or use it in the worksheet.

It's all in the attached file.

Hope that helps

Patrick

ps I have removed all the pictures from the Excel file
'simplified here
Private Sub cboExercise_Change()
Dim str1 As String

str1 = Me.cboExercise.Value '  & ".jpg"
Me.ExPic.Picture = LoadPicture("C:\FullPathHere\" & str1)

End Sub

Open in new window

PictureLoader-01.xls
0
 

Author Comment

by:JohnNZExcel
ID: 33434377
Thanks Patrick, that works really well and i will definitely make use of it.  However i am wanting to provide this file to other people to use and as a consequence i will need to house the pictures within the workbook (on one or more other worksheets which i will probably hide).  This is why i went down the road of pasting the pictures into the workbook, naming them and trying to call them into the userform using their object names. If you have any further suggestions to make this work then i would love to hear them.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33441427
JohnNZExcel

Patrick has asked for additional help here, and this is therefore NOT FOR POINTS but simply to help a colleague.

I have added a couple of shapes to the worksheet, and a macro to copy them to the first sheet.

I actually named them using the immediate window and the syntax:
activesheet.shapes(1).name = "fred"
activesheet.shapes(2).name = "doris"

I hope it helps.

Chris
Sub copyPics()

Sheets("picturestorage").Shapes("fred").Copy
Sheets("frontpage").Range("A" & 1).Select
ActiveSheet.Paste

Sheets("picturestorage").Shapes("doris").Copy
Sheets("frontpage").Range("A" & 6).Select
ActiveSheet.Paste

End Sub

Open in new window

PictureLoader-01.xls
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 81

Accepted Solution

by:
byundt earned 250 total points
ID: 33441538
I too am responding to Patrick's request for help.

Stephen Bullen wrote a nice bit of code that allows you to paste a picture to an image control in a userform without needing to get it from a separate file. Using his PastePicture sub, you can upload the picture in the userform as shown in the snippet.

You can download Bullen's code at http://www.oaltd.co.uk/Excel/SBXLPage.asp  The link is not working at this moment, but I had an old copy and added his code as a new module in the sample workbook.

Brad
Private Sub cboExercise_Change()
Worksheets("PictureStorage").Shapes(cboExercise.Value).CopyPicture
Set ExPic.Picture = PastePicture(xlPicture)
End Sub

Open in new window

PictureLoaderQ26400328.xls
0
 
LVL 81

Expert Comment

by:byundt
ID: 33441568
Code for the Next and Previous buttons appears in the snippet. This code was not part of the sample workbook I posted.

Private Sub cmdNext_Click()
If cboExercise.ListIndex < cboExercise.ListCount - 1 Then cboExercise.ListIndex = cboExercise.ListIndex + 1
End Sub

Private Sub cmdPrev_Click()
If cboExercise.ListIndex > 0 Then cboExercise.ListIndex = cboExercise.ListIndex - 1
End Sub

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33441582
If you insert the pictures into Image controls (ActiveX) on the worksheet, then you can simply assign the picture property of the relevant image control to the Picture property of the Image control on the userform.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33441914
JohnNZExcel,

Please do not award me any points in this question as the highly complex and working solution has been provided in full by byundt.

Patrick
0
 

Author Comment

by:JohnNZExcel
ID: 33442165
Many thanks to you all for your time and effort - the fact that you are helping each other to help me shows what a great attitude you all have to helping aspiring VBA programmers like me.  After Patrick's first response I spent considerable time over the weekend playing with the code and got it semi working which was very satisfying.  To now have the correct solution from byundt is awesome and the bonus of the next and previous buttons is the icing on the cake.  Very happy.
0
 

Author Closing Comment

by:JohnNZExcel
ID: 33442168
Great work - very happy
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33443953
ps. If you want the forward and backward buttons to simply cycle through the pictures, and not stop when at the end of the list, in either direction you could use the code below. It's only a minor addition to byundt's code.
Private Sub cmdNext_Click()
If cboExercise.ListIndex < cboExercise.ListCount - 1 Then
    cboExercise.ListIndex = cboExercise.ListIndex + 1
End If
If cboExercise.ListIndex = cboExercise.ListCount - 1 Then
    cboExercise.ListIndex = 0
End If
End Sub

Private Sub cmdPrev_Click()
If cboExercise.ListIndex > 0 Then
    cboExercise.ListIndex = cboExercise.ListIndex - 1
End If
If cboExercise.ListIndex = 0 Then
    cboExercise.ListIndex = cboExercise.ListCount - 1
End If
End Sub

Open in new window

0
 
LVL 81

Expert Comment

by:byundt
ID: 33444819
Patrick,
Wouldn't you prefer to use an If...Else block of code rather than two If blocks? As is, the Next button will never display the last image, nor the Previous button the first.

Brad
Private Sub cmdNext_Click()
If cboExercise.ListIndex < cboExercise.ListCount - 1 Then
    cboExercise.ListIndex = cboExercise.ListIndex + 1
Else
    cboExercise.ListIndex = 0
End If
End Sub

Private Sub cmdPrev_Click()
If cboExercise.ListIndex > 0 Then
    cboExercise.ListIndex = cboExercise.ListIndex - 1
Else
    cboExercise.ListIndex = cboExercise.ListCount - 1
End If
End Sub

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question