My Print Selection

[GomaPile]

Good morning all,

Found this vba code on Google, and it works okay. Kindly asking for someone's help please if you can add more coding... if the User forgets or doesn't Select the Print Range or if the User clicks the OK button.

Apparently, its given me a pop up error. What I prefer to see is a simple message pop up box saying something like: "You have not selected a print range, please try again". Or if the User chooses to click the Cancel button it does nothing and Exit Sub.

Also, whenever the User selects a range of cells it fits/maximise to the size A3 paper in colour, in print preview, before printing.

Anyways, if you see anything else that can make the vba work better or help the User or speed up... please add more, thanks.

Sub SelectPrintArea()

Dim PrintThis As Range
On Error GoTo ErrorHandler

    ActiveSheet.PageSetup.PrintArea = ""
Set PrintThis = Application.InputBox _
    (Prompt:="Select the Print Range", Title:="Select", Type:=8)
    PrintThis.Select
    Selection.Name = "NewPrint"
    ActiveSheet.PageSetup.PrintArea = "NewPrint"
  
With ActiveSheet.PageSetup
    .PrintArea = Selection.Address
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With
  
ActiveSheet.PrintPreview
  
Exit Sub
ErrorHandler:
    'MsgBox "If error, then exit the Sub"
Exit Sub

End Sub

Regards
NASA2 (GomaPile)

 

[Micron]

IMO when user interaction raises an application error then the way to deal with it is to use an error handler, and you already have that. You need to handle that error and I think this is how I'd do it in that case:
I'd set the range that they select to a range object. If they cancel it will raise error 424. If they continually click the OK button, nothing will happen. Off the cuff -

'beginning of your sub
Dim InputRng As Range
'other code here
Set InputRng = Application.InputBox("COPY RANGE:", Type:=8) 'if user cancels, 424 error is raised. Execution goes to errHandler line
'more code here

exitHere:
Application.DisplayAlerts = True 'reset application settings if they were disabled. This is one example.
Set InputRng = Nothing 'set object variables to Nothing. Not everyone agrees.
Set ReplaceRng = Nothing
Exit Sub 'exit here when no error, otherwise code will continue and cause an error.

errHandler:
If Err.Number <> 424 Then '424 is when user cancels inputbox.
'If not 424, error message will appear; otherwise it just goes to exitHere line. 
'You'd use an Else statement to do something other than just 'quit' the sub
     MsgBox "Error " & Err.Number & ": " & Err.Description
End If
Resume exitHere

End Sub

Sorry I don't know how to control the printer page setting; I would have to Google it. HTH.

 

[GomaPile]

Hey Micron,

Firstly, would like to say thanks for your assistance. Also, so sorry for my late reply too... yesterday was super busy at work.

Anyhow, tried adding your code though I couldn't figure it out what goes where or next in order for the vba to fully work.

When you have a moment is it possible can you show me what order the code goes, where I can just copy and paste straight into my excel file for testing.


Kind Regards,

 

[Micron]

Try this

Sub SelectPrintArea()
Dim PrintThis As Range

On Error GoTo ErrorHandler

ActiveSheet.PageSetup.PrintArea = ""
Set PrintThis = Application.InputBox _
    (prompt:="Select the Print Range", Title:="Select", Type:=8)

PrintThis.Select
Selection.Name = "NewPrint"
ActiveSheet.PageSetup.PrintArea = "NewPrint"
With ActiveSheet.PageSetup
    .PrintArea = Selection.Address
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With
ActiveSheet.PrintPreview

exitHere:
Set PrintThis = Nothing
Exit Sub

ErrorHandler:
If Err.Number <> 424 Then
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume exitHere
End If

End Sub

 

[GomaPile]

Hey mate, that coding seems to work too. Thanks for your support every bit help a lot!!

Nasa (GomaPile)