根据单元格值使用非法字符重命名工作簿中的特定选项卡 [英] Rename specific tab in a workbook based on cell value with illegal character

问题描述

我有一个带有工作表的工作簿，但有一个名为最终"的工作表，该工作表需要根据其中包含非法字符(例如"CHEVROLET/ZZ")的单元格值B2重命名.

I have a workbook with sheets but a specific sheet named "final" that sheet need to be rename based on cell value B2 which has an illegal character in it e,g "CHEVROLET/ZZ"

下面尝试过代码，但是代码适用于所有工作表.

Tried code below , but code are for all sheets .

更改了所有工作表，而不是特定的工作表最终"

changed all sheets instead of specific sheet "final"

Sub tabname()
    Dim ws As Worksheet

    For Each ws In Worksheets
        On Error Resume Next

        If Len(ws.Range("B2")) > 0 Then    
            ws.Name = ws.Range("B2").Value
        End If

        On Error GoTo 0

        If ws.Name <> ws.Range("B2").Value Then
            MsgBox ws.Name & " Was Not renamed, the suggested name was invalid"
        End If
    Next
End Sub

e，g最终"工作表标签中B2单元格中的"CHEVROLET/ZZ".

e,g "CHEVROLET/ZZ" in cell B2 in "final" sheet tab.

只有sheettab最终版"被重命名为"CHEVROLET-ZZ"作为最终结果

only sheettab "final" to be rename as "CHEVROLET-ZZ" as final results

推荐答案

我建议修整预期的名称，替换7个禁止的字符并将其剪切为允许的最多31个字符:

I suggest to trim the intended name, replace 7 forbidden characters and cut it to the allowed maximum of 31 characters:

Private Function NewTableName(ByRef IntendedName As String) As String
    Dim s As String
    s = Application.Trim(IntendedName)
    s = Replace(s, ":", "-")
    s = Replace(s, "\", "-")
    s = Replace(s, "/", "-")
    s = Replace(s, "?", "-")
    s = Replace(s, "*", "-")
    s = Replace(s, "[", "-")
    s = Replace(s, "]", "-")
    NewTableName = Left(s, 31)
End Function

最后:如果尝试使用已经存在的工作表名称重命名第二个工作表，则会出现错误.因此，您应该在之前检查它的存在.

At last: You get an error, if you try to rename a second worksheet with an already existing worksheet's name. So you should check its existence before.

这篇关于根据单元格值使用非法字符重命名工作簿中的特定选项卡的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！