0

I've managed to create a form where the user can expand the fields of a pivot table and, once they've completely expanded a field/branch, a button will appear in column E and that pivot field data is concatenated in column J (there are some hidden columns).

What I want is for the user to click an auto-generating button in column E which exports the corresponding data in column J to a list, somewhere on the workbook.

My code below automatically generates the buttons for fully expanded fields, but I have no idea how to write the code to link each button to the corresponding cell in column J - this is probably not very difficult but any help would be appreciated.

Pivot table

Sub buttonGenerator()
Dim btn As Button
Application.ScreenUpdating = False
ActiveSheet.Buttons.Delete
Dim t As Range
Dim size As Integer

size = ActiveSheet.PivotTables("Pivottable1").TableRange2.Rows.Count

For i = 2 To size Step 1
    If Not IsEmpty(ActiveSheet.Range(Cells(i, 4), Cells(i, 4))) Then
      Set t = ActiveSheet.Range(Cells(i, 5), Cells(i, 5))
      Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
      With btn
        .OnAction = "btnS"
        .Caption = "Add to summary" '& i
        .Name = "Btn" & i
        End With
    End If
Next i
Application.ScreenUpdating = False
End Sub

Sub buttonAppCaller()
    MsgBox Application.Caller
End Sub

So here is my code .. it is throwing Runtime error 1004 "Unable to get the Buttons property of the worksheet class". Not sure what I've done wrong but I need to get the data from the cell next to the button to copy over to the bottom of a list in sheet 2 when that particular button is clicked. Please help!

Sub btnS()
    Dim dest As Range
    Dim origin As Range

    origin = ActiveSheet.Buttons(Application.Caller).TopLeftCell.Offset(0, 1) 'input data from cell next to button click
    dest = Worksheets("Form Output").Range("A1") 'output data to list in sheet 2 - "Form output"
    Set dest = origin
End Sub
T Wilken
  • 25
  • 6

1 Answers1

0

  1. Don't use Integer for row counts as you did for size. Excel has more rows than Integer can handle. It is recommended always to use Long instead of Integer in VBA there is no benefit in Integer at all.

  2. The procedure every button invokes is called btnS as you defined in .OnAction = "btnS". Therefore you need a Sub with that name in a Module.

  3. You can use Buttons(Application.Caller).TopLeftCell to get the cell under a button and from that cell you can determine the row or column.

    Public Sub btnS() 'sub name must match `.OnAction` name
    MsgBox ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row
End Sub

  4. Instead of using ActiveSheet I recommend to use a specific worksheet like Worksheets("your-sheet-name") if you plan to use it on a specific sheet only. ActiveSheet can easily change and should be avoided where possible.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Can you walk me through step 3 as I'm still relatively new to VBA and programming in general. I need to get the corresponding data from next to the button to add to a list on sheet 2 when clicked. Thanks in advance. – T Wilken Jun 29 '18 at 00:00
  • `TopLeftCell` returns the cell under the button and with `TopLeftCell.Offset(row, column)` you can offset from there a specific amount of rows or columns eg `Offset(0, -2)` to move 2 columns left from there. Give it a try yourself and if you get stuck or errors show your code (in your original question) and ask something related to it. – Pᴇʜ Jun 29 '18 at 06:02
  • So I've added my code above.. I'm getting a runtime error 1004. I', trying to get the data from next to a clicked button over to column A in sheet 2. Thanks. – T Wilken Jul 03 '18 at 05:39
  • @TWilken instead of `Set` use `dest.Value = origin.Value` to copy the value of the origin cell into the destination cell. These are VBA basics so I recommend you to read some VBA basic tutorial to understand how variables and objects are handled. – Pᴇʜ Jul 03 '18 at 06:15
  • Hi sorry sorry I've fixed that though my fundamental issue seems to be the error "Unable to get the Buttons property of the Worksheet class". – T Wilken Jul 03 '18 at 22:53
  • I can't see what is wrong with this line of code `origin = ActiveSheet.Buttons(Application.Caller).TopLeftCell.Offset(0, 1)` – T Wilken Jul 03 '18 at 23:15
  • @TWilken You net to `Set origin` because `Offset` refers to a cell/range and that is an object and object need to use `Set` where values must not use `Set`. That's why I suggested to read a tutorial to understand how variables and objects are handled. – Pᴇʜ Jul 04 '18 at 06:04