Solved

VBA - Resize Image In a CommandButton (Excel)

Posted on 2010-09-08
9
2,812 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:HRISTeam
  • 3
  • 3
  • 2
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33633577
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
0
 
LVL 3

Expert Comment

by:WiB
ID: 33636120
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???
0
 
LVL 1

Author Comment

by:HRISTeam
ID: 33636681
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 33639348
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
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 3

Expert Comment

by:WiB
ID: 33641822
>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?
0
 
LVL 1

Author Comment

by:HRISTeam
ID: 33654794
Programmatically.

Does anyone know how to add a picture boxa a button at run time?
0
 
LVL 3

Assisted Solution

by:WiB
WiB earned 250 total points
ID: 33661557
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 = ...
0
 
LVL 1

Author Comment

by:HRISTeam
ID: 33664814
WiB - This sounds promising...
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now