+ Reply to Thread
Results 1 to 9 of 9

Combo box selection to call macro

  1. #1
    Forum Contributor
    Join Date
    05-23-2014
    Location
    Fredericia, Denmark
    MS-Off Ver
    Windows Office 2013, Excel 10
    Posts
    139

    Combo box selection to call macro

    Please see attached file. I've created a combo box that is linked to a cell such that whatever listing is visible in the combo box also appears in the linked cell (E2). I have recorded a macro that sorts for "goat"s in a column of animals. I need to know how to call this macro when the linked cell reads "Dehorn goats". (BTW, these are all just silly examples to illustrate my challenge with a much bigger spreadsheet....)

    Here's what's curious: when I select "Dehorn goats" in the combobox list it does not perform the sorting. But, after selecting this, if I then click on random cells, the second or third cell I double click runs the macro. I cannot figure out why, and there doesn't seem to be a consistent pattern in which cells work to activate the macro, either. Very strange. What am I doing wrong? I sense it has something to do with the recorded macro being in a Module rather than the sheet I'm working in, but I've tried cutting and pasting it into the sheet code with no luck.

    Below is the macro I've scripted to call the SortGoat macro I recorded. (and which does work when run on its own...)
    Please Login or Register  to view this content.
    Thanks for your help, I really need it. David
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Combo box selection to call macro

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    05-23-2014
    Location
    Fredericia, Denmark
    MS-Off Ver
    Windows Office 2013, Excel 10
    Posts
    139

    Re: Combo box selection to call macro

    Hi mehmetcik, I tried your code in Sheet 1 but it did not successfully call the SortGoat macro. If you were able to get it to work in my uploaded file, please rename that file and upload it so I can compare what I'm doing wrong. Thanks very much for the quick response! David

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Combo box selection to call macro

    click on developer
    click on design mode
    right click on listbox
    select view code

    paste this code

    Please Login or Register  to view this content.
    close widow
    exit design mode
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    05-23-2014
    Location
    Fredericia, Denmark
    MS-Off Ver
    Windows Office 2013, Excel 10
    Posts
    139

    Re: Combo box selection to call macro

    Hi Mehmetcik, thanks for sending this. It does the sorting but the following error pops up:

    "Run-time error 1004. AutoFilter method of Range class failed."

    Update: When I debug the sole line of code in my macro is highlighted:

    Please Login or Register  to view this content.
    I perused several site but have not found a re-code that works. Can you help me? Thanks. David

  6. #6
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Combo box selection to call macro

    Is it a combobox or a drop down list using data validation?
    Last edited by Solus Rankin; 08-08-2014 at 11:48 AM. Reason: sp
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  7. #7
    Forum Contributor
    Join Date
    05-23-2014
    Location
    Fredericia, Denmark
    MS-Off Ver
    Windows Office 2013, Excel 10
    Posts
    139

    Re: Combo box selection to call macro

    It's a combo box.

  8. #8
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Combo box selection to call macro

    Changing a combobox isn't going to fire the worksheet change event. I would change it to an activeX control and use the combobox change event.

  9. #9
    Forum Contributor
    Join Date
    05-23-2014
    Location
    Fredericia, Denmark
    MS-Off Ver
    Windows Office 2013, Excel 10
    Posts
    139

    Re: Combo box selection to call macro

    Sorry, I don't understand. It is definitely an ActiveX combo box. I know this because when Design Mode is "On" if I right click on it I have the options to View Code, Properties, etc., and it is in this Properties menu that I linked it to the cell. In my example selecting a listing in the combo box changes text in a cell linked to the combo box. (They always show the same text unless the linked cell is edited.) The macro is run when the linked cell has one specific listing from the combo box. Does that help decipher my error? Thanks Solus! David

  10. #10
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Combo box selection to call macro

    Duplicate post.
    Last edited by Solus Rankin; 08-08-2014 at 03:21 PM. Reason: Dup

  11. #11
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Combo box selection to call macro

    Replace your worksheet change event wth this
    Please Login or Register  to view this content.
    Last edited by Solus Rankin; 08-08-2014 at 03:20 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Want to call Macro when cell changes as a result of Combo Box Choice
    By CRIMEDOG in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-15-2012, 09:26 PM
  2. Run a macro based on a Combo Box Selection
    By DBHaley in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-27-2011, 03:32 AM
  3. Combo Box Change Event with Macro Call
    By Bosco6 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-21-2011, 02:40 PM
  4. [SOLVED] macro to blank combo box selection
    By elevdown in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-04-2008, 06:58 AM
  5. call macro from combo box
    By minkus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2007, 12:14 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1