Solved

Load picture from worksheet into Excel Userform using combobox selection

Posted on 2010-08-13
11
6,431 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
Comment Utility
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
Comment Utility
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
Comment Utility
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 80

Accepted Solution

by:
byundt earned 250 total points
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Great work - very happy
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

10 Experts available now in Live!

Get 1:1 Help Now