[SOLVED] PageSetup properties using variables

froodo · #1 07-02-2009, 08:45 PM

Hi

I am getting error:
Run-time error '1004': Unable to set the Orientation property of the PageSetup class

I am using the following code:

Code:

Dim StrOrientation As String
Application.Goto Reference:="Orientation"
StrOrientation = Activecell.Value 'where reference is either xlPortrait of xlLandscape


    With ActiveSheet.PageSetup
        
         .Orientation = StrOrientation 

    End With

Trebor76 · #2 07-02-2009, 09:22 PM

Hi froodo,

Welcome to the forum.

I may have a solution, but before I can suggest it you'll need to PM a moderator to have your code wrapped as your thread currently breaks Rule 3 of the forum (see here for more info).

Once this is done I'll gladly post my suggested solution.

Regards,

Robert

Trebor76 · #3 07-02-2009, 10:11 PM

Hi froodo,

Thanks for that.

As you've created a named range you don't have to assign its value to variable, i.e.

Code:

Sub Macro1()
           
    With ActiveSheet.PageSetup
    
        .Orientation = Range("Orientation").Value
    
    End With

End Sub

froodo · #4 07-02-2009, 10:26 PM

Thanks for your reply and help with forum etiquette, I still am receiving the same error when I use either of your methods. Any other ideas what may be wrong?

Thanks

Trebor76 · #5 07-02-2009, 11:31 PM

Hi froodo,

The orientation options (i.e. Portrait or Landscape) must be returned as integers (1 or 2 respectively).

Have a look at the following which checks the value in the "Orientation" named range to assign one of the two values to the "intPrintOrientation" accordingly. I've also included some notes to hopefully explain things:

Code:

Sub Macro3()

    'Declare variable
    Dim intPrintOrientation As Integer
    
    'If any part of the named range "Orientation" has _
    'Portrait' in it then...
    If InStr(Range("Orientation").Value, "Portrait") > 0 Then
        '...assign 1 (integer value for portrait) to the _
        'intPrintOrientation' variable.
        intPrintOrientation = 1
    'Else...
    Else
        '...assign 2 (integer value for landscape) to the _
        'intPrintOrientation' variable (this will therefore _
        be the default).
        intPrintOrientation = 2
    End If

    With ActiveSheet
        .PageSetup.Orientation = intPrintOrientation
        .PrintOut Copies:=1
    End With
        
End Sub

froodo · #6 07-08-2009, 09:25 PM

Thanks, that did the trick. I was thinking that the xlLandscape and xlPortrait would of have to of been used.

Cheers

Welcome to the Excel Forum

Forum Statistics