2

Is there a way to give an Excel worksheet a caption/title/label different from its name? (Via OLE automation, if that is relevant.)

The reason is that I might have names that differ only in case, so I can't use them as Worksheet.Name. But I don't need to refer to them from VBA, I just want the user to see them.

Edit: I want to change these strings: screenshot

music2myear has a better explanation of my problem:

Excel enforces a no-duplication policy on the given name, and the given name is the visible name.

My question is/was: Can I have visible name different from the given name?

2
  • To be clear: You want a title (what shows in the Title Bar of Excel when the file is open) that may be different from the file name (what users see in a file browser or explorer)? Commented Aug 29, 2013 at 15:37
  • @music2myear: See edit. Commented Aug 29, 2013 at 16:03

3 Answers 3

1

You cannot set the visible title/label on a worksheet tab to be different from the actual title/label. Excel enforces a no-duplication policy on the given name, and the given name is the visible name.

However, in VBA, you do not necessarily have to refer to the worksheet using its given name.

From http://www.techrepublic.com/blog/10-things/10-ways-to-reference-excel-workbooks-and-sheets-using-vba/ we learn we can reference sheets by their index (dependent on order of sheets) or by their code name property.

Index:

Index values come in handy when you don't care about specific sheets, but only their number or order. Granted, that's not going to be a common task, but occasionally, referencing by index values can come in handy. The following procedure adds and deletes sheets based on the number of sheets you want:

Function ControlSheetNumber(intSheets As Integer)

  'Add or delete sheets to equal intSheets.

  Application.DisplayAlerts = False

  'Delete sheets if necessary

  While Worksheets.Count > intSheets

    Worksheets(1).Delete

  Wend

  'Add sheets if necessary

  While Worksheets.Count < intSheets

    Worksheets.Add

  Wend

  Application.DisplayAlerts = True

End Function

Code Name Property:

Code that refers to a Worksheet object by the name on the sheet's tab runs the risk of generating an error. That's because you must remember to update the code when you change the sheet's name. Not only is that a lot of trouble, users are apt to change a sheet's name. One way to safeguard code that refers to specific sheets by name is to use the CodeName property. The code name is the sheet's default name , which Excel assigns when you create it -- Sheet1, Sheet2, and so on. Changing the sheet's name, as displayed on the sheet's tab, does not change its code name, as you can see in Figure D. The names in parentheses are the sheet names (as shown on the sheet tabs). Notice that the default names, the code names, remain the same even if you change the sheet's name.

1
  • those are workbook properties not individual spreadsheets
    – user222864
    Commented Aug 29, 2013 at 15:41
1

You could put a space at the end of the worksheet name, to set it apart. Thus "Sheet1" will look the same as "SHEET 1 " to the user.

0
1

Unfortunately, The answer is no. You can't have two spreadsheets with same names.

enter image description here


I can suggest a workaround using VBA

Sub AutomateSpreadSheetName()
    Dim name1 As String
    name1 = "Sheet1" 'note Sheet1 already exists
    RenameIfExist name1
End Sub

Private Sub RenameIfExist(name1)
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name = name1 Then
            ws.Name = name1 & Chr(32) & Int(Rnd() * 10)
            RenameIfExist name1
            Exit Sub
        End If
    Next
End Sub

You set the name in the name1 variable. If the name exist in the Worksheet Collection then the current sheet will be renamed with a random digit from 0 to 9

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .