CODEWITHSUNDEEP
excelvba
joekeny
joekeny

Reputation: 15

Macro to set print area from user input

Here is the code that I am using right now.

Sub PrintArea()
Dim arange
    Rows = InputBox("How many Rows do you want to print (Between 1 and 48)?", "Row Selection")
.PageSetup.PrintArea = .Range(A, B, C).Rows
End Sub

When I try to run the macro I get "Compile Error: Invalid or unqualified reference" and it highlights the .Range in my code.

I want the print area to always be columns A-C, but the rows will vary between 2-48 depending on what I am printing.

Edit: Stepping through the code, it stops at this line,

ActiveSheet.PageSetup.PrintArea = .Range(A:C, "arange")

and gives me a syntax error.

Upvotes: 0

Views: 383

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57753

It should be something like this:

Option Explicit

Sub SetMyPrintArea()
    Dim UserInput As Variant

    Do
        UserInput = Application.InputBox(Prompt:="How many Rows do you want to print (Between 1 and 48)?", Title:="Row Selection", Type:=1)
        If VarType(UserInput) = vbBoolean And UserInput = False Then Exit Sub
        DoEvents
    Loop While UserInput < 2 Or UserInput > 48

    Worksheets("Sheet1").PageSetup.PrintArea = "A1:C" & UserInput
End Sub

  • Note that the Do … Loop While UserInput < 2 Or UserInput > 48 forces the input box to appear again until the user put in a value between 2 … 48.

  • This If VarType(UserInput) = vbBoolean And UserInput = False Then Exit Sub is to check if the user pressed the cancel button and then to abort.

  • Don't name your procedure PrintArea because this is already used by Excel PageSetup.PrintArea and can easily confuse.

Upvotes: 1

Related Questions