Cmnd Bttn to show print dialog box and print hidden sheet

[WombatTrax]

I have a workbook that contains 1 visible sheet and 3 hidden sheets. Each hidden sheet has a form associated with it that inputs data to cells on it's associated sheet. I also have a print(command) button on each form. The sheet needs to be printed twice; one to a PDF file, and one to a printer.

I would like the user to be able to click the "Print" button and have the print dialog box show so that they can select the proper printer they need, and then print the hidden sheet. I have been able to print the hidden sheet directly to the default printer, and I have been able to show the print dialog box, but I have not been able to have both instances happen together. Any help would be extremely appreciated. I have searched for a solution to this problem, but have come up empty.

 

[BIGTONE559]

I have a workbook that contains 1 visible sheet and 3 hidden sheets. Each hidden sheet has a form associated with it that inputs data to cells on it's associated sheet. I also have a print(command) button on each form. The sheet needs to be printed twice; one to a PDF file, and one to a printer.

I would like the user to be able to click the "Print" button and have the print dialog box show so that they can select the proper printer they need, and then print the hidden sheet. I have been able to print the hidden sheet directly to the default printer, and I have been able to show the print dialog box, but I have not been able to have both instances happen together. Any help would be extremely appreciated. I have searched for a solution to this problem, but have come up empty.

try using the application.Dialogs(xlDialogPrinterSetup).Show

method. this will allow user to select printer.


let me know if that helps

 

[WombatTrax]

Thank you for the quick response. I have tried that method and it does work. The problem I am facing is that I need to have just the hidden sheet associated with the form print. I can only get the visible sheet to print at this point. I have tried several arrangements of code to make this work, but have failed each time. Here is the code I have right now:

Private Sub CommandButton1_Click()
Dim bresponse As Boolean
bresponse = Application.Dialogs(xlDialogPrinterSetup).Show
If bresponse = False Then
MsgBox "User Cancled"
Exit Sub
End If
ActiveWorkbook.Sheets("CN").PrintOut
End Sub

This brings up the dialog box that lets them choose from any printer on the network, but I still can't get the specific hidden sheet to print. Do I need to add code to make the sheet visible before printing and then hide it again after printing?

 

[BIGTONE559]

is "CN" the hidden sheet?

Thank you for the quick response. I have tried that method and it does work. The problem I am facing is that I need to have just the hidden sheet associated with the form print. I can only get the visible sheet to print at this point. I have tried several arrangements of code to make this work, but have failed each time. Here is the code I have right now:

Private Sub CommandButton1_Click()
Dim bresponse As Boolean
bresponse = Application.Dialogs(xlDialogPrinterSetup).Show
If bresponse = False Then
MsgBox "User Cancled"
Exit Sub
End If
ActiveWorkbook.Sheets("CN").PrintOut
End Sub

This brings up the dialog box that lets them choose from any printer on the network, but I still can't get the specific hidden sheet to print. Do I need to add code to make the sheet visible before printing and then hide it again after printing?

 

[WombatTrax]

is "CN" the hidden sheet?

Yes it is

 

[BIGTONE559]

sounds like you'll need to do the following:

Private Sub CommandButton1_Click()
Dim bresponse As Boolean
bresponse = Application.Dialogs(xlDialogPrinterSetup).Show
If bresponse = False Then
    MsgBox "User Cancled"
    Exit Sub
End If
application.screenupdating=false
activeworkbook.worksheets("CN").visible=true
activeworkbook.worksheets("CN").printout

Activeworkbook.worksheets("CN").visible=xlveryhidden


End Sub

hope that helps

 

[BIGTONE559]

you may also want to store the original printer so that you can return to it once your done. . .

for example

dim strPrinter as String

strprinter=application.activeprinter

' ' ' ' enter code here to print the hidden sheet and dialog box


'at the end you will have the following line
application.activeprinter=strprinter

 

[WombatTrax]

sounds like you'll need to do the following:

Private Sub CommandButton1_Click()
Dim bresponse As Boolean
bresponse = Application.Dialogs(xlDialogPrinterSetup).Show
If bresponse = False Then
    MsgBox "User Cancled"
    Exit Sub
End If
application.screenupdating=false
activeworkbook.worksheets("CN").visible=true
activeworkbook.worksheets("CN").printout

Activeworkbook.worksheets("CN").visible=xlveryhidden


End Sub

hope that helps

Thank you!!! This worked perfectly!