Solved

Load picture from worksheet into Excel Userform using combobox selection

Posted on 2010-08-13
11
7,383 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

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…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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