VB script to notify/popup

Solved/Closed
Sammysen Posts 2 Registration date Tuesday August 5, 2014 Status Member Last seen August 5, 2014 - Aug 5, 2014 at 02:23 AM
 Sammysen - Aug 14, 2014 at 09:38 PM
I was having a difficulty in notifying my co-worker to print all the sheets on a workbook. I a thinking if it's possible to have an pop-up message to be appeared after one sheet is printed requesting to print the next (or other) sheet.

3 replies

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 523
Aug 5, 2014 at 10:51 AM
Hi Sammysen,

Have you checked all the print options? There should be something like print Active sheet, all selected sheets, entire workbook.

Have you tried selecting multiple sheets (while holding Ctrl) and then print?

Which Excel version are you using (like 2003 or 2010 etc.)?

Best regards,
Trowa
0
Sammysen Posts 2 Registration date Tuesday August 5, 2014 Status Member Last seen August 5, 2014
Aug 5, 2014 at 09:30 PM
Trowa,

I'm using Excel 2007.

Clarification to my inquiry above, it is the 'work sheet' that sometimes forgot to be printed and not a 'sheet' in a 'work sheet'.

Sorry for the confusion.

Sammysen
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 523
Aug 7, 2014 at 10:07 AM
Hi Sammysen,

A sheet is the same as a worksheet.
An Excel file / Workbook contains 1 or more sheets / worksheets.

If you want to print multiple workbook; try selecting the ones you want to print > right-click > select print.

Is that what you wanted to do?

Best regards,
Trowa
0
Trowa,

Yes, I practically know that process in particular.

In our setup, we have limitation that prohibit to proceed with selecting all the active sheets, thus we end up printing our requirements one by one. Now the problem is because of this limitation, we sometimes forget to print the other required sheet.

Having said that, I would like to ask if we can add VBA SCRIPT that may notify the to print the the other sheet(s) after we print the other one.


Thank you for your assistance.

Sammysen
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 523
Aug 11, 2014 at 11:54 AM
Hi Sammysen,

That is a some strange limitation you have there.

See if this code works for you. It will loop through all the sheets in the workbook you run the code from.

In case you don't know how to implement the code / run the code:
- Hit Alt+F11 to open Microsoft Visual Basic window.
- Top menu > Insert > Module
- Paste the code in the big white field
- Close Microsoft Visual Basic window.
- Back to Excel hit Alt+F8 to display all macro's
- Double click RunMe to run the code and print all sheets.


And here is the code:
Sub RunMe()
Dim ws As Worksheet

For Each ws In Worksheets
    ws.PrintOut
Next ws

End Sub


Best regards,
Trowa
0
Trowa,

The script works fine.

By the way, is it possible to add a pop up message noting to check print status of other worksheet?

It will be a great help if such message will appear.

Thank you so much for your assistance.

Sammysen
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 523
Aug 12, 2014 at 11:23 AM
Hi Sammysen,

What do you mean by "check print status"?

Do you want a message notifying you which sheet is being printed like:
"Hit OK to print Sheet1"
You hit OK, Sheet1 will be printed and then the next message appears:
"Hit OK to print Sheet2"
etc.

Best regards,
Trowa
0
Trowa,

Yes that is likely what I need. But a little modification on the message instead of "Hit OK to print Sheet1", can we make it "Kindly Print Sheet 2" instead after Sheet 1 is printed?

Thank you so much for your assistance.


Sammysen
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 523
Aug 14, 2014 at 10:16 AM
Sure thing Sammysen.

You can alter the text into anything you want, but here is the code with the requested text:
Sub RunMe()
Dim ws As Worksheet

For Each ws In Worksheets
    MsgBox "Kindly Print " & ws.Name
    ws.PrintOut
Next ws

End Sub


Best regards,
Trowa
0