Sheet Order _ Numbering

[Ardy]

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

 

[isabelle]

hi Ardy,

Dim wk As Workbook, i As Integer
Set wk = ActiveWorkbook
For i = 1 To wk.Sheets.Count
wk.Sheets(i).Name = i
Next


--
isabelle



Le 2011-12-13 11:51, Ardy a écrit :

 

[Ardy]

hi Ardy,

Dim wk As Workbook, i As Integer
Set wk = ActiveWorkbook
For i = 1 To wk.Sheets.Count
wk.Sheets(i).Name = i
Next

Isabelle:
Thanks for your Quick Reply......

The code is changing the name of the tab not the pre-assigned name by
the program.

Before executing your code

Sheet2(Name 1)
Sheet3(Name 2)
Sheet4(Name 3)

After executing your code

Sheet2(1)
Sheet3(2)
Sheet4(3)

Could it be possible to do.....

Sheet1(name 1)
Sheet2(name 2)
Sheet3(name 3)

Ardy

 

[GS]

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

 

[isabelle]

hi,

Sub Macro1()
For Each ws In ActiveWorkbook.Worksheets
i = i + 1
On Error Resume Next
ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = "Sheet" & i
Sheets(i).Name = "name " & i
On Error GoTo 0
Next ws
End Sub

 

[Don Guillett]

In VBE, Put cursor on sheet name>f4>at the top is Name... Now you may
refer to sheet1, sheet2, etc no matter where it is moved to and what
the user changed the name to.

 

[Ardy]

In VBE, Put cursor on sheet name>f4>at the top is Name... Now you may
refer to sheet1, sheet2, etc no matter where it is moved to  and what
the user changed the name to.

What Would I do Without this group.......

My Thanks to all. Garry I think you hit the nail on the head.
Meaningful name s are definitely a +. I think I do that. also your
assumption was correct they are numbered unless one gets deleted,
then you run into trouble. I think I have enough to peace together to
get the show running.

Thank You all.

Ardy

 

[GS]

Ardy explained on 12/13/2011 :

What Would I do Without this group.......

My Thanks to all. Garry I think you hit the nail on the head.
Meaningful name s are definitely a +. I think I do that. also your
assumption was correct they are numbered unless one gets deleted,
then you run into trouble. I think I have enough to peace together to
get the show running.

Thank You all.

Ardy

Thanks for the feedback!
Best wishes in your endeavour...