picture size

[ttowncorp]

I'm trying to get a picture to snap into a specific range but for some reason when i copy and paste the code again and change the range it keep getting an error highlighted in yellow, even though it identical besides the range location. what am I doing wrong?

Private Sub CommandButton2_Click()
Dim strFileName As String
Dim objPic As Picture
Dim rngDest As Range
strFileName = Application.GetOpenFilename( _
FileFilter:="Images (*.jpg;*.gif;*.png;*.bmp),*.jpg;*.gif;*.png;*.bmp", _
Title:="Please select an image...")
If strFileName = "False" Then Exit Sub

Set rngDest = Range("B3:H39") 'This line sets the ActiveCell as the Target for insertion of the picture
Set objPic2 = Worksheets("pic 2").Pictures.Insert(strFileName)
With objPic
.ShapeRange.LockAspectRatio = msoFalse 'This line locks the ratio of Width to Height if =msoTrue, no distortion
.Left = rngDest.Left 'The ' .Left=' and ' .Top=' in this and the next line set the respective locations for the placement of the picture
.Top = rngDest.Top
.Width = rngDest.Width 'You can put an single quote before this line if you want to hold just the Height of the picture to be the height of the cell where it will be placed
.Height = rngDest.Height 'You can put an single quote before this line if you want to hold just the Width of the picture to be the width of the cell where it will be placed
End With

End Sub


Private Sub CommandButton3_Click()
Dim strFileName As String
Dim objPic As Picture
Dim rngDest As Range
strFileName = Application.GetOpenFilename( _
FileFilter:="Images (*.jpg;*.gif;*.png;*.bmp),*.jpg;*.gif;*.png;*.bmp", _
Title:="Please select an image...")
If strFileName = "False" Then Exit Sub

Set rngDest = Range("K3:Q39") 'This line sets the ActiveCell as the Target for insertion of the picture
Set objPic2 = Worksheets("pic 2").Pictures.Insert(strFileName)
Set objPic2 = Worksheets("pic 2").Pictures.Insert(strFileName)
With objPic
.ShapeRange.LockAspectRatio = msoFalse 'This line locks the ratio of Width to Height if =msoTrue, no distortion
.Left = rngDest.Left 'The ' .Left=' and ' .Top=' in this and the next line set the respective locations for the placement of the picture
.Top = rngDest.Top
.Width = rngDest.Width 'You can put an single quote before this line if you want to hold just the Height of the picture to be the height of the cell where it will be placed
.Height = rngDest.Height 'You can put an single quote before this line if you want to hold just the Width of the picture to be the width of the cell where it will be placed
End With

End Sub

 

[jolivanes]

Sub Maybe()
Dim strFileName As String
Dim objPic As Picture
Dim rngDest As Range
strFileName = Application.GetOpenFilename( _
FileFilter:="Images (*.jpg;*.gif;*.png;*.bmp),*.jpg;*.gif;*.png;*.bmp", _
Title:="Please select an image...")
If strFileName = "False" Then Exit Sub
Set rngDest = Range("B3:H39") 'This line sets the ActiveCell as the Target for insertion of the picture
Set objPic = Worksheets("Sheet1").Pictures.Insert(strFileName)
With objPic
.ShapeRange.LockAspectRatio = msoFalse 'This line locks the ratio of Width to Height if =msoTrue, no distortion
.Left = rngDest.Cells(1).Left 'The ' .Left=' and ' .Top=' in this and the next line set the respective locations for the placement of the picture
.Top = rngDest.Cells(1).Top
.Width = rngDest.Width 'You can put an single quote before this line if you want to hold just the Height of the picture to be the height of the cell where it will be placed
.Height = rngDest.Height 'You can put an single quote before this line if you want to hold just the Width of the picture to be the width of the cell where it will be placed
End With
End Sub

 

[jolivanes]

Have not heard back if suggestion works.
If you want to insert pictures at intervals of 9 columns, you might try this. No need changing ranges in the code.
Run the macro as many times as you want. It will insert the selected picture 2 columns past the last picture.
It is important that you don't move pictures because the code works on counting pictures with the topleftcell in row 3.

Sub Pics_At_Intervals()
Dim shp As Shape, startcol As Long, col As Long, x As String
startcol = 0
    For Each shp In ActiveSheet.Shapes
        If shp.TopLeftCell.Row = 3 Then col = shp.TopLeftCell.Column
        If startcol < col Then startcol = col
    Next shp
        startcol = IIf(startcol = 0, 2, startcol + 9)
    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Add "Images", "*.gif; *.jpg; *.jpeg", 1
        If .Show = -1 Then x = .SelectedItems(1)
    End With
ActiveSheet.Shapes.AddPicture(x, False, True, Cells(3, startcol).Left, Cells(3, startcol).Top, Cells(3, startcol + 7).Left - Columns(startcol).Left, Cells(38, startcol + 6).Top).Name = "Pic " & startcol
End Sub