MrExcel.com - Your One Stop for Excel Tips & Solutions

Print Range Decision - Command Button on UserForm

Posted by Jim on January 15, 2001 5:05 PM

Please Help and Thank you!
A Userform2 contains a Command Button.
The Userform2 is activated from the Worksheet by many users with authority to print their own sheet of data.

I have one Print Command Button on a UserForm;
There are multiple sheets in the Shared workbook and each sheet has a different range for printing.
Depending on the Active Sheet the User is on, I want to specify the Range for the active sheet only.
On the click of the command Button, I would like Visual basic to determine what sheet I am ON and what the range is that needs to be printed for the active sheet.(This part can be hardcoded as it will not grow or Shrink).

Example: User 1 is on Sheet 1 and wants to print data: The Range is A1:C10 that needs to be printed
User 2 is on Sheet 2 and wants to print data: The Range is A1:G20 that needs to be printed.

Thank you.
Jim

Posted by Dave Hawley on January 17, 2001 3:16 AM


Hi Jim

You would probably need Select Case for this. I would also advise to use the sheets Code Names as these do not change unless done so via VBA. You can see your sheets code names by looking in the Project Explorer (Ctrl+R).

Private Sub CommandButton1_Click()
Select Case ActiveSheet.CodeName
Case Sheet1
Sheet1.PageSetup.PrintArea = "$D$1:$F$10"
Sheet1.PrintOut
Case Sheet2
Sheet1.PageSetup.PrintArea = "$A$1:$H$10"
Sheet1.PrintOut
End Select
End Sub


Simple add more cases as needed. Hope this helps

Dave


  • OzGrid Business Applications

Posted by Jim on January 17, 2001 7:05 AM

Hi Jim Select Case ActiveSheet.CodeName Case Sheet1 Sheet1.PageSetup.PrintArea = "$D$1:$F$10" Sheet1.PrintOut Case Sheet2 Sheet1.PageSetup.PrintArea = "$A$1:$H$10" Sheet1.PrintOut End Select End Sub

Simple add more cases as needed. Hope this helps

Dave,
Thank you so much for the Code. it works great!.
Is there a way for VB to identify the Range for me to the bottom of the actual data. The Bottom of the Sheet on Row 400 I have some recap data, so I need to determine the maximum cell from A1 to MaxColumn Row that is less than 400. Example: Sheet1 range is A1 thru AE18
Sheet 2 range is A1 thru AE 198.
I was wanting to leave a blank line between the last row of data and the total Line so for the above example the total row is 200.
Can you do a Max row less than 400 + 2 to get the Total line in the Print Range????

Thanks again for the workaround answer to the Print Decisions. Sincerely, Jim