动态按钮-运行时错误1004-无法获取Worksheet类的Buttons属性 [英] Dynamic button - Runtime error 1004 - Unable to get the Buttons property of the Worksheet class

问题描述

我收到运行时错误1004-无法获取Worksheet类的Buttons属性"，它来自底部 Set b = Worksheets("Form").Buttons(Application.Caller)的引用按钮.

Hi I'm getting "Runtime error 1004 - Unable to get the Buttons property of the Worksheet class" and it's coming from line near the bottom Set b = Worksheets("Form").Buttons(Application.Caller) 'references button.

我基本上是在尝试将数据复制到数据透视表完全展开的字段旁边显示的动态按钮旁边的列中.我的按钮看起来不错，我只需要它们在单击该特定按钮时将相邻单元格中的数据复制到另一张工作表中的列表中即可.

I'm basically trying to copy the data in the column next to a dynamic button that appears next to the fully expanded fields of a pivot table. I've got the buttons appearing fine, I just need them to copy the data in the adjacent cell to a list in another sheet when that particular button is clicked.

Sub buttonGenerator()

Dim btn As Button
Application.ScreenUpdating = False
ActiveSheet.Buttons.Delete
Dim t As Range
Dim size As Long

size = Worksheets("Form").PivotTables("Pivottable1").TableRange2.Rows.Count 'returns number of rows in expanding pivot table

For i = 2 To size Step 1 'cycles through from row 2 to last row of pivot table
    If Not IsEmpty(Worksheets("Form").Range(Cells(i, 4), Cells(i, 4))) Then 'only shows button if last col of pivot table has data
      Set t = Worksheets("Form").Range(Cells(i, 5), Cells(i, 5))
      Set btn = Worksheets("Form").Buttons.Add(t.Left, t.Top, t.Width, t.Height)
      With btn
        .OnAction = "btnS" 'call btnS subroutine
        .Caption = "Button" & i 'button label
        .Name = "Button" & i 'button name
        End With
    End If

Next i

Application.ScreenUpdating = False

End Sub


Public Sub btnS()

Dim b As Object
Dim r As Integer
Dim c As Integer

Set b = Worksheets("Form").Buttons(Application.Caller) 'references button
With b.TopLeftCell 'returns row and col of button pushed
r = .row
c = .col
End With

origin = Range(r, c)
dest = Worksheets("Form Output").Range(Cells(1, 1))
dest.Value = origin.Value

End Sub

推荐答案

添加以下行:

 Option Explicit      ' :)
 Dim origin As Range
 Dim dest As Range

更正这些行:

 c = .colUMN
 ...
 SET origin = Worksheets("Form").Cells(r, c)
 SET dest = Worksheets("Form Output").Cells(1, 1)

一些建议:您可以使用 .Cells(i，4)代替 .Range(Cells(i，4)，Cells(i，4)))

Some advice: you can use .Cells(i, 4) instead of .Range(Cells(i, 4), Cells(i, 4)))

这篇关于动态按钮-运行时错误1004-无法获取Worksheet类的Buttons属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！