+ Reply to Thread
Results 1 to 4 of 4

Record time based on value being selected in list box

  1. #1
    Registered User
    Join Date
    05-26-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Record time based on value being selected in list box

    Hello,

    I'm trying to figure out how to record the time that a user selects an option in a list box. I have two list boxes in their own sheet that get their info from two different worksheets.

    I pass the listvalue to the worksheet, to select the item. But I need to record the time the listvalue changed on that worksheet. I put code below into a sheet, but it only works when I manually change the number in E4, not when you change the value of E4 (the listvalue) by using the drop down.

    Please, please any help really gratefully appreciated!

    Please Login or Register  to view this content.
    Last edited by Jadesky; 05-31-2010 at 09:17 PM.

  2. #2
    Registered User
    Join Date
    05-26-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Record time based on value being selected in list box

    Thought it would be helpful to add some context to this question. I have a list of potential projects and another list of executed projects. I need to find a way to track which list option the user last selected so that I can use the related row of info in further calculations.

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,375

    Re: Record time based on value being selected in list box

    Hello Jadesky,

    selecting a value in a listbox does not trigger an event.

    But there is a workaround. Assign the listbox selection a cell link, say cell K1.
    Every time you select a value in the listbox, K1 changes. But this alone does not trigger an event.

    But if there is another cell that performs a calulation on K1, then you can use the Calculate event to see if the Listbox value has changed. The calculation of the changed value in K1 does trigger an event.

    So, set up a dummy cell somewhere, with the formula

    =K1*1

    Then create a Calculate event macro along the lines of this:

    Please Login or Register  to view this content.
    The global variable ListValue will hold the old value of the ListBox cell.

    Now, whenever the worksheet recalculates, the macro will run. If the ListValueCell is different from the previously stored ListValue, then the current date/time will be added to the TimeStampCell

    Does that work for you?

    cheers

  4. #4
    Registered User
    Join Date
    05-26-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Record time based on value being selected in list box

    Thank you so much Teylyn - that worked perfectly!!

    Thank you also for your detailed explanation - really very much appreciated.

+ 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