Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
setting up a page numbering format
I want to set up a numbering system for an excel 2003 form. I DON'T want to
use page and page of found under header/footer because I don't want to have these items in the margins, but within the body of the form itself. The file is set up with sheet one (main form) followed by a continuation sheet which can be copied to additional tabs creating many continuation sheets. Using an IF formula, is there a way to reference the (sheet) cell and (of) cell on the first sheet (tab) and use it to sequence the rest of the sheets in the file? Example: Sheet 1 is the first sheet of 8 total sheets (tabs) in the file..... Sheet 1 is sheet 1 of 8, sheet 2 is 2 of 8, sheet 3 is 3 of 8..... etc. Later versions of excel may have this feature now built in, but not in 2003. Searching thru HELP in excel is getting me nowhere. Any enlightenment on this subject would be greatly appreciated.... Lenny Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
setting up a page numbering format
Hi Lenny
I think the routine below does what you specify. It captures the "SheetActivate" event for the workbook and renumbers the tabs as you require. Make sure you paste this into the code for the Workbook, NOT in a standalone module. Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim MySheet As Worksheet, WBSheets As Sheets Dim i As Integer Application.ScreenUpdating = False Set WBSheets = ThisWorkbook.Worksheets i = 0 For Each MySheet In WBSheets i = i + 1 MySheet.Name = CStr(i) & " of " & CStr(WBSheets.Count) Next MySheet Application.ScreenUpdating = True End Sub If this helps, please click "Yes" <<<<<<<<<<< "Lenny" wrote: I want to set up a numbering system for an excel 2003 form. I DON'T want to use page and page of found under header/footer because I don't want to have these items in the margins, but within the body of the form itself. The file is set up with sheet one (main form) followed by a continuation sheet which can be copied to additional tabs creating many continuation sheets. Using an IF formula, is there a way to reference the (sheet) cell and (of) cell on the first sheet (tab) and use it to sequence the rest of the sheets in the file? Example: Sheet 1 is the first sheet of 8 total sheets (tabs) in the file..... Sheet 1 is sheet 1 of 8, sheet 2 is 2 of 8, sheet 3 is 3 of 8..... etc. Later versions of excel may have this feature now built in, but not in 2003. Searching thru HELP in excel is getting me nowhere. Any enlightenment on this subject would be greatly appreciated.... Lenny Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
start page numbering from page 2 in excel | Excel Discussion (Misc queries) | |||
How to return the default setting of numbering format in Excel? | Excel Discussion (Misc queries) | |||
Format page numbering to be Page 1 of X | Excel Discussion (Misc queries) | |||
Setting a Prinout format for Printout of a page. | Excel Programming | |||
Setting sheet names as headers with page numbering | Excel Programming |