Use a Cell Value to define the From and To option in PrintOut VBA

Question

Hello folks,

I've little experience with VBA so trying to find a simple solution for a problem I've encountered regarding PrintOut

I'm using a simple macro a the PrintOut function to print multiple sheets on a single click. However I'd like the user to be able to define the pages on some of these sheets which need printing. Ideally I'd like a check box (e.g. the user could choose to print pages 1, 2, 3 and 4 or 4, 5 and 6 and so on....) or the ability for the user to enter a value in a cell for the page range before hitting the button that triggers the macro and print out.

Can anyone help as to how this could be done?

Thanks in advance

Answer 1

Hi,

You can allow users to specify the pages they want to print in Excel by using an InputBox or a UserForm to enter the page range.

Sub PrintWithPageRange()     Dim PageRange As String     Dim ws As Worksheet          ' Prompt the user for the page range     PageRange = InputBox("Enter the page range to print (e.g., 1-3):", "Page Range")          ' Check if the user canceled the InputBox     If PageRange = "" Then         Exit Sub     End If          ' Loop through the worksheets you want to print     For Each ws In ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))         ' Print the specified page range         ws.PageSetup.PrintArea = PageRange         ws.PrintOut     Next ws End Sub 

Then create a UserForm and write VBA code to show the UserForm when the macro is executed, and use the values entered in the UserForm to specify the page range for printing.

Sub PrintWithUserForm()     ' Show the UserForm     UserForm1.Show End Sub  Sub PrintSelectedPages(PageRange As String)     Dim ws As Worksheet     Dim PrintRange As String          ' Loop through the worksheets you want to print     For Each ws In ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))         ' Set the print range         PrintRange = PageRange                  ' Apply the print range to the worksheet         ws.PageSetup.PrintArea = PrintRange                  ' Print the worksheet         ws.PrintOut     Next ws End Sub 

In this code, UserForm1 is the UserForm you've created. The PrintWithUserForm macro shows the UserForm, and the PrintSelectedPages macro prints the specified page range.

Best Regards.