How to "tag" worksheet for later identification

[Student7]

I'm looking for a way to tag a worksheet, so I can later check the tag and run different sets of code depending on the tag. I can't use the worksheet name as the tag since users will have the ability to rename sheets.

The best thing I've come up with is putting the tag in an unused hidden, locked cell and later checking the value of that cell in the code. I'm hoping there's some "more appropriate" way to do this?

[Bob Phillips]

Use the worksheet codename. It is a another name that is not exposed to the user.

[Student7]

There will be multiple sheets in a workbook that need the same tag, so codename won't work. I'm looking for something that works more like the tag property on a control.

[figment]

it appers that the worksheet lacks any form of a tag, so i propose a work around

you could define a named range. and shift what cells that range is pointing to, using the cell that the range looks at to determin what code your function runs. its not fool proff but most the users i have run accrosst dont know how to change range names. and those that do are smart enough not to mess with them.

[Dr.K]

Could you create your own tag by using the worksheet "CustomProperties Property"?

Its designed for XML metadata, but thats just a fancy tagging system...

[Bob Phillips]

Just create a sheet specific name for the tagged sheets

[vba]

With Worksheets("Sheet1")
.Names.Add Name:="'" & .Name & "'!WSTag", RefersTo:=True
End With
[/vba]

and you can test for it like so

[vba]

Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
If Not IsError(sh.Evaluate("'" & sh.Name & "'!WSTag")) Then
MsgBox sh.Name
End If
Next sh
[/vba]

[Student7]

Thanks for all the suggestions. The sheet specific named range is working great for what I need. I don't entirely understand why I can use TRUE as the argument for RefersTo, but it works great. Seems like a sharp programmers way to get away with not providing a required argument?

[Bob Phillips]

Quite honestly, it could be anything. It is just adding the sheet local name that is emulating a sheet property like a tag, the existence of the property, it is not the value given to that sheet local name.

[asingh]

Use the worksheet codename. It is a another name that is not exposed to the user.

XLD...what is this....is it an alias we somehow assign to the worksheet, or something the system has assigned..and we access it via VBA...??..How...?

regards,

asingh

[mikerickson]

How about creating custom properties "Sheet1's tag", "Sheet2's tag"..... (based on code names) Since the names of these properties are keyed to the code name, the correspondence between sheet and property won't be lost.

This will create those properties for each sheet in a workbook.

Sub createNewProperties()
Dim xSheet As Worksheet
On Error Resume Next: Rem in case property already exists
For Each xSheet In ThisWorkbook.Worksheets
    With ThisWorkbook.CustomDocumentProperties
        .Add Name:=xSheet.CodeName & "'s tag", LinkToContent:=False _
                , Type:=msoPropertyTypeString, Value:=vbNullString
    End With
Next xSheet
On Error GoTo 0
End Sub

This will return the value of the tag for the ActiveCell's sheet, no matter what the user renames the sheet.

ThisWorkbook.CustomDocumentProperties(ActiveCell.Parent.CodeName & "'s tag").Value

[Bob Phillips]

XLD...what is this....is it an alias we somehow assign to the worksheet, or something the system has assigned..and we access it via VBA...??..How...?

regards,

asingh

It is a system thing. If you look at the projectin the project explorere in the VBIDE, you will see all sheets listed. Each will have two names, like 'Sheet1(Sheet1)'.

The first is the codename, which a user never sees, the second is the name the user sees and can change.

You can change the codename by VBA or in the VBIDE in the properties window, it is the {Name} property.