Page preview - remove the grey numbers

[grecko]

Hey chaps,
We do a lot of work in excel, and our standard formatting is such that our view is constantly in 'print preview' (Main models in white and random workings in the grey bit on the sides). In which case there is always the page number in view. is there any way of getting rid of it?
thanks!

 

[Joe Was]

This will let you put it back in "Normal" View and still know what print page you are on. It puts the Print Page Number in the Excel Status Bar [Generally the lower left of the window] and has and option for a message box, if you do not use the "Events" this option may be usefull, when you just want to know "How many print pages their are or what print page you are on." The code as is can work with just the "Standard Module Sub, alone or as the whole package, where each basic code module type gets some code, Standard Module, Sheet Module and ThisWorkbook Module. Note: If you go the Events route then Each Sheet Module in the Workbook gets the Sheet Module Events!

You can convert the Sheet Module Events to ThisWorkbook Events to avoid having these Events in Each Sheet Module:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
'ThisWorkbook code module only!

Application.StatusBar = False
End Sub

Private Sub Worksheet_Activate()
'Sheet module code, like: Sheet1!

Call PageBreakCount
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Sheet module code, like: Sheet1!

Call PageBreakCount
End Sub

Sub PageBreakCount()
'Standard module code, like: Module1.
Dim strThisRng$, strThisAddr$
Dim objTrue As Object

Application.DisplayStatusBar = True
Application.StatusBar = False

For PBFound = 1 To ActiveWindow.SelectedSheets.HPageBreaks.Count
strThisRng = ActiveSheet.Range(Cells(1, 1), Cells(ActiveSheet.HPageBreaks(PBFound).Location.Row, 1)).Address
strThisAddr = ActiveSheet.Range("A" & Selection.Row).Address

Set objTrue = Application.Intersect(ActiveSheet.Range(strThisRng), ActiveSheet.Range(strThisAddr))

If Not objTrue Is Nothing Then
'Option: Display Sheet Number in Status Bar!
Application.StatusBar = "Sheet: " & PBFound

'Option: Display Sheet Number in Message Box!
'MsgBox "Sheet: " & PBFound
Exit Sub
End If

Next PBFound
End Sub


To make it work on every sheet and not just the sheets you choose use:

The Standard Module code as above and do not use the Sheet Module Events. Then load the ThisWorkbook code module with:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
'ThisWorkbook code module only!

Application.StatusBar = False
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'ThisWorkbook code module only!

Call PageBreakCount
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'ThisWorkbook code module only!

Call PageBreakCount
End Sub

 

[grecko]

thanks, but we work in print preview for formatting reasons, (and so we know what we are printing) rather for reference to what page numbers there are. yes, weird I know. It is like using the highlighted area for the model and the grey (non printable area) as a no mans land. so it would be cool to get rid of the 'Page 1' etc to read whats under it more easily. does that explain it better?

 

[Joe Was]

Can't be done, your way!