Excel VBA Macro

[llravlin]

Hi,

I have this code to be able to select a picture and then select a cell to insert the picture. Everything works until trying to put the picture in the cell. I receive the following error: "Unable to get the Insert property of the Pictures class". Cannot figure out what I am doing wrong...Also, is there a way to resize as well in this code?

Sub Insert_Pict()
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


End Sub

Thanks,
Laura

 

[daverunt]

Hi Laura,

If you protect a Worksheet sheet manually you will see in the right-click context menu that 'Insert' is disabled.
The first line of your code is protecting the sheet.
I would imagine you need to Unprotect the sheet do the Insert then protect it?

Not sure about the resize.

 

[llravlin]

ok, I took the protecting out completely. It runs without any errors, but the picture does not go into the cell I selected...why??

Sub Insert_Pict()
Dim Pict
Dim ImgFileFormat As String
Dim PictCell As Range
Dim Ans As Integer




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




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






'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


End Sub

 

[AlphaFrog]

Try something like this...

    [COLOR=green]'Now paste to userselected cell[/COLOR]
GetCell:
    [COLOR=darkblue]Set[/COLOR] PictCell = Application.InputBox("Select the cell to insert into", Type:=8)
    [COLOR=darkblue]If[/COLOR] PictCell.Count > 1 [COLOR=darkblue]Then[/COLOR] MsgBox "Select ONE cell only": [COLOR=darkblue]GoTo[/COLOR] GetCell
    PictCell.Select
[COLOR=#ff0000]    With ActiveSheet.Pictures.Insert(Pict)
        .Width = PictCell.Width
        .Height = PictCell.Height
    End With[/COLOR]

 

[llravlin]

Thanks, but that didn't work either. The picture gets inserted into the first cell of the worksheet. This is so frustrating.

 

[AlphaFrog]

It worked for me. It inserted the picture in the cell I selected when prompted then resized it to that cell.

Maybe try this...

    [COLOR=green]'Now paste to userselected cell[/COLOR]
GetCell:
    [COLOR=darkblue]Set[/COLOR] PictCell = Application.InputBox("Select the cell to insert into", Type:=8)
    [COLOR=darkblue]If[/COLOR] PictCell.Count > 1 [COLOR=darkblue]Then[/COLOR] MsgBox "Select ONE cell only": [COLOR=darkblue]GoTo[/COLOR] GetCell
    PictCell.Select
    [COLOR=darkblue]With[/COLOR] ActiveSheet.Pictures.Insert(Pict)
        .Left = PictCell.Left
        .Top = PictCell.Top
        .Width = PictCell.Width
        .Height = PictCell.Height
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

 

[daverunt]

I used this, which worked for me.
I should point out that the picture isn't 'In' the cell it is floating on top.
You have to right click and change the properties of the picture to lock it into changing position and size with the cell.

ActiveSheet.Pictures.Insert(Pict).Select

With Selection
    .Width = ActiveCell.Width
    .Height = ActiveCell.Height
    .Top = ActiveCell.Top
    .Left = ActiveCell.Left
End With

 

[llravlin]

Thank you so much daverunt...It now works the way I want!

 

[daverunt]

At least you have it working, which is the important thing.
AlphaFrogs code works fine too.