Create Name with a value as a string

[kienleong]

I am looking to identify sheet templates by giving them a sheet level name.

If the sheet name contains a string, then I get "="string"" as the name value, not "string".

example code

Sub TestSheetName()
    
    Dim SheetType As String
    Dim Wksht As Worksheet
    
    Set Wksht = ActiveSheet
    SheetType = "SheetType1"
    
    Wksht.Names.Add Name:="SheetType", RefersTo:=SheetType
    MsgBox "The Sheet Name is " & Wksht.Names("SheetType").Value

End Sub

 

[Joe4]

It looks like it is naming the sheet correctly (if you view the Name Manager after running it). It just appears the Wksht.Names("SheetType").Value calculation returns the equal sign in VBA. You can get rid of that pretty easily:

MsgBox "The Sheet Name is " & Right(Wksht.Names("SheetType").Value, Len(Wksht.Names("SheetType").Value) - 1)

 

[kienleong]

So, looks like this might just be a quirk in Excel.

Wksht.Names("SheetType")
Wksht.Names("SheetType").Value
Wksht.Names("SheetType").RefersTo

All return the same result that matches .RefersTo in name manager.

Your solution to clean it up is simple and effective.

Thanks for the reply.