请求实现：ComboBox生成重复工作表标签时触发InputBox自定义命名的VBA代码

以下是修改后的代码，实现了重复命名时提示用户自定义名称的功能，同时补充了名称合法性校验：

Dim strNewName As String
Dim ws As Worksheet
Dim userInput As String

' 生成初始工作表名称
If CreateNewVBA.ComboBoxPayer.Value = "Other" Then
    strNewName = CreateNewVBA.TextBoxOtherPayer.Value & " " & CreateNewVBA.ComboBoxLOB.Value & " " & CreateNewVBA.ComboBoxContractType.Value
Else
    strNewName = CreateNewVBA.ComboBoxPayer.Value & CreateNewVBA.TextBoxOtherPayer.Value & " " & CreateNewVBA.ComboBoxLOB.Value & " " & CreateNewVBA.ComboBoxContractType.Value
End If

If strNewName <> "" Then
    ' 循环检查名称有效性，直到获得可用名称
    Do While IsSheetExists(strNewName) Or Not IsValidSheetName(strNewName)
        ' 弹出提示输入框
        userInput = InputBox("A similar contract already exists. Please enter a name for the new VBA.", "Duplicate Sheet Name", strNewName)
        
        ' 用户取消输入则终止流程
        If userInput = "" Then Exit Sub
        
        ' 校验输入名称合法性
        If Not IsValidSheetName(userInput) Then
            MsgBox "Invalid sheet name. Avoid using: \ / ? * [ ] and ensure length ≤31", vbExclamation
        Else
            strNewName = userInput
        End If
    Loop
    
    ' 创建并命名工作表
    Set ws = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
    ws.Name = strNewName
End If

' 辅助函数：检查工作表是否已存在
Private Function IsSheetExists(sheetName As String) As Boolean
    Dim targetSheet As Worksheet
    On Error Resume Next
    Set targetSheet = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0
    IsSheetExists = Not targetSheet Is Nothing
End Function

' 辅助函数：校验工作表名称是否符合Excel规则
Private Function IsValidSheetName(sheetName As String) As Boolean
    Dim invalidChars As String
    invalidChars = "\/?*[]"
    
    ' 检查名称非空、长度合规且无非法字符
    If sheetName = "" Or Len(sheetName) > 31 Then
        IsValidSheetName = False
    Else
        Dim i As Integer
        For i = 1 To Len(invalidChars)
            If InStr(sheetName, Mid(invalidChars, i, 1)) > 0 Then
                IsValidSheetName = False
                Exit Function
            End If
        Next i
        IsValidSheetName = True
    End If
End Function

核心功能说明 #

• 重复名称检测：通过IsSheetExists函数快速判断初始生成的名称是否已被使用
• 用户交互处理：名称重复时弹出指定提示的输入框，支持用户自定义新名称；若用户取消输入则直接终止流程
• 合法性校验：IsValidSheetName函数确保输入名称符合Excel规则（无非法字符、长度≤31位、非空）
• 循环校验机制：通过Do While循环持续验证，直到用户输入一个既不重复又合法的名称

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