Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Thanked 0 Times in 0 Posts

    Command Button (2000)

    I have a pivot table that I created with Access. When I Edit the Pivot table it goes to Excel. When I am done editing the pivot table, I would like to have a command button on the spreadsheet that can be clicked on to close Excel and return to Access. Instead of clicking on the file menu and choosing "Close and return to Access", I would like a command button on the spreadsheet to do this. Does anyone know how I would do this or if I can do this? Thanks in advance for your help.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    New York, New York, Lebanon
    Thanked 1 Time in 1 Post

    Re: Command Button (2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Linda

    The button in the MS-Excel workbook, or on a toolbar could issue the VBA command:

    <font color=blue>Application.Quit.</font color=blue>

    This will quite the application. It does have some draw backs, but if you simply want to let Excel go, it will do the job.

    Alternativly, you could hide Excel and bring the Access application to life by making it the active application.

    Check the Close and Quite in the Online Help, and see what draw backs they have.

    Hope this helps.

    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Command Button (2000)

    The simple ways to close:
    or even
    just pressing the "X" in the upper right hand corner

    If you want a command button, You would have to write the code to create the command button on the sheet (from access) when you create the privot table.

    Access would have to open excel (eg with the name xlApp), create the workbook, add the pivot, then add some code like this to create a button (change the size and location as desired, as well as the macro it calls)
    <pre>Sub CreateExitButton()
    Dim MyExitButton As Button
    Set MyExitButton = ActiveSheet.Buttons.Add(15, 59.25, 61.5, 35.25)
    MyExitButton.OnAction = "MyExit"
    End Sub</pre>

    It would then have to create the routine to exit excel, something like this (change the xlApp name to whatever object you stored it as)
    <pre>Sub myExit()
    End Sub</pre>

    To add a procedure to an excel sheet you will have to use code. Chip Pearson has some code here under the heading "Adding A Procedure To A Module " if you need some example code.

    How you would all do that from Access I have a "vague idea" but I think that end is more of an access question.
    Hope this helps,

Posting Permissions

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