Dec 13 2022 10:50 AM - edited Dec 13 2022 10:52 AM
I have the following code:
Private Sub img_Browse_Click()On Error Resume Next
Dim img As String
Dim xCmpPath As String
img = Application.GetOpenFilename
If img <> False Then
Me.img_Photo.Picture = LoadPicture(img)
xCmpPath = img
Sheet1.Range("AE1").FormulaR1C1 = xCmpPath
End If
End Sub
It worked perfectly with my userform but sadly I have to change all my coding to work on a Worksheet (Sheet3)
I tried to change the code accordingly, but nothing seems to work:
Private Sub img_Browse()
On Error Resume Next
Dim img As String
Dim xCmpPath As String
img = Application.GetOpenFilename
If img <> False Then
Sheet3.Shapes.Range(Array("img_Photo")).Picture = LoadPicture(img)
xCmpPath = img
Sheet1.Range("AE1").FormulaR1C1 = xCmpPath
End If
End Sub
Please can someone tell me what is wrong with the above code?
And how can I set it back to "" again when I use a "clear" command button.
I struggle greatly to change all the userform coding to work with the combo boxes, combo list, image etc. on/in my sheet.... And this is only the start of all the changes I have to make :(
Dec 13 2022 12:43 PM
SolutionIn such situations, you should temporarily comment out the line
On Error Resume Next
because with it, you won't be notified of any errors.
You declare img as a String, but a String cannot be False. The line
If img <> False Then
causes a Type Mismatch error.
You should declare img as a Variant. That allows it to be any type, whether Boolean (False) or String (a file path).
In the second place, as far as I know, you cannot change the picture on an existing picture shape. You have to delete the existing picture and insert a new one.
Try this version - make sure that there is a shape (any shape) named img_Photo on Sheet3.
Private Sub img_Browse()
Dim img As Variant
Dim shp As Shape
Dim x As Single
Dim y As Single
Dim w As Single
Dim h As Single
Dim xCmpPath As String
'On Error Resume Next
img = Application.GetOpenFilename(FileFilter:= _
"Pictures (*.jpg;*.png;*.gif;*.bmp),*.jpg;*.png;*.gif;*.bmp")
If img <> False Then
Set shp = Sheet3.Shapes("img_Photo")
x = shp.Left
y = shp.Top
w = shp.Width
h = shp.Height
shp.Delete
Set shp = Sheet3.Shapes.AddPicture(img, False, True, x, y, h, w)
shp.Name = "img_Photo"
xCmpPath = img
Sheet1.Range("AE1").FormulaR1C1 = xCmpPath
End If
End Sub
Dec 13 2022 10:29 PM
Dec 13 2022 11:05 PM
@Hans Vogelaar Hans img_Photo is an ActiveX Control Image is it possible to keep the Picture Alignment as fmPictureAlignmentCentre and PictureSizeMode to fmPictureSizeModeZoom?
Thank you
Kind Regards
Dec 14 2022 04:03 AM - edited Dec 14 2022 04:04 AM
For an ActiveX image control, it's actually easier:
Private Sub img_Browse()
Dim img As Variant
'On Error Resume Next
img = Application.GetOpenFilename(FileFilter:= _
"Pictures (*.jpg;*.png;*.gif;*.bmp),*.jpg;*.png;*.gif;*.bmp")
If img <> False Then
With Sheet3.img_Photo
.Picture = LoadPicture(img)
.PictureAlignment = fmPictureAlignmentCenter
.PictureSizeMode = fmPictureSizeModeZoom
End With
Sheet1.Range("AE1").Value = img
End If
End Sub
Dec 14 2022 04:15 AM
Dec 13 2022 12:43 PM
SolutionIn such situations, you should temporarily comment out the line
On Error Resume Next
because with it, you won't be notified of any errors.
You declare img as a String, but a String cannot be False. The line
If img <> False Then
causes a Type Mismatch error.
You should declare img as a Variant. That allows it to be any type, whether Boolean (False) or String (a file path).
In the second place, as far as I know, you cannot change the picture on an existing picture shape. You have to delete the existing picture and insert a new one.
Try this version - make sure that there is a shape (any shape) named img_Photo on Sheet3.
Private Sub img_Browse()
Dim img As Variant
Dim shp As Shape
Dim x As Single
Dim y As Single
Dim w As Single
Dim h As Single
Dim xCmpPath As String
'On Error Resume Next
img = Application.GetOpenFilename(FileFilter:= _
"Pictures (*.jpg;*.png;*.gif;*.bmp),*.jpg;*.png;*.gif;*.bmp")
If img <> False Then
Set shp = Sheet3.Shapes("img_Photo")
x = shp.Left
y = shp.Top
w = shp.Width
h = shp.Height
shp.Delete
Set shp = Sheet3.Shapes.AddPicture(img, False, True, x, y, h, w)
shp.Name = "img_Photo"
xCmpPath = img
Sheet1.Range("AE1").FormulaR1C1 = xCmpPath
End If
End Sub