Set print area from activex command button

[jgoulart]

I have a command button on a sheet that launches a userform that gives you 2 options of a print range to set. I can't get the code to run correctly when I launch my userform from the activex command button. The code to set the print area runs fine if I run the userform from the VBA editor. What do I need to do to make it work correctly from the active x button. Here is some of the code:

' this is the code for the activex command button in the sheet code
Private Sub Printexe_Click()
Printoption.Show
Printoption.reg = True
End Sub

'this is the code of the userform accept button

Private Sub CommandButton1_Click()
Printoption.Hide
Unload Printoption
If Printoption.vol = True Then
printvol
Else
Printreg
End If

End Sub

' this is the sub that defines the print range

Sub Printreg()
Dim myrange As String
ThisWorkbook.Worksheets("Invoice").Activate
Sheets("Invoice").Activate
myrange = ThisWorkbook.Worksheets("Invoice").Range("A5:L48").Address
Worksheets("invoice").PageSetup.PrintArea = myrange
Application.Dialogs(xlDialogPrint).Show
End Sub

Sub printvol()
Dim myrange As String
ThisWorkbook.Worksheets("Invoice").Activate
Sheets("Invoice").Activate
myrange = ThisWorkbook.Worksheets("Invoice").Range("A5:M48").Address
ActiveSheet.PageSetup.PrintArea = myrange
Application.Dialogs(xlDialogPrint).Show
End Sub

I'm lost as what to try next. Is there a way to make the activex command button public(I'm guessing that is where my problem is now)? Any advice would be much appreciated.

John

 

[Russell Hauf]

You just need to reverse the lines in your worksheet button.

Printoption.reg = True
Printoption.Show

Also, you don't need to hide your form before unloading it. But for clarity, you may want to hide the form, run your print function, then unload. But it obviously works either way.

Hope this helps,

Russell

 

[jgoulart]

I switched the order to:

printoption.reg = true
printoption.show


and I still get the same problem. My userform comes up and when I excecute the "command button" on it it fails to:

"Run time error 1004 unable to set print area property of the pagesetup class. I still think it is because the activex command button is keeping it in private control(I'm not sure if I'm wording it right as I am fairly new to vba) If I run the userform from the vba editor everything runs fine.

 

[Russell Hauf]

Can you send me the workbook? I can take a look and get back to you...