动态按钮-运行时错误1004-无法获得工作表类的按钮属性

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (31)

我得到“运行时错误1004 - 无法获取Worksheet类的Buttons属性”,它来自底部附近的行Set b = Worksheets("Form").Buttons(Application.Caller) 'references button

我基本上试图将数据复制到一个动态按钮旁边的列中,该按钮出现在数据透视表的完全展开的字段旁边。我的按钮看起来很好,我只需要它们将相邻单元格中的数据复制到另一个工作表中的列表中,当单击该特定按钮时。

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
提问于
用户回答回答于

↓如果工作表(“表格”)不是ActiveSheet,则此↓将失败

Worksheets("Form").Range(Cells(i, 4), Cells(i, 4))

使用语句可以轻松完全限定引用

With Worksheets("Form") Worksheets("Form").Range(.Cells(i, 4), .Cells(i, 4))

Range是一组细胞。Cells()是一个范围。使用Cells()按行和列号引用单个单元格。

Worksheets("Form").Cells(i, 4)

  • c = .Col:c = .Column
  • 范围(r,c):范围不接受参数的整数。使用单元格(r,c)
  • origin和dest是范围。范围是一个对象。对象需要设置为等于另一个对象。
Sub buttonGenerator()

    Dim btn As Button
    Application.ScreenUpdating = False
    ActiveSheet.Buttons.Delete
    Dim t As Range
    Dim size As Long, i As Long
    With Worksheets("Form")

        size = .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 .Cells(i, 4) <> vbNullString Then      'only shows button if last col of pivot table has data
                Set t = .Cells(i, 5)
                Set btn = .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

    End With
    Application.ScreenUpdating = False

End Sub

Public Sub btnS()

    Dim b As Object
    Dim r As Integer
    Dim c As Integer
    Dim dest As Range, origin As Range
    With Worksheets("Form")
        Set b = .Buttons(Application.Caller)          'references button
        With b.TopLeftCell                            'returns row and col of button pushed
            r = .Row
            c = .Column
        End With

        Set origin = .Cells(r, c)
        Set dest = Worksheets("Form Output").Cells(1, 1)
        dest.Value = origin.Value
    End With
End Sub

所属标签

可能回答问题的人

  • HKC

    红客学院 · 创始人 (已认证)

    27 粉丝7 提问5 回答
  • 西风

    renzha.net · 站长 (已认证)

    9 粉丝1 提问3 回答
  • 螃蟹居

    1 粉丝0 提问2 回答
  • 富有想象力的人

    2 粉丝0 提问1 回答

扫码关注云+社区

领取腾讯云代金券