Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Nov 2005
    Location
    Monkton, Maryland, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Command Buttons (Excel 2003)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Command Buttons (Excel 2003)

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

  3. #3
    2 Star Lounger
    Join Date
    Nov 2005
    Location
    Monkton, Maryland, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Buttons (Excel 2003)

    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!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Command Buttons (Excel 2003)

    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:
    <code>
    ActiveSheet.PrintOut
    </code>
    - The so-called event procedure should now look like this:
    <code>
    Private Sub CommandButton1_Click()
    ActiveSheet.PrintOut
    End Sub
    </code>
    - 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:
    <code>
    ActiveWorkbook.SendMail Recipients:="someone@somewhere.com", Subject:="Invoice"
    </code>
    - 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:
    <code>
    Private Sub CommandButton2_Click()
    ActiveWorkbook.SendMail Recipients:="someone@somewhere.com", Subject:="Invoice"
    End Sub
    </code>
    - 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.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2005
    Location
    Monkton, Maryland, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Buttons (Excel 2003)

    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?
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Command Buttons (Excel 2003)

    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.

  7. #7
    2 Star Lounger
    Join Date
    Nov 2005
    Location
    Monkton, Maryland, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Buttons (Excel 2003)

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

    Carol

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •