Linking Macro to Drop Down Box

  • Short version: I am attempting to find the language to execute a macro the first time the right mouse button is pressed by the user. It needs to execute only once per instance of the file being open. Below is the long version of my issue:)



    I am attempting to assign a macro to a drop down box, but I'm having a strange issue. We are using third party software to publish data through Excel to the internet, so I assume this is causing the issue. Issue -->The drop down box is populated by a named range, but the range fails to expand and contract properly when the macro is assigned to the box. Is there a creative way to assign the macro to the drop down box after the first click of the mouse by the user? I know how to use a seperate macro to assign the main macro, but what would the trigger language be? I would only want it to assign once per instance of opening the spreadsheet, but not necessarily upon opening, as the third party software is populating data and there could be a conflict. I wouldn't want a button either, as I would prefer a cleaner look.


    Thanks for any help!


    Chris

  • Hi Chris


    I'm struggling to understand your needs. To have a range expand/contract you can use a [dr]*[/dr].


    To have more control on Events of the ComboBox? Use one from the Control Toolbox toolbar.

  • Hi Dave,


    Thanks for the reply. I have since created a workaround for my issue, but it was an interesting delimma.


    I had a drop down box which was populated by a named range. As the third party software (Excel Writer) populated data into Excel, the named range should have expanded to accomidate the data. When I had a macro attached to the drop down box, the named range failed to expand. When I took the macro out, it expanded properly and populated the drop down. The macro was simply a retrieve script designed to pull data associated with the choice in the drop down box. I even tried attaching a different, very basic macro to the box and got the same result. My workaround was to create a macro that fired upon opening the workbook to attach my retrieve macro to the box. Have you heard of anything like this happening? Thanks for any input.


    Chris

  • The range is populated by an SQL query tied to a data tag located on the spreadsheet. For example, the data tag would be located in cell A2 (%%=LIST.NAMES). Before the spreadsheet is opened, the data is populated in cell a2 down to cell A whatever, apparently by inserting rows. For this reason, I made my named range A2:A3, so that it would expand as rows are inserted.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!