Jeremy Zhang
New Member
- Joined
- Mar 8, 2023
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
Hi,
I am new to VBA, I have two VBA codes from the web and they work fine for me except below:
I am new to VBA, I have two VBA codes from the web and they work fine for me except below:
- For the auto resize image, I would like to set the image size a little bit smaller than the cells
- I woud also like to combine these two codes to one, so that I can complete these two actiosn by one time
VBA Code:
Public Sub AutoResizeImage()
On Error GoTo Select_Image
Dim ZImageWtoHRatio As Single
Dim QWtoHRatio As Single
With Selection
ZImageWtoHRatio = .Width / .Height
End With
With Selection.TopLeftCell
QWtoHRatio = .Width / .RowHeight
End With
Select Case ZImageWtoHRatio / QWtoHRatio
Case Is > 1
With Selection
.Width = .TopLeftCell.Width
.Height = .Width / ZImageWtoHRatio
End With
Case Else
With Selection
.Height = .TopLeftCell.RowHeight
.Width = .Height * ZImageWtoHRatio
End With
End Select
With Selection
.Top = .TopLeftCell.Top
.Left = .TopLeftCell.Left
End With
Exit Sub
Select_Image:
MsgBox "Choose an Image and Run the Macro."
End Sub
VBA Code:
Sub CenterPicturesHnV()
Dim myR As Range
Dim myP As Shape
For Each myP In ActiveSheet.Shapes
Set myR = myP.TopLeftCell
myP.Left = myR.Left + (myR.Width - myP.Width) / 2
myP.Top = myR.Top + (myR.Height - myP.Height) / 2
Next myP
End Sub