Print Button macro

[Colin Mannion]

HI

I am trying to put together a simple Print Button - hope someone can help.

I have a top sheet which is heavily graphic and coloured, and want to reduce the amount of toner being used when printed. The top sheet with the graphic is called Brand Dashboard, and there is a direct copy of that sheet - Table Print - which pulls through only the required data tables which have a print area of B142.

Have had a go (see code below), but the selection seems to get lost when the macro reaches the print preview dialogue, and not sure the DoEvents statement is having the desired effect of waiting until the user has edited the print dialogue for number of copies/name of printer before returning to the Brand dashboard sheet.

Can you tell me where I've gone wrong?

Thanks in advance.
Colin


Sub CommandButton1_Click()
' Select_Data_For_Printing Macro
'

Sheets("TABLE PRINT").Select
Sheets("TABLE PRINT").Range("B142").Select
Application.CommandBars.ExecuteMso ("PrintPreviewAndPrint")
DoEvents
Sheets("Brand Dashboard").Select
Sheets("Brand Dashboard").Range("a1").Select
End Sub


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[PCosta87]

Hi Colin,

Maybe doing it the other way around?:

Sub CommandButton1_Click()

Sheets("Brand Dashboard").Select
Sheets("Brand Dashboard").Range("a1").Select

Sheets("TABLE PRINT").Range("B1:P42").PrintOut preview:=True

End Sub

 

[Colin Mannion]

Hi there PCosta - how's you?

Thanks for that - have given it a try.

The issue seems to be that I need the user to be able to use the print dialogue box in order to choose their printer, and when this code runs, it goes from clicking the print button back to the Brand Dashboard sheet.

Does that make sense?

Regards
C

 

[PCosta87]

Hi,

I'm fine thank you how are you?

Yes, it does make sense... still haven't figured out a way to it with that dialog box though

 

[Colin Mannion]

All good here thank you - just back from a week in Austria which was great.

Appreciate the support.
C

 

[PCosta87]

Hi,

I found a workaround

In a module:

Sub CommandButton1_Click()
' Select_Data_For_Printing Macro
'

Sheets("TABLE PRINT").Select
Sheets("TABLE PRINT").Range("B1:p42").Select
Application.CommandBars.ExecuteMso ("PrintPreviewAndPrint")

End Sub

Sub Afterprinting()

Sheets("Brand Dashboard").Select
Sheets("Brand Dashboard").Range("a1").Select

End Sub

In "ThisWorkbook" object:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Application.OnTime Now, "Afterprinting"
End Sub

Try it out and let me know if that works for you.

Cheers

 

[Colin Mannion]

HI

Tried that but it leaves me in the Table Print tab - it could be I have inserted the code in the wrong place - can you clarify what you mean by in "ThisWorkbook" object - I have inserted it in the Table Print sheet code - is that what you mean?

When I run the Afterprinting routine independently it does return me to the Brand Dashboard page, so that looks okay on it's own.

Thanks again.
Colin

 

[Colin Mannion]

Just to be really clear on this PC - the two sheets are in the same workbook - does that affect the code?

Regards
C

 

[PCosta87]

HI

Tried that but it leaves me in the Table Print tab - it could be I have inserted the code in the wrong place - can you clarify what you mean by in "ThisWorkbook" object - I have inserted it in the Table Print sheet code - is that what you mean?

When I run the Afterprinting routine independently it does return me to the Brand Dashboard page, so that looks okay on it's own.

Thanks again.
Colin

Hi,

There should be a "ThisWorkbook" object similar to the "Table Print".

 

[Colin Mannion]

mmmmm... something still not working. have had a couple of goes at inputting the code but things seem to be getting worse!!!

Have attached a copy of the sheet for you to look at - I am sure it is very close - it is down to my lack of knowledge, not your expertise.

Appreciate the help.
C

 

[PCosta87]

mmmmm... something still not working. have had a couple of goes at inputting the code but things seem to be getting worse!!!

Have attached a copy of the sheet for you to look at - I am sure it is very close - it is down to my lack of knowledge, not your expertise.

Appreciate the help.
C

Done,

Just click the button and print... it should go to the other sheet afterwards:

You were close!

Let me know if there is anything else I can help with.

 

[Colin Mannion]

Top Man PC - appreciate that - nice way to end the week - have a great one!

C

 

[PCosta87]

Top Man PC - appreciate that - nice way to end the week - have a great one!

C

You are welcome have a great weekend!