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.

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

Originally Posted by Norie

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.

Originally Posted by Norie

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.