+ Reply to Thread
Results 1 to 7 of 7

VBA code to create command button. Print all sheets then delete command button.

  1. #1
    Forum Contributor
    Join Date
    03-09-2012
    Location
    Isle of Man
    MS-Off Ver
    Excel 2010
    Posts
    118

    VBA code to create command button. Print all sheets then delete command button.

    Hi to everyone,

    I have a macro that collates data etc. In a number of sheets in a workbook. My intention was that the final piece of code would be to select all the sheets that needed printing and then print them.

    Unfortunately some of the sheets will need to have comments manually inserted by the user before printing. I was hoping that as part of my macro a command button would appear in Sheet1 after the last row of data in column A.

    When pressed the following code would run printing the sheets and then the command button would disappear. Is this possible?

    Please Login or Register  to view this content.
    Any help you could give me would be much appreciated.

  2. #2
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: VBA code to create command button. Print all sheets then delete command button.

    Hi Declamatory,

    Great question. See the following two Macros:
    a. CreateCommandButtonAtTheBottomOfSheet1(). This macro creates the CommandButton at the bottom of 'Sheet1' and can be called from anywhere in your existing code.
    b. PrintSheetsAndRemoveCommandButton(). This macro is the Shell for printing when the CommandButton is 'Left Clicked'. You have to add your print code to this routine, or have your code in another Macro that is called from this routine.

    See the attached file which contains the following code in an Ordinary Code Module:
    Please Login or Register  to view this content.

    It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:
    Please Login or Register  to view this content.
    This option requires all variables to be declared and will give a compiler error for undeclared variables.

    Lewis

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,292

    Re: VBA code to create command button. Print all sheets then delete command button.

    Do you need to create the button each time?

    Why not create it manually and hide it until your code is completed?

    The button could be hidden again at the end of the code for printing.
    If posting code please use code tags, see here.

  4. #4
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: VBA code to create command button. Print all sheets then delete command button.

    Quote Originally Posted by Norie View Post
    Why not create it manually and hide it until your code is completed?
    Excellent suggestion Norie. The downside of your suggestion is the CommandButton name must be known to make the CommandButton visible. The CommandButton name must also be unique.

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,292

    Re: VBA code to create command button. Print all sheets then delete command button.

    Why would that be a problem?

  6. #6
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: VBA code to create command button. Print all sheets then delete command button.

    Quote Originally Posted by Norie View Post
    Why would that be a problem?
    If accidentally deleted, the CommandButton would have to be recreated with exactly the same name. That is easy for someone with advanced skills, but can be difficult for someone who is less experienced.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,292

    Re: VBA code to create command button. Print all sheets then delete command button.

    If there is only ever one command button the name doesn't need to be used.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Userform: Code for DELETE on a command button
    By Gal403 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-25-2014, 01:28 AM
  2. Create a command button with code with a command button
    By jakara in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2013, 01:28 PM
  3. Command Button to Print Grouped Sheets
    By KEMelton in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-30-2012, 01:21 PM
  4. [SOLVED] vba code, command bar, command bar button, one button works but not two
    By amazingg64 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2012, 09:55 AM
  5. [SOLVED] Print Setup VBA code behind a command button
    By boldcode in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-08-2011, 02:04 PM
  6. how can a create a print command button in excel
    By velcrowe11 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-12-2006, 11:39 AM
  7. [SOLVED] command button:Can i use a command button to open diffrrent sheets
    By Hellboy in forum Excel General
    Replies: 0
    Last Post: 11-03-2005, 11:00 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1