VBA无法重命名新建工作表问题排查

Let's walk through the issues in your code and fix them so it works as intended—plus we'll add some robustness to handle edge cases you might run into:

Key Issues in the Original Code #

• Missing a proper procedure wrapper (Sub ... End Sub) — your code starts with variable declarations but no actual runnable subroutine
• Typo: Inputbox should be InputBox (VBA is case-insensitive, but this is the standard function name)
• Wrong empty input check: vbNull isn't the right value to compare against; use vbNullString to detect empty input or a canceled InputBox
• Forgot the core step: assigning the entered name to the new worksheet
• Minor inconsistencies in variable casing (e.g., oWs vs oWS) and unnecessary spaces around function calls
• No validation for invalid worksheet name characters (Excel blocks /:*?"<>| in sheet names, which would cause a runtime error)

Corrected & Robust Code #

Option Explicit

Sub AddNamedWorksheet()
    Dim oWS As Worksheet
    Dim sName As String
    Dim invalidChars As Variant
    Dim char As Variant
    
    ' List of characters Excel won't allow in sheet names
    invalidChars = Array("/", "\", ":", "*", "?", """", "<", ">", "|")
    
Again:
    sName = InputBox("Enter Sheet Name")
    
    ' Handle canceled input or empty name
    If sName = vbNullString Then
        MsgBox "No name entered. Exiting procedure.", vbInformation
        Exit Sub
    End If
    
    ' Check for invalid characters
    For Each char In invalidChars
        If InStr(sName, char) > 0 Then
            MsgBox "Invalid character '" & char & "' found. Please enter a valid sheet name.", vbExclamation
            GoTo Again
        End If
    Next char
    
    ' Check if the sheet name already exists (case-insensitive)
    For Each oWS In ThisWorkbook.Worksheets
        If LCase(sName) = LCase(oWS.Name) Then
            MsgBox "A sheet named '" & sName & "' already exists. Please try another name.", vbExclamation
            GoTo Again
        End If
    Next oWS
    
    ' Add new sheet after the active sheet and assign the name
    Set oWS = ThisWorkbook.Worksheets.Add(After:=ActiveSheet)
    oWS.Name = sName
    
    MsgBox "Successfully added sheet: '" & sName & "'", vbInformation
End Sub

What We Updated #

• Added a proper Sub AddNamedWorksheet() wrapper so you can run the code directly
• Fixed the InputBox typo and corrected the empty input check
• Added validation for invalid characters to prevent runtime crashes
• Added clear message boxes to inform you of issues (duplicate names, invalid characters) instead of looping silently
• Added the critical line oWS.Name = sName to assign your input to the new sheet
• Used ThisWorkbook.Worksheets instead of just Worksheets to ensure we're targeting the workbook with your code, not the active workbook (safer practice)

内容的提问来源于stack exchange，提问作者urdearboy