基于依赖单元的动态工作表名称 [英] Dynamic sheet names based on dependent cells

问题描述

抱歉，如果这很简单，但我是VBA新手。我正在尝试设置我的Excel表格，以便当第一张表格中的某些单元格更改（例如，A1，A2，A3，A4）时，其他四张单据的名称将更改为与之匹配。我找到了以下公式，如果我更改了该工作表上的特定单元格，则该工具可用;

`

  Private Sub Worksheet_SelectionChange（ByVal Target As Excel.Range）
设置目标=范围（A1）
如果Target =然后退出Sub 
开启错误GoTo Badname 
 ActiveSheet.Name = Left（Target，31）
退出Sub 
 Badname：
 MsgBox请修改A1中的条目。 &安培; Chr（13）_ 
& 它似乎包含一个或多个& Chr（13）_ 
& 非法人物。 &安培; Chr（13）
 Range（A1）。激活
 End Sub 
  

`不幸的是，如果我将A1更改为依赖于之前指定的主页上的四个单元格之一，它将无法正常工作，因为它只会查看保存在工作表中的更改。

有没有办法使用VBA查看一张单元格，然后更改另一张表单的表格名称以匹配？

谢谢

解决方案

像我在评论中提到的那样，重命名表格并不简单。你必须检查这么多东西。

我的假设

1. 您在工作簿中有5张表格; Sheet1 ， Sheet2 ， Sheet3 ， Sheet4 Sheet5


2. 当您更改 Sheet5 ，取决于更改的单元格， Sheets1-4的名称已更改


3. 我假设当 A1 更改， Sheet1 被重命名。当 A2 更改时， Sheet2 被重命名等等...

逻辑

1. 使用 Worksheet_Change 事件将陷阱变为单元格 A1 ， A2 ， A3 或 A4


2. 使用Sheet CodeName更改名称


3. 检查工作表名称是否有效。表单名称不能包含任何这些字符 \ / *？ []


4. 检查您是否已经有一个要使用重命名名称的工作表


5. 如果一切都是hunky dory然后继续替换

代码

看这个例子。此代码位于 Sheet5 代码区域。

  Dim sMsg As String 
 
 Private Sub Worksheet_Change（ByVal Target As Range）
 Dim wsName As String 
 
错误GoTo Whoa 
 
 sMsg =成功 
 
 Application.EnableEvents = False 
 
如果没有Target.Cells.CountLarge> 1然后
如果不相交（目标，范围（A1））没有，然后
 wsName = Left（Target，31）
 
 RenameSheet [Sheet1]，wsName 
 ElseIf Not Intersect（Target，Range（A2））Is Nothing Then 
 wsName = Left（Target，31）
 
 RenameSheet [Sheet2]，wsName 
 ElseIf Not相交（目标，范围（A3））不是然后
 wsName =左（目标，31）
 
 RenameSheet [Sheet3]，wsName 
 ElseIf不相交（目标，范围（A4））没有了
 wsName = Left（Target，31）
 
 RenameSheet [Sheet4]，wsName 
如果
结束If 
 
 MsgBox sMsg 
 Letscontinue：
 Application.EnableEvents = True 
退出子
哇：
 MsgBox Err.Description 
恢复Letscontinue 
 End Sub 
 
'~~>过程实际上重命名工作表
 Sub RenameSheet（ws As Worksheet，sName As String）
如果IsNameValid（sName）然后
如果sheetExists（sName）= False然后
 ws.Name = sName 
 Else 
 sMsg =工作表名称已存在，请检查数据
 End If 
 Else 
 sMsg =无效工作表名称
结束如果
 End Sub 
 
'~~>检查工作表名称是否有效
函数IsNameValid（sWsn As String）As Boolean 
 IsNameValid = True 
 
'~~>表单名称不能包含任何这些字符\ / *？ [] 
对于i = 1到Len（sWsn）
选择案例中（sWsn，i，1）
案例\，/，*，？ ，[，]
 IsNameValid = False 
退出
结束选择
下一个
结束功能
 
'~~> ;检查表是否存在
函数sheetExists（sWsn As String）As Boolean 
 Dim ws As Worksheet 
 
 On Error Resume Next 
设置ws = ThisWorkbook.Sheets（sWsn ）
错误GoTo 0 
 
如果不是ws是没有，那么sheetExists = True 
结束函数
  

截图

Apologies if this is simple, but I am new to VBA. I am attempting to set up the my Excel sheet so that when certain cells in the first sheet are changed (eg A1, A2, A3, A4) the names of four other sheets will change to match them. I have found the following formula which works if I change the specific cell on that sheet;

`

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
        Set Target = Range("A1")
        If Target = "" Then Exit Sub
        On Error GoTo Badname
        ActiveSheet.Name = Left(Target, 31)
        Exit Sub
    Badname:
        MsgBox "Please revise the entry in A1." & Chr(13) _
        & "It appears to contain one or more " & Chr(13) _
        & "illegal characters." & Chr(13)
        Range("A1").Activate
    End Sub

` Unfortunately it will not work if I change A1 to be dependent on one of the four cells on the main sheet specified previously, as it only looks for changes in the sheet it is saved in.

Is there a way to use VBA to look at a cell in one sheet and then change the sheet name of another sheet to match?

Thanks

解决方案

Like I mentioned in the comments, it's not that simple to rename the sheet. You have to check for so many things.

My Assumptions

1. You have 5 Sheets in a workbook; Sheet1, Sheet2, Sheet3, Sheet4 and Sheet5
2. When you change cells in Sheet5, depending on the cell which changes, Sheets1-4's names are changed
3. I am assuming that when A1 changes, Sheet1 is renamed. When A2 changes, Sheet2 is renamed and so on...

Logic

1. Use Worksheet_Change event to trap changes to cell A1, A2, A3 or A4
2. Use Sheet CodeName to change the name
3. Check if the sheet name is valid. A sheet name cannot contain any of these Characters \ / * ? [ ]
4. Check if you already have a sheet with the name you want to use for renaming
5. If everything is hunky dory then go ahead and replace

Code

See this example. This code goes in the Sheet5 code area.

Dim sMsg As String

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsName As String

    On Error GoTo Whoa

    sMsg = "Success"

    Application.EnableEvents = False

    If Not Target.Cells.CountLarge > 1 Then
        If Not Intersect(Target, Range("A1")) Is Nothing Then
            wsName = Left(Target, 31)

            RenameSheet [Sheet1], wsName
        ElseIf Not Intersect(Target, Range("A2")) Is Nothing Then
            wsName = Left(Target, 31)

            RenameSheet [Sheet2], wsName
        ElseIf Not Intersect(Target, Range("A3")) Is Nothing Then
            wsName = Left(Target, 31)

            RenameSheet [Sheet3], wsName
        ElseIf Not Intersect(Target, Range("A4")) Is Nothing Then
            wsName = Left(Target, 31)

            RenameSheet [Sheet4], wsName
        End If
    End If

    MsgBox sMsg
Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

'~~> Procedure actually renames the sheet
Sub RenameSheet(ws As Worksheet, sName As String)
    If IsNameValid(sName) Then
        If sheetExists(sName) = False Then
            ws.Name = sName
        Else
            sMsg = "Sheet Name already exists. Please check the data"
        End If
    Else
        sMsg = "Invalid sheet name"
    End If
End Sub

'~~> Check if sheet name is valid
Function IsNameValid(sWsn As String) As Boolean
    IsNameValid = True

    '~~> A sheet name cannot contain any of these Characters \ / * ? [ ]
    For i = 1 To Len(sWsn)
        Select Case Mid(sWsn, i, 1)
        Case "\", "/", "*", "?", "[", "]"
            IsNameValid = False
            Exit For
        End Select
    Next
End Function

'~~> Check if the sheet exists
Function sheetExists(sWsn As String) As Boolean
    Dim ws As Worksheet

    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sWsn)
    On Error GoTo 0

    If Not ws Is Nothing Then sheetExists = True
End Function

Screenshot

这篇关于基于依赖单元的动态工作表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！