How to uniquely identify worksheets

Vinit

How about using the Excel Object approach. If you have a look Sheet Objects in the VBE, you will see that the object name retains its place and name regardless of the name of the sheet, or its order in the workbook.

Tony

acw said:

Vinit

How about using the Excel Object approach. If you have a look Sheet Objects in the VBE, you will see that the object name retains its place and name regardless of the name of the sheet, or its order in the workbook.

Tony

Click to expand...

Thanks Tony,
The name of object is called "CodeName" which is a read only property.You can access it using VBA "MsgBox Sheet1.CodeName". But You cant access it in VC++.

Hi

How about the name, not the codename property

Sheet1.name will give the current sheet name.


Tony

There is "(Name)" which is CodeName which I cant access. and other is Name which is caption which we see in Excel. Sheet1.Name gives the name which changes when user rename worksheet.

vinit_rewatkar said:

The problem in using "Name" of worksheet is that user can change it any time

Click to expand...

How about protecting the Workbook's Structure via Tools=>Protection=>Protect Workbook ?

That will prevent users from Renaming the worksheets.

Regards

Hi Jafar,

It restricts user from Sheet rename/delete activities.

Regards,
Vinit

vinit_rewatkar said:

There is no event in excel to keep track of the name change of worksheet.

Click to expand...

Here is one way to monitor and create an event to a sheet tab name change.

Step 1
Insert a new sheet in your workbook.

Step 2
In column B starting in B1, manually type in the sheet tab names of the workbook's sheets, not including the new sheet (you could write an easy macro to do this if there are a lot of sheets).

Step 3
For as many sheets as there are (minus the new sheet), create a UDF in the following syntax, utilizing the sheets' codename. Examples for sheet codenames 1, 2, and 3:

Public Function SheetName1() As String
Application.Volatile
SheetName1 = Sheet1.Name
End Function

Public Function SheetName2() As String
Application.Volatile
SheetName2 = Sheet2.Name
End Function

Public Function SheetName3() As String
Application.Volatile
SheetName3 = Sheet3.Name
End Function

...and so on.

Step 4
In the new sheet, enter the udf formulas in column A that correspond to their tab named in column B:
A1: =SheetName1()
A2: =SheetName2()
A3: =SheetName3()
...and so on.

Step 5
In C1:C whatever, enter and copy down:
=IF(A1=B1,0,1)

Step 6
In D1 enter:
=SUM(C:C)

Step 7
In the new sheet's module:
Private Sub Worksheet_Calculate()
If Range("D1") > 0 Then MsgBox "Sheet name changed !", 48, "Warning"
End Sub

Step 8 (optional)
Hide the new sheet containing those formulas.


Now when a tab name is changed, a warning will pop up, and will continue to pop up each time a cell is edited, until the original sheet name is restored. From here you should be able to reference or handle that event in your VC++ code.

Hi,

An alternative to Toms solution, you can use Worksheet Names (As in Insert=>Name=>Define NOT as Sheet Tab names) to tag each Sheet and therefore use these Name tags as unique IDs.

First, decide a descriptive name for each Sheet like (Sales, Accounts ....) and run this code:

Note: I used the Array ShtsArr to store these descriptive names. Adapt as required.



Now, to retrieve a Sheet, just call the GetSheet function and pass the descriptive name as its argument. The correct target Sheet will always be retrieved regardless of whether it has been moved or renamed by the user as shown in the Test code below:





Regards.