Results 1 to 6 of 6

Thread: I have a list of 27 macros and would like them in combo box

  1. #1

    I have a list of 27 macros and would like them in combo box



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi hope someone can help
    I have a list of 27 macros in a spread sheet and would like them in a combo box or some type of drop down box.

    Can anyone help
    Thanks in advace

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Are you looking for a handy way to trigger macros? If so, use Alt + F8. Or do you want users to only trigger specific macros from the sheet (i.e. are there more than 27?)

  3. #3
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    I tried something similar before. I'm assuming any one writing 27 macro's knows their stuff so this is a guideline

    on one sheet I had a descriptive name for each function (call it Mac1..Mac27)
    I created a named range of this (Mymacros)
    And then I hid the sheet from prying eyes

    Then on the main sheet
    Column A uses a data validation lookup to Mymacros
    Column C (and others) contain the input parameters (e.g. width height etc) (If you need input parameters)

    Then I created a module with a single UDF RunMyMacro that calls application.evaluate(macro(parameters)) and returns either true/false/result

    Finally on the main sheet Column B =RunMyMacro(A1&"(" & C1 & "," & D1&")")

    So change the data validation dropbox and/or input parameters and away it goes.
    Just take note a UDF can only return values to itself so the macro will fail updating elsewhere

    That worked well. then I tried getting cleverer by adding an OnChange event instead of a UDF so I can change values anywhere. Need to be very careful of infinite loops and volatile functions.....
    Last edited by WizzardOfOz; 2014-09-09 at 08:34 AM. Reason: dropped quotes

  4. #4
    Hi not sure if you got my last message.
    That looks around the thing lm looking for could l send you the file so you can look at it.
    Regards
    Dave

  5. #5
    Thanks for your reply
    Could l send you the file to take a look at
    Regards
    Dave

  6. #6
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    yep new to this site so not sure if there is an in-mail box here somewhere, almost home time in Oz though

Posting Permissions

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