CODEWITHSUNDEEP
excelvba
Peter Smith
Peter Smith

Reputation: 11

changing a worksheet name

On my named active EXCEL sheet I have put a string with fewer than 32 characters in let's say cell A1.

I want to use the string in A1 to rename the sheet. I have used VBA in the past but not for some time.

I would be pleased if someone could suggest a macro that would do this when the cell A1 is clicked. Are there any characters that cannot be in A1 (I want ~ and underscore)?

Upvotes: 1

Views: 19232

Answers (2)

brettdj
brettdj

Reputation: 55672

For a complete process I would recommend

  1. Testing whether the sheet name already exists (to prevent an error)
  2. Cleansing the non-valid charcters before renaming the sheet
  3. Checking the new sheet name is less than 32 characters (but not empty)

To use this code

  • right click your sheet tab
  • View Code
  • Copy and paste the code in
  • Alt + F11 back to Excel

The code is triggered by a right click on cell A1

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim rng1 As Range
Dim StrNew As String
Dim objRegex As Object
Dim blTest As Boolean
Dim ws As Worksheet
Set rng1 = Intersect(Range("A1"), Target)
If rng1 Is Nothing Then Exit Sub

StrNew = rng1.Value
Set objRegex = CreateObject("vbscript.regexp")

With objRegex
    .Pattern = "[\/\\\?\*\]\[]"
    .Global = True
    blTest = .test(StrNew)
    StrNew = .Replace(StrNew, vbNullString)
End With

On Error Resume Next
Set ws = Sheets(StrNew)
On Error GoTo 0

If ws Is Nothing Then
    If Len(StrNew) > 0 And Len(StrNew) < 32 Then
        ActiveSheet.Name = StrNew
        MsgBox "Sheet name updated to " & StrNew & vbNewLine & IIf(blTest, "Invalid characters were removed", vbNullString)
    Else
        MsgBox "Sheetname of " & StrNew & " is either empty or too long", vbCritical
    End If

Else
    MsgBox "Sheet " & StrNew & " already exists", vbCritical
End If

End Sub

Upvotes: 0

Trace
Trace

Reputation: 18859

Paste this code in the related Sheet object (VBA editor).
Note that you can also use an event such as double click.
On selecting cell "A1" of the active sheet, the sheet name will become the value contained by cell "A1". 

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim sSheet_Name             As String

If Target = ActiveSheet.Range("A1") Then
    sSheet_Name = ActiveSheet.Range("A1").Value
    ActiveSheet.Name = sSheet_Name
End If

End Sub

Check out: Valid characters for Excel sheet names

Upvotes: 1

Related Questions