Print Range User Selected

Question

I was hoping to create a module that would basically operate like so:

1. Define 4 or 5 print ranges;
2. Prompt a user an input box;
3. Allow the user to select, from a drop down in that input box, the range they wish to print;
4. After selecting the range, they hit OK, and are prompted by a "are you sure?" box to prevent mistaken clicks.

I'm fairly lost on this and I honestly feel like the code I've been writing will be less help than just articulating the problem.

I have had it work by the user defining the range (manually selecting the columns they wish to print), but that's not what I'm looking for.

One step further, would it be possible to allow for the customization of the print format (landscape vs portrait, and paper type) even further?

Thanks so much for the help in advance, I'll do my best to answer questions and provide samples of the code I referenced above (just a prompt that allows you to select the columns. I need it to be a defined range, by name, range1=a2:c14 or something like that, because the end user is not a great excel user.

See below:

Sub SelectPrintArea()
Dim PrintThis As Range
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"
ActiveSheet.PrintPreview
End Sub

As a follow-up:

Assume the document has hidden sections, would it be able to unhide those sections if they are part of a user defined range (like if it was part of a grouping). Would this work on a protected document?

Answer 1

In order to present a list of names to the user, you'll need a UserForm similar to this:

The code behind that form would look like the below. I've used Print preview in favor of an "Are you sure" message, because it's a more elegant UX.

Option Explicit

Private Sub UserForm_Initialize()

  With Me.cboPrintAreas
    .MatchRequired = True
    'Add named ranges to the listbox
    .AddItem "Report_1"
    .AddItem "Report_2"
    .AddItem "Report_3"
    .AddItem "Report_4"
    .AddItem "Report_5"

    'Set the default report
    .Value = "Report_1"
  End With

End Sub

Private Sub btnCancel_Click()
  Unload Me
End Sub

Private Sub btnPrint_Click()

    Dim rng As Range

    Set rng = Range(Me.cboPrintAreas.Value)

    With rng.Worksheet
      'Do a crude assignment of paper orientation
      If rng.Height > rng.Width Then
      .PageSetup.Orientation = xlPortrait
      Else
        .PageSetup.Orientation = xlLandscape
      End If
      .PageSetup.PrintArea = rng.Address
      Me.Hide
      .PrintOut Preview:=True, IgnorePrintAreas:=False
      Unload Me
    End With

End Sub

And you'd display the form from a Standard module with code like:

Sub test()

  UserForm1.Show

End Sub

If you want to unhide hidden rows/columns, you'll need to ensure you have the range's sheet suitably unprotected.