CODEWITHSUNDEEP
vbaexcelcells
Sjoerd Eeman
Sjoerd Eeman

Reputation: 13

VBA Center picture in merged cells

I've been trying to fix this problem for a while. The following code inserts a picture from your choose to my excel document. It places the picture in cell B10 and resizes it to the height of one of my merged cells. Now the problem is that I can't get it centerd.

.Left = 35#

With the line above i can manually center one picture, but i want every other picture with other width's to be centerd aswell. Can anyone help me with this problem? The code below is what i've been using. Thanks in advance!

Sub Insert_Pic_Section_One()

Dim fileName1 As Variant

fileName1 = Application.GetOpenFilename(filefilter:="Tiff Files(*.tif;*.tiff),*.tif;*.tiff,JPEG Files (*.jpg;*.jpeg;*.jfif;*.jpe),*.jpg;*.jpeg;*.jfif;*.jpe,Bitmap Files(*.bmp),*.bmp", FilterIndex:=2, Title:="Choose picture", MultiSelect:=False)

 If fileName1 = False Then
 Exit Sub
 Else
 ActiveWorkbook.ActiveSheet.Select
 Range("B10").Select
 Dim picture1 As Object
 Set picture1 = ActiveWorkbook.ActiveSheet.Pictures.Insert(fileName1)

  With picture1
   .Top = .Top
   .Left = 35#
   .Width = .Width
   .Height = 233#
  End With

 End If

End Sub

Upvotes: 1

Views: 7003

Answers (1)

arcadeprecinct
arcadeprecinct

Reputation: 3777

No need to select anything. Because you use a merged cell you need to use .MergeArea otherwise it will only give you the height and width of the unmerged row and column.

Dim ws As Worksheet
Dim targetCell As Range
Dim picture1 As Picture

Set ws = ActiveSheet 'replace with actual worksheet if possible
Set targetCell = ws.Range("B10")
Set picture1 = ws.Pictures.Insert(fileName1)

With picture1
    .Height = targetCell.MergeArea.Height 'set height first because width will change
    .Top = targetCell.Top
    .Left = targetCell.Left + (targetCell.MergeArea.Width - .Width) / 2
End With

Upvotes: 3

Related Questions