Excel中的按鈕。你怎么能簡化

我有這個問題。我只做 VBA 大約一個星期。我有一個作業簿，我在其中創建了一個按鈕，該按鈕可以連續復制某個范圍并將其粘貼到另一個作業表上的表格中。我的問題是：我是否需要為每個按鈕創建一個模塊，或者我可以以某種方式簡化代碼以便為第一張紙上的每一行創建相同的按鈕？

Sub SelectRangea()

    Sheets("Tournaments").Select
    Range("B4:G4").Select
    Application.CutCopyMode = False
    Selection.Copy

    With Sheets("Results")
       lst = .Range("A" & Rows.Count).End(xlUp).Row   1
       .Range("A" & lst).PasteSpecial xlPasteColumnWidths
       .Range("A" & lst).PasteSpecial xlPasteValues
    End With

End Sub

uj5u.com熱心網友回復：

您需要相應地調整代碼，但這將為您添加一組按鈕，并告訴您按鈕是從...

Public Sub AddButtons()
    Dim lngRow As Long, rngCell As Range, objButton As Shape
    
    For lngRow = 1 To 10
        Set rngCell = Sheet1.Cells(lngRow, 1)
        
        Set objButton = Sheet1.Shapes.AddFormControl(xlButtonControl, rngCell.Left, rngCell.Top, rngCell.Width, rngCell.Height)
        objButton.OnAction = "ButtonPushAction"
    Next
End Sub

Public Sub ButtonPushAction()
    Dim objCaller As Shape
    
    Set objCaller = Sheet1.Shapes(Application.Caller)
    
    MsgBox "Top Cell = " & objCaller.TopLeftCell.Address & vbCrLf & _
        "Row = " & objCaller.TopLeftCell.Cells(1, 1).Row & vbCrLf & _
        "Column = " & objCaller.TopLeftCell.Cells(1, 1).Column, vbInformation, "Button Push"
End Sub

uj5u.com熱心網友回復：

我需要為每個按鈕創建一個模塊嗎？

我們只需要創建一個包含按鈕所需宏的模塊，我們可以對所有按鈕使用相同的宏。

我可以以某種方式簡化代碼為第一張紙上的每一行創建相同的按鈕嗎？

除名稱外，所有按鈕都應相同。它們可以是彼此的副本。

我假設我們要復制單擊的行。所以我改變了SelectRangea：

' Copy the code below to a standard module
Public Sub SelectRangea(RowNumber As Integer)
    ' Copy the row clicked
    Sheets("Tournaments").Select
    Range("B" & RowNumber & ":G" & RowNumber).Select
    Application.CutCopyMode = False
    Selection.Copy

    ' Paste the row clocked
    With Sheets("Results")
       lst = .Range("A" & Rows.Count).End(xlUp).Row   1
       .Range("A" & lst).PasteSpecial xlPasteColumnWidths
       .Range("A" & lst).PasteSpecial xlPasteValues
    End With
End Sub

這是按鈕的點擊處理程式：

' Copy the code below to a standard module
Public Sub MyButton_Click()
    Dim Btn As Object
    Dim RowNumber As Integer
    'Set Btn = ActiveSheet.Buttons(Application.Caller) ' either this
    Set Btn = ActiveSheet.Shapes(Application.Caller) ' or this
    With Btn.TopLeftCell 
        RowNumber = .Row
    End With 
    SelectRangea RowNumber
End Sub

自動創建按鈕

我們可以創建一個用于創建按鈕，如果不存在的話，使用宏Sheet.Shapes.AddShape，并將.OnAction它們來MyButton_Click：

' Copy the code below to a standard module.
' Create buttons on a sheet.
' Sht         : The sheet to create buttons on
' RowNumber   : Create buttons from RowNumber and down.
' ColNumber   : The column the button is created in.
' ColNumberSrc: The column used to determine the number of rows.
Public Sub AddButtons(Sht As WorkSheet,
               RowNumber As Integer,
               ColNumber As Integer, 
               ColNumberSrc As Integer)
    Dim MyLeft As Double
    Dim MyTop As Double
    Dim Rng As Range
    Dim Shp As Shape
    Dim NumRows As Integer
    
    NumRows = Sht.Range.Cells(Sht.Rows.Count, ColNumberSrc).End(xlUp).Row
    If NumRows < RowNumber Then Exit Sub
    For Idx = RowNumber To NumRows
        Set Rng = Sht.Range.Cells(Idx, ColNumber)
        MyLeft = Rng.Left
        MyTop = Rng.Top
        ' We could let the size of the button's we create be the same size as the cell.
        Set Shp = Sht.Shapes.AddShape(msoShapeRectangle, MyLeft, MyTop, 100, 22)
        Shp.Name = "Btn" & Sht.Index & "_" & Idx
        Shp.TextFrame.Characters.Text = "Clickme"
        Shp.OnAction = "MyButton_Click"
    Next Idx
End Sub

不要使用按鈕

我們可以洗掉按鈕并使用雙擊來代替。這將復制雙擊的行：

' Copy the three lines to the corresponding function in your sheet module.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim RowNumber As Integer
    RowNumber = Target.Row
    SelectRangea RowNumber
End Sub

不要混淆用戶

我們應該避免使用Copy和Select，因為它會惡化用戶體驗。我們應該只在用戶希望我們使用它們時使用它們。重構代碼以避免使用它們：

' Copy the code below to a standard module
Public Sub SelectRangea(ByVal RowNumber As Integer)
    Dim Sht As WorkSheet
    Dim Rng As Range
    Dim Dat As Variant
    
    ' Copy the row clicked
    Set Sht = Sheets("Tournaments")
    Set Rng = Sht.Range("B" & RowNumber & ":G" & RowNumber)
    Dat = Rng
    ' Paste the row
    Set Sht = Sheets("Results")
    RowNumber = Sht.Range("A" & Sht.Rows.Count).End(xlUp).Row   1
    Set Rng = Sht.Range("B" & RowNumber & ":G" & RowNumber)
    Rng = Dat
    ' Fix column widths
    Sht.UsedRange.Columns.AutoFit 
End Sub

也可以看看

• 如何在特定單元格處添加形狀

• 如何獲取單擊按鈕的行號。

• 如何獲得單擊的單元格的行

NB

我無法訪問辦公環境，因此暫時無法測驗代碼。

我認為我們可以為一個形狀設定一個選項，這樣當單元格被調整大小、添加或洗掉時它會留在它的單元格中。

標籤：擅长 vba

上一篇：Vlookup公式將一些資料從一張作業表中提取到另一張作業表（谷歌電子表格）

下一篇：Excel：動態下拉串列