Page preview - remove the grey numbers

grecko

New Member
Joined
Aug 12, 2006
Messages
13
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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

New Member
Joined
Aug 12, 2006
Messages
13
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?
 

Forum statistics

Threads
1,181,733
Messages
5,931,726
Members
436,800
Latest member
abowalid98

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top