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
' 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