Results 1 to 3 of 3

Thread: Sub quits on Dropdown.OnAction assignment
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    546
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sub quits on Dropdown.OnAction assignment

    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 by Dr. Demento; Jul 24th, 2019 at 01:22 PM.

  2. #2
    Board Regular ParamRay's Avatar
    Join Date
    Aug 2014
    Location
    England, UK
    Posts
    1,195
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sub quits on Dropdown.OnAction assignment

    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
    Windows 10, Excel 365

  3. #3
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    546
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sub quits on Dropdown.OnAction assignment

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •