PDA

View Full Version : SOLVED: Dynamic Printing Range



primaryteach
12-13-2008, 03:24 AM
I have a large file (up to 20000 rows) that will be populated with different levels of data at different times. I'm looking to have a macro that allows the user to choose their print area each time they want to print out, so defining a static range is not an option.

I have seen on different posts macros that try to detect where the last entry on the spreadsheet is and only print above that cell. However, this approach seems to have 2 flaws in that it has problems with cells with formulas and of course the user might not want all of the data printed selected by this method.

So what I would ideally like is an input box to appear when the macro runs, into which I can enter a range (select with mouse click and drag), then it prints out that range.

I realise the user could go to page setup and manually change the range from there, but I was hoping to automate the system a bit more.

Thanks for your help,

Simon

UK Teacher

Bob Phillips
12-13-2008, 04:48 AM
How about trapping the print event, and forcing the user to specify the range there?

primaryteach
12-13-2008, 04:58 AM
Thanks XLD for the reply. I'm a VBA newbie - what do you mean by 'trapping'. Sorry to be so dumb!

Bob Phillips
12-13-2008, 05:12 AM
I mean don't have a particular macro, but just let them use the stanadrd print, but intercept that print event and then run your custom code that makes them select a range.

primaryteach
12-13-2008, 05:36 AM
I have just found this code:
Sub setprintarea()


Dim MySelection As Range
On Error GoTo ErrHandler
Set MySelection = Application.InputBox(prompt:="Choose which data you want to print by clicking on cell B1, hold down the mouse button and drag until you have highlighted the desired data and click OK", Type:=8)

MySelection.Select
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1

ErrHandler: MsgBox "Printing cancelled."
End Sub

However, it seems to not work correctly. In Excel 2007 it will print out the desired range but still delivers the error message!? I also have Excel 2000, it seems to let me choose a range but then goes immediately to the error message without printing or adjusting the print area.

Any ideas how resolve these issues, particularly as I tend to use excel 2000 over 2007?

Thanks, Simon

Bob Phillips
12-13-2008, 06:04 AM
That is similar t the approach I was suggesting, but mine was to change to the print event.

lucas
12-13-2008, 07:59 AM
This seems to work for 2003:

Option Explicit
Sub setprintarea()
Dim MySelection As Range
Set MySelection = Application.InputBox(prompt:="Choose which data you want to print by clicking on cell B1, hold down the mouse button and drag until you have highlighted the desired data and click OK", Type:=8)
MySelection.Select
ActiveSheet.PageSetup.PrintArea = MySelection.Address
' ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveSheet.PrintPreview
End Sub

primaryteach
12-13-2008, 08:43 AM
Lucas I have tried the code in excel 2007 and it works - thanks. However my Excel 2000 throws an error straight away: 424 Object required. When running the debug it highlights this line:

Set MySelection = Application.InputBox(prompt:="Choose which data you want to print by clicking on cell B1, hold down the mouse button and drag until you have highlighted the desired data and click OK", Type:=8)

lucas
12-13-2008, 09:06 AM
Try this, I found it at mrExel in a discussion about ver. 2000:

Sub setprintarea()
Dim MySelection As Range
Set MySelection = Application.InputBox("Please select range...", "Select", , , , , , 8)
ActiveSheet.PageSetup.PrintArea = MySelection.Address
' ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveSheet.PrintPreview
End Sub

GTO
12-13-2008, 09:28 AM
Greetings to all,

Reference Lucas' (Hi Steve :-) code, when you "straight away," I believe you mean on the circumstance that the Cancel button is pressed. You could add error handling similar to the example you found to handle this. FYI as to the error... this is because when cancelling, no range is referred to, so Set throws an understandable hissy fit...

Before Bob made mention of catching the user in BeforePrint, I was cobbling this together while drifting off (zzzzzzzzzzzzzzzzzzz). It would still need the Printout added if the desire is to do it in one swoop, I just concentrated on setting the range.

Anyways, if of any use, it tacks in a temporary command at the top of the shortcut (R-Click) menu.

In a Standard Module:
Option Explicit
Public cmdRClk As CommandBarButton
Sub SetRangeAndOffer()
Dim rngWanted As Range
Dim strSel As String
strSel = Selection.Address

On Error Resume Next
Set rngWanted = Application.InputBox(prompt:="Please select the range you want to print", _
Default:=strSel, _
Title:="", _
Type:=8)
If Err.Number > 0 Then
Exit Sub
End If
On Error GoTo 0

ActiveSheet.PageSetup.PrintArea = rngWanted.Address
ActiveCell.Select

End Sub

Sub CBarCmd_On()

Call Cbar_Off
Set cmdRClk = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, _
Before:=1, _
Temporary:=True)
With cmdRClk
.Caption = "Set Print Range"
.FaceId = 3917
.OnAction = "SetRangeAndOffer"
.TooltipText = "Click me to set print range on this sheet"
End With
End Sub

Sub Cbar_Off()
On Error Resume Next
Application.CommandBars("Cell").Controls("Set Print Range").Delete
On Error GoTo 0
End Sub

In ThisWorkbook module:
Option Explicit
Private Sub Workbook_Activate()
Call CBarCmd_On
End Sub

Private Sub Workbook_Deactivate()
Call Cbar_Off
End Sub

A great day to all. Don't know what today looks like in your neck of the woods, quite nice out here.

Mark

lucas
12-13-2008, 09:33 AM
Hi Mark, Windy and cold in Okla today......

I didn't get that the problem was on cancel. I may have missed that. I was just trying to get The print area select to work for Simon in v2000 to begin with before tackling the beforeprint event.........

primaryteach
12-13-2008, 09:51 AM
Good news and bad news!

The good news is that on an empty sheet it works.

The bad news is it doesn't want to work in my workbook. I guess this must be due to some of the options or other VBA code I have added to my file. Hence, I am uploading a much smaller version that has had a lot of stuff deleted but the vba code remains intact. It is a workbook that local schools have asked me to design.
I have assigned Lucas's macro to the 'Print' button on the 'Entry' sheet.

Simon

P.S. I have just read via a google search that conditional formatting may be a reason as to why the inputbox is failing. Apparently this affects the inputbox from functioning correctly (but not in Excel 2007). I have some conditional formatting on column G. I also have some validated data in every other column from J onwards - I'm not sure if that has anything to do with it. (Straws - clutching at!!)

primaryteach
12-13-2008, 10:06 AM
Additional info - I have just tried GTO's code (I like the command bar button idea), but just as Lucas' and my original code, it produces an error once you have selected the range and pressed 'OK' (I removed the 'error' VBA lines, so I could see what the debug would come up with - 424 object required on the selection line of code).

Therefore it must be something in my workbook that's conflicted - see previous post, particularly the P.S.

Thanks for all your input so far guys, I really appreciate the support.

Simon

lucas
12-13-2008, 10:25 AM
Simon, I'm starting to think you are right about the conditional formatting. It works fine on my 2003 and I have no way to test on 2000 so maybe check out this link:
http://www.ozgrid.com/forum/showthread.php?t=29264

I would suggest removing the conditional formatting on a copy and see if that resolves it........then we can figure out how to go from there.

lucas
12-13-2008, 10:30 AM
Simon, Just a note:
I removed the conditional formatting from your workbook and it sped it up considerably. I have a very fast computer and it was slow loading on my machine when I first downloaded it.......

primaryteach
12-13-2008, 10:36 AM
Conditional formatting is the culprit - I have tested the code without it in.

I still want cells in column G to go green if a value of 180 or more is entered in them, red for 179 or less.

Is there a way of using VBA to colour the background of each cell, rather than conditional formatting? If there is, I think this issue will be solved.

Simon

lucas
12-13-2008, 10:49 AM
Try this. Put this in the code module for the sheet "Entry"
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
For Each cel In Range("G:G").Cells
If IsNumeric(cel.Value) And cel.Value <> "" Then
If cel.Value >= 0 And cel.Value < 180 Then
cel.Interior.ColorIndex = 3
cel.Font.ColorIndex = 2
cel.Font.Bold = True
ElseIf cel.Value >= 180 Then
cel.Interior.ColorIndex = 4
cel.Font.ColorIndex = 1
cel.Font.Bold = True

Else 'default conditions
cel.Interior.ColorIndex = 0
cel.Font.ColorIndex = 1
cel.Font.Bold = False
End If
End If
Next
End Sub

Your file attached with conditional formatting removed and the code above added to the sheet "Entry"

Try adding your numbers in column G and then try your select code.........

Edit: I had to edit this to add the file........critters in my computer caused me to post too soon...

primaryteach
12-13-2008, 11:26 AM
Just had a quick trial - this seems to work a treat. I will apply to my full file tomorrow. I am really grateful for the time and effort you have put into this Lucas. Conditional formatting and input boxes obviously don't like sharing a bed together!

lucas
12-13-2008, 11:30 AM
Good news Simon, the file should be faster without the conditional formatting also. Let us know if that is true also.

Teachers I know have also been interested in this thread, just an FYI:
http://www.vbaexpress.com/forum/showthread.php?t=16113

primaryteach
12-14-2008, 03:26 AM
Lucas, I have just retried your code when I noticed that the cells don't want to reset themselves back to the white if the cell has already turned green or red, they just remained red. Hence the code appears to be ignoring the last 'else' section. I have tried the code in an empty workbook and it produces the same results.

Any ideas how to rectify this?

Simon

lucas
12-14-2008, 11:48 AM
Try this Simon:

Notice the range is G11 to G19 if you need more cells you will have to change the row 19 in the code to include any addtional rows.....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
For Each cel In Range("G11:G19").Cells
If IsNumeric(cel.Value) And cel.Value <> "" Then
If cel.Value >= 0 And cel.Value < 180 Then
cel.Interior.ColorIndex = 3
ElseIf cel.Value >= 180 Then
cel.Interior.ColorIndex = 4
Else 'default conditions
cel.Interior.ColorIndex = 0
cel.Font.ColorIndex = 1
End If
Else
cel.Interior.ColorIndex = 0
cel.Font.ColorIndex = 1
End If
Next
End Sub

primaryteach
12-14-2008, 12:11 PM
Thank you for this Lucas. This works as long as I leave the sheet unprotected. What code do I need to add to make this work on a protected sheet (Does the sheet have to be told to unprotect and then re-protect itself?)

Simon

lucas
12-14-2008, 12:39 PM
Simon, You will have to unlock the cells in the range G11:G19

select the range and right click on them.....select "format cells.."

Select the protection tab and remove the check next to "Locked"

say ok.

Now add this code to the sheet which will allow the code to unprotect the sheet long enough to format or remove formatting from the cells and then reprotect it.

I used the password pwd in the attachment.

ps you always must unlock cells that you wish to be able to use when the sheet is protected....all others will be locked.

Aussiebear
12-27-2008, 03:32 PM
Edit: I had to edit this to add the file........critters in my computer caused me to post too soon...


Over here we call them...... fingers.:devil2:

GTO
12-28-2008, 07:31 AM
Here in the States, we know that you type w/your fingers, and that critters are small animals, mostly rodents, that play w/the PC when you aren't looking...

lucas
12-28-2008, 11:19 AM
Ted must not have stubs for fingers as I do......I'm with Mark, sometimes my computer does what I tell it to and not what I want it to do so I'm blaming that on some obscure outside force.......can't be me, I'm sure......kinda.