Sub quits on Dropdown.OnAction assignment

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
618
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I'm running a sub from PERSONAL.XLSB that assigns a DropDown to the active sheet. Below is how it's being assigned.

Code:
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.
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top