Sub quits on Dropdown.OnAction assignment

[Dr. Demento]

I'm running a sub from PERSONAL.XLSB that assigns a DropDown to the active sheet. Below is how it's being assigned.

Dim ddFilterDropDown As dropdown
  ' Add a new filter dropdown
  Set ddFilterDropDown = rngDest.Parent.Shapes.AddFormControl(xlDropDown, _
                                                              Left:=rngDest.OffSet(0, 1).Left, _
                                                              Top:=rngDest.OffSet(0, 1).Top, _
                                                              Width:=rngDest.OffSet(0, 1).Resize(1, 2).Columns.Width, _
                                                              Height:=rngDest.rows.Height).OLEFormat.Object

  With ddFilterDropDown
    .name = "pvtFilterDropDown"
    .OnAction = "UpdateFilterDropDown"
  End With

The problem comes on the execution of the .OnAction statement - the sub just quits; no errors. First of all, .OnAction doesn't show up in IntelliSense (but all the other options/methods are present). Am I missing a reference??

Does the code need to reside in the active worksheet instead of PERSONAL.XLSB?? The code originates and is modified from Emulate Excel Pivot Tables with Texts in the Value Area using VBA and this command works fine in the linked file. I'm NOT passing variables and the sub referenced in the .OnAction command is in the same module.

I even attempt to push thru any error using On Error Resume Next and the sub still quits. I've tried assigning data to the dropdown either before or after the .OnAction -- no joy!

I have no earthly idea what's going wrong.

 

[ParamRay]

Hello.

Not sure by your post if you already know this, but the OnAction property assigns a macro to be executed when an item is selected in the dropdown control.

You aren't missing a reference; the dropdown (form control) belongs to the Excel model. In VBA, however, the form control classes have been hidden since Excel 2000, because VBA support is deprecated. You can make them visible (including intellisense) by right-clicking in the VBA object browser and selecting "show hidden members".

After running the following CreateDropDown() method, the DropDown_Change() method will trigger every time an item is selected from the dropdown control. Perhaps you can adapt this example to suit your needs.

FYI, it doesn't matter if you are using personal.xlsb or not.

Public Sub CreateDropDown()
  Dim shp As Shape
  Dim ddn As DropDown
  With Range("B2:D2")
    Set shp = .Parent.Shapes.AddFormControl(xlDropDown, .Left, .Top, .Width, .Height)
    Set ddn = shp.OLEFormat.Object
    ddn.AddItem "Item 1"
    ddn.AddItem "Item 2"
    ddn.AddItem "Item 3"
    ddn.OnAction = "DropDown_Change"
  End With
End Sub

Private Sub DropDown_Change()
  Dim ddn As DropDown
  Set ddn = ActiveSheet.DropDowns(Application.Caller)
  MsgBox ddn.List(ddn.Value) & " was selected.", vbInformation
End Sub

 

[Dr. Demento]

Thank you! What was gacking it up was the fact I was attempting to both create the shape and assign it to a dropdown in the same line. Separating them into two lines made it work.

Thanks again.