Set print area from activex command button
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Set print area from activex command button

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    John G
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    John G
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com