Ardy presented the following explanation :
Hello All.
it is my understanding that Excel calls an spreadsheet file (*.xls,
*.xlsx) a Workbook and the tabs within are called Worksheet. Looking
at the VB editor I can see the tabs are ordered as Sheetx(name of the
tab).

Here is my question. is there a way to rename the Sheetx to numbers
starting from 1 to what ever number of exiting tabs. this way once I
get a file regardless of what the user has done I can assure that the
tabs are ordered from 1 to x. then it would be easier to reference
the tabs by numbers knowing they will be there.

So essentially I am numbering the tabs starting from 1.

Ardy

The problem you're going to run into here is duplication of existing
'codenames'. (CodeName IS what you're talking about!)

What I do is assign codenames to worksheets of a project and I use
those for selection regardless of what tabname the user assigns. I use
a custom function to get the tabname on the fly. Here's the function...

Function Get_SheetTabName(CodeName As String, Optional Wkb As Workbook)
As String
Dim Wks As Worksheet
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
For Each Wks In Wkb.Worksheets
If Wks.CodeName = CodeName Then Get_SheetTabName = Wks.Name: Exit
Function
Next
End Function

The way I assign codenames to project worksheets is as follows:

wksExpenses tabname="Expenses"
wksIncome tabname="Income"
wksSummary tabname="Summary"
wksTaxes tabname="Taxes"

...but user can change the tabname to whatever they want and I still get
the correct sheet via code as follows:

<air code
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
Sheets(Get_SheetTabName(wks.CodeName)).Protect
Next 'wks

It makes no sense to me to apply numbering to sheets since they already
have an index in the Worksheets collection. IOW, Sheet1 is #1, Sheet2
is #2, and so on. (assuming no sheets deleted) I suppose you could
rename them wks1, wks2, and so forth but why not give them meaningful
names that reflect their use in your projects? <IMO

Example:
Dim wksSource As Worksheet
Set wksSource = Sheets(Get_SheetTabName("wksSummary", ThisWorkbook))

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc