Solved

VBA - Resize Image In a CommandButton (Excel)

Posted on 2010-09-08
9
2,955 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

687 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