1

I am writing code for a button in Excel with the intention of taking the row number of the clicked button, duplicate the corresponding row given the row number, and then inserting and shifting the duplicated row below the original row of the click button. See the pictures for example: Click here for picture before button is pressed. Click here for picture after button is pressed.

I have tried several revisions of code based on solutions to similar issues I find online such as here and here(among other places), but I cannot find a solution to my reoccurring error which is Unable to get the Buttons property of the Worksheet class. From what I've gathered online about this error, it occurs often when any argument passed to the worksheet function is not of the correct type or simply doesn't make sense. Below I posted two iterations of my codes;

    Private Sub CorrugatedR_Click()
    Dim b As Object, RowNumber As Integer
    ActiveSheet.Activate
    Set b = ActiveSheet.Buttons("CorrugatedR")
    With b.TopLeftCell
        RowNumber = .Row
        Rows(RowNumber + 1).Insert Shift:=xlDown
        Rows(RowNumber).Copy Rows(RowNumber + 1)
    End With
End Sub

The other version, which should do the same thing(I've been doing a lot of playing around):

Private Sub CorrugatedR_Click()
Dim b As Object, RowNumber As Integer
ActiveSheet.Activate
ActiveSheet.Buttons("CorrugatedR").Select
ActiveSheet.Buttons("CorrugatedR").Copy
b.Paste
    With b.TopLeftCell
        RowNumber = .Row
    End With
Rows(RowNumber + 1).Insert Shift:=xlDown
Rows(RowNumber).Copy Rows(RowNumber + 1)
End Sub

A quick important note is that originally instead of calling the button's name itself: ActiveSheet.Buttons("CorrugatedR") I implemented ActiveSheet.Buttons(Application.Caller) instead because several people made this suggestion, but this gave me another error Application.Caller = Error 2023. After doing research I think these problems both relate to the same reoccurring issue that ether it's relating to typing or something i'm unaware of; I tried implementing and using the information from the solution to the same error found here, and still no luck. My intuition is that the issue may lie in the code where I set b: Set b = ActiveSheet.Buttons("CorrugatedR")

I'm still new to VBA and Excel, so my whole approach could be miscued in itself, and I would really appreciate any help, I've been stuck on this one for a while.

M. Dreisbach
  • 25
  • 5

1 Answers1

0

If your code is in a module, this sample code will work (replace CommandButton1 with the name of your button):

Private Sub example()
    MsgBox ActiveSheet.CommandButton1.TopLeftCell.Row
End Sub

...Or if your code is on the sheet:

Private Sub CommandButton1_Click()
    MsgBox Me.CommandButton1.TopLeftCell.Row
End Sub
David
  • 1,222
  • 1
  • 8
  • 18
  • This worked, I was originally using a **ActiveX Controls** command button; when I implemented a **Form Controls** button instead, I was able to _Assign a Macro_ and from there, my old code worked. Thanks for pointing me in the right direction, I greatly appreciate it! – M. Dreisbach Jul 14 '17 at 15:30