Command Buttons (Excel 2003)

Topic

New Reply

I have a client who has sent me an alpha file of what he is looking for, but I have never created command buttons and do not know how to go about it. He wants two command buttons at the bottom of his spreadsheet; if you click one it will print the spreadsheet and if you click the other button it will send the spreadsheet via Outlook e-mail. Any help would be greatly appreciated.

Reply | Quote

Replies

“At the bottom of his spreadsheet” is a vague notion – if you scroll up or down, the command buttons will move around.

What does the client want the second button to do – open a new e-mail with the workbook attached (so that the user can fill in the recipient etc.), or send the workbook to a fixed recipient without intervention by the user?

Reply | Quote

Hi Hans,
Thanks for your speedy response. The client has sent me an alpha file of what he wants. Basically it looks to be a spreadsheet with four columns, the first column is labeled Document and below that there are several documents listed with a check box next to them. The next column is labeled Quantity. The client wants to be able to click the checkbox under Document and that would enable him to enter a quantity in the next column and the third column is labeled Unit price and the fourth column is labeled Line price. There are thirteen documents listed with check boxes and a cell at the end of the Line price column for the Total Project Cost.

Beneath all of that (two rows down) is a cell for the date, one for a P.O./Reference and a command button to click to print the document and the other command button to click and send the document to a fixed e-mail address. This may be a bit over my head, but I would like to give it a try if you can help me out.

Thank you so much for your time and effort!

Reply | Quote

You could do the following:
– Select View | Toolbars | Control Toolbox. You’ll see a new toolbar.
– Click on the Command Button button on the Control Toolbox.
– Click on the worksheet where you want the first command button.
– Excel will automatically turn on Design Mode.
– Double click the new command button.
– This will activate the Visual Basic Editor and create the first and last lines of the code to be executed by the button.
– Press Tab, then type the following instruction:

ActiveSheet.PrintOut

– The so-called event procedure should now look like this:

Private Sub CommandButton1_Click()
ActiveSheet.PrintOut
End Sub

– On the left hand side, you should see the Properties pane for the command button. If not, select View | Properties.
– Change the Caption property to Print Sheet or something similar.
– Press Alt+F11 to return to Excel.

– Click on the Command Button button on the Control Toolbox again.
– Click on the worksheet where you want the first command button.
– Double click the new command button.
– Press Tab, then type the following instruction:

ActiveWorkbook.SendMail Recipients:="someone@somewhere.com", Subject:="Invoice"

– Change someone@somewhere.com to the correct e-mail address, and Invoice to the appropriate subject.
– Note: you may initially want to use your own e-mail address for testing purposes.
– The so-called event procedure should now look like this:

Private Sub CommandButton2_Click()
ActiveWorkbook.SendMail Recipients:="someone@somewhere.com", Subject:="Invoice"
End Sub

– Change the Caption property to E-mail workbook or something similar.
– Press Alt+F11 to return to Excel.
– Click the first button on the Control Toolbox to turn off Design Mode.
– The buttons should now work.

Reply | Quote

Hi Hans,

I followed your instructions but I guess I am being obtuse because when I click the command buttons nothing happens at all. I am attaching what I did for you to view. Can you tell me what I did wrong?

Reply | Quote

The buttons do work when I try them.

1) Make sure that you have turned off design mode – the first button on the Control Toolbox should *not* be depressed.
2) Make sure that you have enabled macros.

Reply | Quote

You were absolutely right Hans! I had not exited design mode. It now works fine! Thank you so much once again!

Carol

Reply | Quote