Print area User selected by VBA Excel
I was hoping to create a module that would basically work like this:
- Define 4 or 5 print ranges;
- Prompt the user for an input field;
- Allow the user to select the range they want to print from the drop-down list in this input field;
- After selecting the range, click "OK" and you will be prompted "are you sure?" to prevent erroneous clicks.
I am quite lost on this and I honestly feel that the code I was writing would be less than just formulating the problem.
I had a job with a user defining a range (manually selecting the columns they want to print), but that's not what I'm looking for.
One more step, is it possible to further customize the print format setting (landscape versus portrait and paper type)?
Thanks a lot for the help in advance, I will do my best to answer the questions and provide the code samples I referred to above (just a prompt that allows you to select columns. I need it to be a specific range, by name, range1=a2:c14
or whatever- then 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
Continuing:
Suppose the document has hidden sections, would it be able to display those sections if they are part of a user-defined range (for example, if it was part of a grouping). Will this work on a protected document?
source to share
To present a list of names to a user, you need a UserForm like this:
The code for this form will look like this. I used Print Preview in favor of the "Are you sure" message because it has 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 can display the form from a standard module with code like:
Sub test()
UserForm1.Show
End Sub
If you want to display hidden rows / columns, you need to make sure the range sheet is not protected.
source to share