+ Reply to Thread
Results 1 to 5 of 5

Macro not automatically running until I click elsewhere on sheet

  1. #1
    Registered User
    Join Date
    01-02-2015
    Location
    Cambridge, UK
    MS-Off Ver
    2016
    Posts
    10

    Macro not automatically running until I click elsewhere on sheet

    Hi All,

    I have an issue that should be straightforward to resolve that I (as a relative n00b) can't work out for the life of me - the idea is that based on the choice in a dropdown menu, specific rows (named ranges) would be shown or hidden accordingly. The problem is that the macro does not run automatically. Ideally I would like it to run as required as soon as I select the option from the dropdown menu, but currently I have to click elsewhere in the sheet for it to work – any ideas to correct this…?

    I tried adding in:
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    at various stages but to no avail

    For info: the dropdown menu is called _Lookup, and the rows of data are split into two ranges: _123 and _ABC, with the range _All being both _123 and _ABC combined. In reality I have over 20 ranges that are included in _All to include in this macro, but for the sake of this forum I limited it to two to demonstrate the issue:

    Please Login or Register  to view this content.
    Thanks!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,422

    Re: Macro not automatically running until I click elsewhere on sheet

    You probably need it to be a Change rather than a SelectionChange event handler.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    01-02-2015
    Location
    Cambridge, UK
    MS-Off Ver
    2016
    Posts
    10

    Re: Macro not automatically running until I click elsewhere on sheet

    That was quick! Works perfectly, thanks!

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

    Re: Macro not automatically running until I click elsewhere on sheet

    Ok

    Your macro is a SelectionChange macro so it can only run if you select on a cell.

    Try using a calculate macro. Link your drop down menu to a cell ( T ) and then put a formula into a cell that references that cell. (T)

    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,422

    Re: Macro not automatically running until I click elsewhere on sheet

    You're welcome. Thanks for the rep.

+ 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. Running a Macro from a Checkbox WITHOUT Manual Click
    By Intervigilium in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2015, 07:27 PM
  2. [SOLVED] Active Sheet not automatically running macro
    By RichTea88 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-10-2014, 06:54 AM
  3. Replies: 0
    Last Post: 03-06-2013, 12:52 PM
  4. [SOLVED] Running a macro from within Userform click command
    By unreal_event_horizon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-14-2012, 09:50 AM
  5. Replies: 7
    Last Post: 07-28-2011, 11:11 AM

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