+ Reply to Thread
Results 1 to 5 of 5

Auto-update initial drop-down selection based on previous drop-downs

  1. #1
    Registered User
    Join Date
    06-14-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Question Auto-update initial drop-down selection based on previous drop-downs

    Hi - am new to the forum and appreciate your time.

    I have a legacy VB form I am updating at work which has four different drop down items that display times (the RowSource for each drop-down is simply a column of times in increments of five minutes). I attached an image.

    In order to prevent errors, I want to create a macro that accomplishes the following:

    After the 1st drop-down is set (maybe after it leaves focus or the user uses TAB), I would like the remaining three drop-downs to automatically default/populate to the same value as was selected in the 1st. When the second is set, I would like the remaining two to default to the second. And when the third is set, I would like the fourth to default to the third.

    For our purposes, each dropdown box should always be equal to or greater than the preceding box, and I would like to prevent errors by auto-updating the fields as the user moves through them.

    I am not sure how to capture and 'lost-focus' event or update the displayed value of the drop-down.

    Any ideas ?

    Thanks again
    Attached Images Attached Images
    Last edited by thornomad; 06-16-2010 at 09:56 AM. Reason: Figured it out with help! thanks.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Auto-update initial drop-down selection based on previous drop-downs

    I would use the change event of the control and code similar to this for each combobox (1, 2,3):
    Please Login or Register  to view this content.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Registered User
    Join Date
    06-14-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Auto-update initial drop-down selection based on previous drop-downs

    Thanks for your help - do I simply create a new Module (eg, Module3) for this Private Sub ? Or do I have to put it in a certain place ?

    I tried creating a new module, changing the variables to match my dialog box, and then running the code but I don't see anything happening.

    I added a MsgBox (just as a hack) to see if the event was being triggered but that didn't do anything either. For example:


    Please Login or Register  to view this content.
    I feel maybe I am not implementing this in the right place ...

    Thanks,
    Damon

  4. #4
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Auto-update initial drop-down selection based on previous drop-downs

    By form, I assume you mean a custom user form. However, if the "form" is a worksheet with embedded active-x control boxes this approach will still work.

    The code goes into the change event for the control as I show it. Each control must have the code, adjusted accordingly for the succeeding control. See if the attached is of any help.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-14-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Auto-update initial drop-down selection based on previous drop-downs

    Got it! I meant a custom "UserForm" ... and, I didn't realize that you could double click on a form item (eg, dropdown box) and open its custom private sub ... I was writing them in a new module ... after I did that, though, I got perfect results!

    Thanks so much. The example helped me find the error in my ways.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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