+ Reply to Thread
Results 1 to 7 of 7

VBA to execute when target cell value changed/filled

  1. #1
    Registered User
    Join Date
    08-18-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    41

    Question VBA to execute when target cell value changed/filled

    Hi all,

    I have a VBA when it will execute after a hotkey is pressed. Basically to vlookup the value present on cell B onto C, D, E, etc.

    If I want to modify it such that it will detect that there is value in cell B2, and automatically execute the vlookup in C2, D2, E2, etc.

    Right now, if any of the cell in B is changed, will need to press the hotkey for them to re-calculate.

    Attached is the excel screenshot.

    Any help will be appreciated, thanks a lot.

    2013-03-03 23_43_01-Microsoft Excel - Label Issue - RTS List - Supplier Info.png

    Please Login or Register  to view this content.
    Last edited by arlu1201; 03-03-2013 at 02:31 PM. Reason: Code tags and not quote tags.

  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
    44,091

    Re: VBA to execute when target cell value changed/filled

    Please Login or Register  to view this content.

    This code goes in the worksheet class module, not a standard module.

    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
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: VBA to execute when target cell value changed/filled

    Quote Originally Posted by Kehjz View Post
    Right now, if any of the cell in B is changed, will need to press the hotkey for them to re-calculate.
    Once the formulas are in place, they would automatically recalculate if you have your worksheet's option set to Calculation = Automatic
    1. Click the Microsoft Office Button, and then click Excel Options.
    2. On the Formulas tab, select the calculation mode (Automatic) that you want.

  4. #4
    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
    44,091

    Re: VBA to execute when target cell value changed/filled

    @Kehjz: Thanks for the rep


    To put code in the worksheet class module:

    right click on the sheet tab
    select "view code" from the menu
    the VB editor will open with the an empty class module
    copy and paste the code from above into the empty page.

    return to your sheet and type a code into column B

    Done

    Regards, TMS

  5. #5
    Registered User
    Join Date
    08-18-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: VBA to execute when target cell value changed/filled

    Hi TMS, thanks a lot for the quick reply,it worked. Just one more thing.

    Say I copy and paste from B3-B10, the code will only vlookup the first entry, which is B3. Is there any way to make it vlookup all from B3-B10?

    Say I might copy and paste 100 entries to column B, best is to have all 100 entries do the vlookup instead of only the first one.

  6. #6
    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
    44,091

    Re: VBA to execute when target cell value changed/filled

    OK, that would be:

    Please Login or Register  to view this content.

    Regards, TMS

  7. #7
    Registered User
    Join Date
    08-18-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: VBA to execute when target cell value changed/filled

    Thanks Alpha frog,

    I think I set them to calculate only after I click F9 to prevent me from waiting for the calculation to complete every time I change a value..

    Thanks for pointing out.

    Quote Originally Posted by AlphaFrog View Post
    Once the formulas are in place, they would automatically recalculate if you have your worksheet's option set to Calculation = Automatic
    1. Click the Microsoft Office Button, and then click Excel Options.
    2. On the Formulas tab, select the calculation mode (Automatic) that you want.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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