Solved: can you make command buttons look nice in excel?

[k13r4n]

Ive just finished the first version of a spreadsheet here at work, its going to be used by a few of the other staff and so i want it to look nice and snazy. only thing is the command buttons on the spreadsheet that call the various bits of VBA into action look crap, and to make it worse you only have very limited formating options. Ive been looking around on the net and cant seem to find any ways to create nice ones. I found a couple of programs that can be used to create real fancy buttons for VB apps but surely they wont work in a vba script... will they?

Anyone got any ideas?
Can i use vba to program the look of the button?


Oh and yes i tried images but you get that horrid "make it look like its standing out of the page" border around it still and that looks worse than a standard button

cheers


kieran

[lucas]

For general appearance sake I try to avoid buttons on a spreadsheet alltogether.

You might find a popup menu or a file menu to be an interesting alternative...

See here for one simple example.
http://vbaexpress.com/kb/getarticle.php?kb_id=921

There are many others available to check out by searching the forum...

I have never experimented with dressing up buttons so I can't really help you directly with your question.......sorry.

You can use shapes, pictures, etc. on a sheet and assign macro's to them....just right click on the shape and select "assign macro"

[david000]

I'm sorry, I did not notice that this post was solved.

Anyway, I like buttons. You asked if you can use VBA to program buttons so I thought an example would be good. Buttons can really be anything that you can right click and assign a macro to, even text boxes and pictures.

Sub ColorShape()
Dim s As shape
For Each s In Sheet1.Shapes
   s.Fill.ForeColor.SchemeColor = Int((56 - 1 + 1) * Rnd + 1)
Next s
End Sub

[xluser2007]

I'm sorry, I did not notice that this post was solved.

Anyway, I like buttons. You asked if you can use VBA to program buttons so I thought an example would be good. Buttons can really be anything that you can right click and assign a macro to, even text boxes and pictures.

Sub ColorShape()
Dim s As shape
For Each s In Sheet1.Shapes
   s.Fill.ForeColor.SchemeColor = Int((56 - 1 + 1) * Rnd + 1)
Next s
End Sub

David,

This is a really great example - a nice use of buttons. I can see some nice applications for this, e.g. run one macro successfully through one button, and have the next sequential macro button (or shape) highlighted etc.

Just wondering, how did you get the Form Button to look like that the design to look like a button, the default is a simple rectangle (I know we can colour it etc, but couldn't understand how to change the shape)?

regards

[JKwan]

Those are not Form Button. They are "AutoShapes". So, if you bring up the AutoShapes tool bar, the shape is under Basic Shaps Row 4 Col 3 is the shape, they just assign the macro to the shape to execute the Color Change

[xluser2007]

Those are not Form Button. They are "AutoShapes". So, if you bring up the AutoShapes tool bar, the shape is under Basic Shaps Row 4 Col 3 is the shape, they just assign the macro to the shape to execute the Color Change

JKwan, thanks for the explanation. I realise that you can Assign macros to any shape, so this makes enhancing the Excel Front End really convenient.

regards,