File Browser to Insert Picture into Image Control

[ellabell]

Hello to everyone,

Using my basic VBA knowledge I've been trying to code a image control that I embedded on a worksheet ("Picture Template"). I can find codes that will launch a image specific file browser but then I'm having a hard time making the link to actually show the image in the control that I created on the worksheet.

This is a code that I had found on another forum that I have been trying to manipulate. I can filter out the sheet protection stuff and at the end where it brings up the msg box to enter a cell for the image to be pasted in that's where I want to load it into my control (Image1).

Dim Pict
Dim ImgFileFormat As String
Dim PictCell As Range
Dim Ans As Integer

ActiveSheet.Protect True, True, True, True, True
ImgFileFormat = "Image Files (*.bmp),others, tif (*.tif),*.tif, jpg (*.jpg),*.jpg"
GetPict:
Pict = Application.GetOpenFilename(ImgFileFormat)
'Note you can load in any nearly file format
If Pict = False Then End
Ans = MsgBox("Open : " & Pict, vbYesNo, "Insert Picture")
If Ans = vbNo Then GoTo GetPict 'Now paste to userselected cell
GetCell:
Set PictCell = Application.InputBox("Select the cell to insert into", Type:=8)
If PictCell.Count > 1 Then MsgBox "Select ONE cell only": GoTo GetCell
PictCell.Select
ActiveSheet.Pictures.Insert(Pict).Select


The second thing I need to do is have the image displayed on another worksheet ("Template"). I know how to do this with other types of data but what I'm trying doesn't seem to be working for these controls.

Dim ws As Worksheet
Set ws = Worksheets("Picture Template")
Me.Image1.Picture = ws.Image1.Picture

Thank you for taking the time to help me out

 

[MickG]

Try

Me.Image1.Picture = LoadPicture(Pict)

 

[ellabell]

Sorry, that didn't seem to do anything for me.

 

[MickG]

If you place the following just above that line you should see the file path for the Picture.

MsgBox Pict

If you've got that I'm not sure why it not loading !!
also I dimensioned "Pict":-
Dim Pict as string

 

[ellabell]

I think maybe I'm having some difficulties with this part because I don't have the first code working properly. I took that original code that I mentioned and I've been trying to work with it to get what I want this is what I've done so far:


Dim Pict
Dim ImgFileFormat As String
Dim PictCell As Range
Dim Ans As Integer

ImgFileFormat = "Image Files jpg (*.jpg),*.jpg,bmp (*.bmp), *.bmp, tif (*.tif),*.tif"

GetPict:
Pict = Application.GetOpenFilename(ImgFileFormat)
'Note you can load in any nearly file format
If Pict = True Then Image1.Picture = Pict.Value Else
If Pict = False Then End

Ans = MsgBox("Open : " & Pict, vbYesNo, "Insert Picture")
If Ans = vbNo Then GoTo GetPict
If Ans = vbYes Then Image1.Picture = Pict.Picture

And it does everything fine except actually put the picture into my Image1 control... minor detail haha. Can you lend any insight on this problem?

Thanks so much for taking your time to help me out

 

[MickG]

Try this:-
Its seems to work but I feel it could be better !!!

[COLOR="Navy"]Sub[/COLOR] MG24Jun44
[COLOR="Navy"]Dim[/COLOR] ImgFileFormat [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] PictCell [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ans [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Pict [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
ImgFileFormat = "Image Files jpg (*.jpg),*.jpg,bmp (*.bmp), *.bmp, tif (*.tif),*.tif"
GetPict:
Pict = Application.GetOpenFilename(ImgFileFormat)
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not Pict = False [COLOR="Navy"]Then[/COLOR]
        Ans = MsgBox("Open : " & Pict, vbYesNo, "Insert Picture")
        [COLOR="Navy"]If[/COLOR] Ans = vbNo [COLOR="Navy"]Then[/COLOR] GoTo GetPict
        [COLOR="Navy"]If[/COLOR] Ans = vbYes [COLOR="Navy"]Then[/COLOR] Me.Image1.Picture = LoadPicture(Pict)
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
    [COLOR="Navy"]End[/COLOR] If
 
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ellabell]

As a rookie, I feel like all the coding I end up with could be better haha. But that worked! So it will give me something to start with However, back to the one that seems like it should be really easy... I can't get that image to then show up in the image control on the other sheet. I tried your code:

Dim Pict As String
MsgBox Pict
Me.Image1.Picture = LoadPicture(Pict)

and for me it brings up the msgbox (but empty) and then nothing appears in the control.

 

[ellabell]

OK, so something else I discovered is that I know that the original code was designed to protect a sheet. I thought I deleted that part of the code but it seems that it protects it anyways. Plus, it asks for a password (that I obviously don't know) in order to unprotect it. How do I get it to stop that action? Also, could this also be a reason for the image not transferring to my other sheet?

 

[MickG]

That Protect piece of code appears to protect the sheet, A password is required when one has not been set. Resulting in not being able to Un pootect the sheet. I resolved this by removing that piece of code and deleting the sheet, and starting again.
Although it works OK if you set a Password.
The code code worked Ok for me in Placing the Image in an "Image Control" on another sheet.
Alter sheet Name to suit.

Dim ImgFileFormat As String
Dim PictCell As Range
Dim Ans As Integer
Dim Pict As String
ImgFileFormat = "Image Files jpg (*.jpg),*.jpg,bmp (*.bmp), *.bmp, tif (*.tif),*.tif"
GetPict:
Pict = Application.GetOpenFilename(ImgFileFormat)
On Error Resume Next
    If Not Pict = False Then
        Ans = MsgBox("Open : " & Pict, vbYesNo, "Insert Picture")
        If Ans = vbNo Then GoTo GetPict
        If Ans = vbYes Then Sheets("Sheet29").Image1.Picture = LoadPicture(Pict)
    Else
        Exit Sub
    End If

 

[ellabell]

Thank you so much for all your help! I've got everything working I really appreciate your time and input. Thanks!!