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
Solved

Image placed on userform lost when file opened on a different computer

Posted on 2014-12-17
9
225 Views
Last Modified: 2014-12-21
Hi all, Nick 97 just helped with this.

I have a spreadsheet with operations perfomed by VBA code on several userforms. I have code that allows a user to select a picture and place it on a user form as image1. The code works properly and users can navigate to a graphic file, select the file and the image is placed on the userfom. With help from Nick97, now each time the userform is initialised the graphic shows on the userform.

Thank works OK while the file is opened on my computer. Can I embed the graphic on the userform so if the file is opened on another computer, (it wont have the correct graphic or file path)  the graphic will show on the userform on the new computer.

current routines are:

Private Sub CommandButton2_Click()

Dim openDialog As Office.FileDialog
Dim FName As String

Set openDialog = Application.FileDialog(msoFileDialogFilePicker)
openDialog.Filters.Clear
openDialog.Filters.Add "JPEG Files", "*.jpg"

     With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .ButtonName = "Submit"
        .Title = "Select an image file"
        .Filters.Add "Image", "*.gif; *.jpg; *.jpeg", 1
        If .Show = -1 Then

            Me.Image1.PictureSizeMode = fmPictureSizeModeZoom
            Me.Image1.Picture = LoadPicture(.SelectedItems(1))
            Me.Repaint
            
            FName = openDialog.SelectedItems(1)
            Sheets("SoftwareParameters").Range("LogoGraphic") = FName
       Else
            

       End If
       
    End With
End Sub


Private Sub userForm_Initialize()
Me.Image1.Picture = LoadPicture(Sheets("SoftwareParameters").Range("LogoGraphic"))
End Sub

Open in new window


Bunchil
0
Comment
Question by:Bunchil
  • 5
  • 4
9 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40505722
I don't know if you can or not.
What you CAN do is take .SelectedItems(1) and use that to save the desired file to a desired network location.
Then the image will work across all the computers on the network.
Do you need help to work out the nuts and bolts of that?

Or do you want to wait and see if someone knows if you can embed the image?
0
 
LVL 1

Author Comment

by:Bunchil
ID: 40506022
Hi Nick, thanks again it is not a network problem as other computers that will use the file will be in different locations and not networked. Could I save the graphic on a worksheet in the work book and then load it into the userform from there so although they could not see it the graphic used on the userform will be imported onto a hidden worksheet. If that is possible  can you advise how to code the importation to a workssheet and then link to a userform please
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40506038
There's this
http://spreadsheetpage.com/index.php/tip/pasting_an_image_to_a_userform_control/

Your code from before, is that code to permanently set an image on the userform?
Or the image can be changed by the user, but should remain on the form until changed at a later time.
Because I am thinking you wouldn't have bothered to code for a one-and-done,

I think I can get it code to embed in the userform itself -- but I do have to experiment.
Let me know what the whole usage scenario is
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
LVL 1

Author Comment

by:Bunchil
ID: 40506083
Hi Nick, checked the link you sent and that works but I don't think the steps that include opening the VBA editor can be coded as VBA code.

The usage scenario is that I have a simple cashflow spreadsheet that people like and it can print summary pages for budgeting purposes. User have asked that they can have their own log placed on the userform for appearance sake. So with some code only accessed by a password, before I send them a copy of the spreadsheet I want to place individual logos on the userform.

I already have code to put the users logo in the header of pages so printed sheet will have the users logo on them.

Individual users will not be able to make any changes - they will be done by me before the spreadsheet is made available.

I look forward to hearing if you are successful with your experimentation
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40506114
It can all get coded.
Look here
http://www.oaltd.co.uk/Excel/Default.htm
Now, a lot of that is API code.
That can be hard on the brain.
I do a lot of fun things with images in VBA using the WIA library
http://www.experts-exchange.com/Database/MS_Access/A_13519-Printing-many-large-images-in-MS-Access-reports.html

So, I just need to take your code for selecting an image, combine it with my code for loading the image in WIA with that site's (http://www.oaltd.co.uk/Excel/Default.htm) technique for knocking the clipboard image into the control.

All doable, just kitschy.  I'll have a go at it tomorrow.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40507819
Yup,
Take the stuff from the PastePicture.zip from the link here and throw it into a module.
What I did from there is throw a new sheet in, throw the image from .SelectedItems(1) on it, knock that image to the clipboard and hammer it on the userform.

There's a whole module to add too, but the alteration of your code is below.
Sample xls attached

Private Sub CommandButton2_Click()
Dim openDialog As Office.FileDialog
Dim SheetName As String

Set openDialog = Application.FileDialog(msoFileDialogFilePicker)
openDialog.Filters.Clear
openDialog.Filters.Add "JPEG Files", "*.jpg"

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .ButtonName = "Submit"
    .Title = "Select an image file"
    .Filters.Add "Image", "*.gif; *.jpg; *.jpeg", 1
    If .Show = -1 Then
        'add a sheet
        Worksheets.Add
        ActiveSheet.Pictures.Insert(.SelectedItems(1)).Select
        ActiveSheet.Shapes(1).CopyPicture xlScreen, xlBitmap
        SheetName = ActiveSheet.Name
        Application.DisplayAlerts = False
        Worksheets(SheetName).Delete
        Application.DisplayAlerts = True

        'Paste the picture from the clipboard into our image control
        Me.Image1.Picture = PastePicture(xlBitmap)
        Me.Repaint
    Else
    
    End If

End With
End Sub

Open in new window

Clipboard.xls
0
 
LVL 1

Author Comment

by:Bunchil
ID: 40508138
Hi Nick Thanks for your help help. I understand what you have explained and have inserted module as suggested but can't get it to work so I downloaded your sample file and that does not appear to work either. I removed code to delete the sheet inserted to take the graphic and it put the graphic on the userform. However if I stop code to look at the userform the graphic disappears again so it is still not embedded in the userform.

Have I made any errors with the code you gave me
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40509617
No, but it seems what I found could put the image on the userform but not make it stick.

Sooooooooooooooooooooooo...

I guess we'll keep the sheet that the code adds and the image on it.
and make it xlVeryHidden.
and load the image from it if it exists.

It's a bit of kludge but it works.

Nick67
Clipboard-v1.xls
0
 
LVL 1

Author Closing Comment

by:Bunchil
ID: 40512188
Hi Nick, Thanks again,  works perfectly. Have a great Christmas
Bunchil
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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.

839 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