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(ClassType:="Forms.CommandButton.1", _
    Link:=False, DisplayAsIcon:=False, Left:=l, Top:=t, Width:=w, Height:=h)
    Obj.Name = "TestButton"
    Obj.Object.Picture = LoadPicture(ActiveWorkbook.Path & "\example.bmp")
    'parameters desired height / width don't work???
    'Need to shink but not stretch
    ActiveSheet.OLEObjects(1).Object.Caption = "Test Button"
End Sub
LVL 1
HRISTeamAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
You could argue this all day, but the simple-est way of dealing with this might be to just simply insert the image directly on the Excel sheet.

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
WiBCommented:
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???
HRISTeamAuthor Commented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
Then, if I am reading your post correctly, AFAICT you might want to do the resizing in an image editing program first, then import the image to Excel.

Using even the most rudimentary image editor (MS Paint for ex.)
You have a fairly good set of tools for sizing the image.

;-)

JeffCoachman

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WiBCommented:
>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?
HRISTeamAuthor Commented:
Programmatically.

Does anyone know how to add a picture boxa a button at run time?
WiBCommented:
if you stretch/shrink a picture at the run time (I guess by using StretchBlt), you should use DrawText to put  desired text on the image and only after that assign a picture to the button, Obj.Object.Picture = ...
HRISTeamAuthor Commented:
WiB - This sounds promising...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.