主页 / coding / 问题 / 79438046
Accepted
Caleb Phelps
Asked: 2025-02-14 08:21:09 +0800 CST

如何根据按钮所在的行找到表中的单元格？

  • 772

我想添加一个工作表，使用表格的前 2 个单元格作为其名称。例如，在下表中，按钮 1 将创建一个名为“Doe,John”的工作表。

最后的 第一的 按钮
多伊 约翰 按钮1
多伊 简 按钮2

该按钮的代码目前如下所示，弹出一个输入框，手动输入名称，打开它，然后按字母顺序对工作表进行排序。如果工作表已经存在，它只会打开该工作表：

Sub TestSheetCreate()
    Dim newSheetName As String
    Dim checkSheetName As String
    Dim I As Integer
    Dim J As Integer
    
    newSheetName = Application.InputBox("Input Sheet Name:", "Excel 10 Tutorial", "", , , , , 2)

    On Error Resume Next

    checkSheetName = Worksheets(newSheetName).Name

    If checkSheetName = "" Then
        Worksheets.Add.Name = newSheetName
    Else
        Sheets(checkSheetName).Select
    End If

    For I = 1 To Sheets.Count - 1
        For J = I + 1 To Sheets.Count
            If UCase(Sheets(I).Name) > UCase(Sheets(J).Name) Then
                Sheets(J).Move Before:=Sheets(I)
            End If
        Next J
    Next I
    
End Sub

我该如何用按钮所在行的前两个单元格自动生成的名称替换输入框？

excel

1 个回答

  1. Best Answer

    如果使用形状/图片作为工作表上的按钮。

    Sub TestSheetCreate()
    Dim newSheetName As String
    Dim checkSheetName As String
    Dim I As Integer
    Dim J As Integer
'    newSheetName = Application.InputBox("Input Sheet Name:", "Excel 10 Tutorial", "", , , , , 2)
    With ActiveSheet.Shapes(Application.Caller).TopLeftCell
        newSheetName = .Offset(, -2) & "," & .Offset(, -1)
    End With
    On Error Resume Next
    checkSheetName = Worksheets(newSheetName).Name
    If checkSheetName = "" Then
        Worksheets.Add.Name = newSheetName
    Else
        Sheets(checkSheetName).Select
    End If
    For I = 1 To Sheets.Count - 1
        For J = I + 1 To Sheets.Count
            If UCase(Sheets(I).Name) > UCase(Sheets(J).Name) Then
                Sheets(J).Move Before:=Sheets(I)
            End If
        Next J
    Next I
End Sub

    在此处输入图片描述

    • 0

相关问题