VBA Error That I Cannot Solve

[Jingles3X]

I am new to vba, and have been somewhat successful in some of the basics, but I am having trouble
getting the below code to work. I am trying to select a picture from file, then insert it in a range,
completely centered. I really have tried to make it work, and have tried the search function, but it
just isnt happening for me.

I would be very grateful if someone with knowledge could look at it and tell me where i zigged instead of zagged.

Thanks.

Private Sub CommandButton11_Click()

Dim fNameAndPath As Variant
Dim img As Picture
Set ws = Worksheets("Cover_Page")


fNameAndPath = Application.GetOpenFilename(Title:="Select Picture To Be Imported")
If fNameAndPath = False Then Exit Sub
    Set img = Worksheets("Cover_Page").Pictures.Insert(fNameAndPath)
    With img
       'Resize Picture to fit in the range....
       .Left = ActiveSheet.Range("B15").Left
       .Top = ActiveSheet.Range("B15").Top
       .Width = ws.("B15:I15").Width
       .Height = ws.("B15:I33").Height
img.Top = cellLocation.Top + (cellLocation.Height / 2) - (myImage.Height / 2)
img.Left = cellLocation.Left + (cellLocation.Width / 2) - (myImage.Width / 2)


       .Placement = 1
       .PrintObject = True
    End With

End Sub

 

[Domenic]

After taking a quick look, here's where you zigged, instead of zagged . . .

       .Width = ws.("B15:I15").Width
       .Height = ws.("B15:I33").Height

It should be . . .

       .Width = ws.Range("B15:I15").Width
       .Height = ws.Range("B15:I33").Height

Also, it looks like you have not defined cellLocation and myImage.

And you're assigning a value to both Top and Left twice.

 

[Jingles3X]

Private Sub CommandButton11_Click() Dim fNameAndPath As Variant Dim img As Picture Set ws = Worksheets("Cover_Page") fNameAndPath = Application.GetOpenFilename(Title:="Select Picture To Be Imported") If fNameAndPath = False Then Exit Sub Set img = Worksheets("Cover_Page").Pictures.Insert(fNameAndPath) With img 'Resize Picture to fit in the range.... .Left = ActiveSheet.Range("B15").Left .Top = ActiveSheet.Range("B15").Top .Width = ws.("B15:I15").Width .Height = ws.("B15:I33").Height img.Top = cellLocation.Top + (cellLocation.Height / 2) - (myImage.Height / 2) img.Left = cellLocation.Left + (cellLocation.Width / 2) - (myImage.Width / 2) .Placement = 1 .PrintObject = True End With End Sub

Thank you!. I did fix that, and the picture now places in the general area where I need it, but It does not center....it just stays in the top left corner of the range. Any idea why?

Private Sub CommandButton11_Click()

Dim fNameAndPath As Variant
Dim img As Picture
Set ws = Worksheets("Cover_Page")


fNameAndPath = Application.GetOpenFilename(Title:="Select Picture To Be Imported")
If fNameAndPath = False Then Exit Sub
    Set img = Worksheets("Cover_Page").Pictures.Insert(fNameAndPath)
    With img
       'Resize Picture to fit in the range....
       .Left = ActiveSheet.Range("C15").Left
       .Top = ActiveSheet.Range("C15").Top
       .Width = ws.Range("C15:I15").Width
       .Height = ws.Range("C15:I33").Height
       .Placement = 1
       .PrintObject = True
    End With

End Sub

 

[Domenic]

If you mean that you want the picture to span the width and height of your range, try . . .

    With img
        .ShapeRange.LockAspectRatio = msoFalse
        'etc
        '
        '
    End With

 

[Jingles3X]

If you mean that you want the picture to span the width and height of your range, try . . .

    With img
        .ShapeRange.LockAspectRatio = msoFalse
        'etc
        '
        '
    End With

added after the rest of the code but before the End Sub?

 

[Domenic]

Add it to the first line of your With/End With statement. Note, however, your code can be re-written as follows...

Private Sub CommandButton11_Click()

    Dim fNameAndPath As Variant
    Dim ws As Worksheet
    Dim rng As Range
    Dim img As Picture
    
    fNameAndPath = Application.GetOpenFilename(Title:="Select Picture To Be Imported")
    If fNameAndPath = False Then Exit Sub
    
    Set ws = Worksheets("Cover_Page")
    
    Set rng = ws.Range("C15:I33")
    
    Set img = ws.Pictures.Insert(fNameAndPath)
    
    With img
       'Resize Picture to fit in the range....
       .ShapeRange.LockAspectRatio = msoFalse
       .Left = rng.Left
       .Top = rng.Top
       .Width = rng.Width
       .Height = rng.Height
       .Placement = 1
       .PrintObject = True
    End With

End Sub

 

[Jingles3X]

Add it to the first line of your With/End With statement. Note, however, your code can be re-written as follows...

Private Sub CommandButton11_Click()

    Dim fNameAndPath As Variant
    Dim ws As Worksheet
    Dim rng As Range
    Dim img As Picture
   
    fNameAndPath = Application.GetOpenFilename(Title:="Select Picture To Be Imported")
    If fNameAndPath = False Then Exit Sub
   
    Set ws = Worksheets("Cover_Page")
   
    Set rng = ws.Range("C15:I33")
   
    Set img = ws.Pictures.Insert(fNameAndPath)
   
    With img
       'Resize Picture to fit in the range....
       .ShapeRange.LockAspectRatio = msoFalse
       .Left = rng.Left
       .Top = rng.Top
       .Width = rng.Width
       .Height = rng.Height
       .Placement = 1
       .PrintObject = True
    End With

End Sub

Thank you so much! I truly appreciate your help. Out of curiosity, If i wanted to apply a formatting option from the Style menu, is there a way to do that with VBA or does it have to be manually performed? (if you right click on a picture and click into 'Style" which puts different frames/shapes around the pic.)

 

[Domenic]

It seems as though there's no easy way. It looks like you will have to set a number of properties to achieve your desired result. I think you'll need to refer to the ShapeRange property of the Picture object, and set the different properties of the ShapeRange object.

    With img.ShapeRange
        .ShapeStyle = .....
        .Shadow = .....
        .Glow = .....
        .Glow.Radius = .....
    End With

For a list of properties of the ShapeRange object and their available settings, have a look here...

ShapeRange object (Excel)

Office VBA reference topic

docs.microsoft.com

 

[Jingles3X]

It seems as though there's no easy way. It looks like you will have to set a number of properties to achieve your desired result. I think you'll need to refer to the ShapeRange property of the Picture object, and set the different properties of the ShapeRange object.

    With img.ShapeRange
        .ShapeStyle = .....
        .Shadow = .....
        .Glow = .....
        .Glow.Radius = .....
    End With

For a list of properties of the ShapeRange object and their available settings, have a look here...

ShapeRange object (Excel)

Office VBA reference topic

docs.microsoft.com

Thanks Again! I have only a few more things to accomplish with this before my boss tells me I did it wrong Cannot thank you enough!

 

[Domenic]

That's great, glad I could help.

And good luck. ?

Cheers!