VBA - Resize Image In a CommandButton (Excel)
I would like to resize an image in a button without stretching the image. I would like the image to fit in the button as large as possible without appearing distorted. In addition, I would like to attach a macro to the button click event. Thanks in advance for your help.
Sub CreateBtn(StartCell As Range, EndCell As Range)
Dim Obj As Object
Dim Code As String
ActiveSheet.Select
With StartCell
'Set button coordinates with cells
t = .Top
l = .Left
w = EndCell.Offset(0, 1).Left - .Left
h = EndCell.Offset(1, 0).Top - .Top
End With
'create button
Set Obj = ActiveSheet.OLEObjects.Add
Link:=False, DisplayAsIcon:=False, Left:=l, Top:=t, Width:=w, Height:=h)
Obj.Name = "TestButton"
Obj.Object.Picture = LoadPicture(ActiveWorkbook
'parameters desired height / width don't work???
'Need to shink but not stretch
ActiveSheet.OLEObjects(1).
End Sub
Sub CreateBtn(StartCell As Range, EndCell As Range)
Dim Obj As Object
Dim Code As String
ActiveSheet.Select
With StartCell
'Set button coordinates with cells
t = .Top
l = .Left
w = EndCell.Offset(0, 1).Left - .Left
h = EndCell.Offset(1, 0).Top - .Top
End With
'create button
Set Obj = ActiveSheet.OLEObjects.Add
Link:=False, DisplayAsIcon:=False, Left:=l, Top:=t, Width:=w, Height:=h)
Obj.Name = "TestButton"
Obj.Object.Picture = LoadPicture(ActiveWorkbook
'parameters desired height / width don't work???
'Need to shink but not stretch
ActiveSheet.OLEObjects(1).
End Sub
it's possible to do with StretchBLT, (it does stretch/shrink without visible quality loss)
and assign a macro via OnAction property of your button
but as boag2000 said WHY???
and assign a macro via OnAction property of your button
but as boag2000 said WHY???
ASKER
I already know how to insert image and resize it dynamically. I want to have a CommandButton, because I a automating an exitsting wookbook that created several sheets using buttons. My hope was to replicate the sheet exactly with buttons. In addition, I have text and an image togather on the button on the original sheet. The text will change depending on the position of the button. I don't want to layer a text box over an image. So, how can I accomplish this in a single button? Thank WiB and boaq2000 for your responses.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>In addition, I have text and an image togather on the button on the original sheet. The text will change >depending on the position of the button. I don't want to layer a text box over an image. So, how can I >accomplish this in a single button?
approach depends on how did you perform image resizing
did you do it programmatically or manually?
approach depends on how did you perform image resizing
did you do it programmatically or manually?
ASKER
Programmatically.
Does anyone know how to add a picture boxa a button at run time?
Does anyone know how to add a picture boxa a button at run time?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
WiB - This sounds promising...
If you resize the image from a "Corner" (not the middle of a "side") the it should keep it's aspect Ratio.
(Optional: If you then resize the image while holding down the Alt key, you can "kinda" size the image to the cells)
The create a macro to do what you want the button to do.
The right-click the image and select "assign macro".
Then assign the macro you just created to the image.
here is a sample
;-)
JeffCoachman
Excel--26460318--ButtonImageMacr.xls