Solved

Load picture from worksheet into Excel Userform using combobox selection

Posted on 2010-08-13
11
6,591 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

929 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now