+ Reply to Thread
Results 1 to 18 of 18

Automatic date, but after a dropdown selection is made, not before

  1. #1
    Registered User
    Join Date
    09-22-2013
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    30

    Automatic date, but after a dropdown selection is made, not before

    Is there something that I can add to this code to make it trigger the automatic date after the selection is made (I have dropdown selections in A) in the target cell rather than just when you click the cell? So in other words: I can just click cell A and the automatic date will show up 11 columns to the right. I want the dropdown selection to be made before the date shows up.

    HTML Code: 

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Automatic date, but after a dropdown selection is made, not before

    Try pasting the following into the appropriate tab (e.g. Sheet1) in the VBA editor.

    Please Login or Register  to view this content.
    Martin

  3. #3
    Registered User
    Join Date
    09-22-2013
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Automatic date, but after a dropdown selection is made, not before

    Thanks, but no luck, it continues to put date into L when you click cell A. No entry in A was required.

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Automatic date, but after a dropdown selection is made, not before

    OK - try

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-22-2013
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Automatic date, but after a dropdown selection is made, not before

    Ah yes this works fine when I run it by itself... but now I'm trying to integrate your code with an existing Private Sub Worksheet_Change and your code doesn't do its job when combined. I've included it below, any suggestions?

    HTML Code: 

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Automatic date, but after a dropdown selection is made, not before

    Please Login or Register  to view this content.
    This line of code is exiting the sub if the updated cell is not in column K. Therefore when the dropdown in column A is updated, the new code is never reached.

    Maybe this:
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  7. #7
    Registered User
    Join Date
    09-22-2013
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Automatic date, but after a dropdown selection is made, not before

    Fantastic! yes it works. Sorry one more question: how would I add code to trigger the date to display in cell R (when an entry is made in Q), and then an entry in S would cause the date to appear in T....?
    Last edited by FlyFisherman; 10-16-2013 at 01:17 PM.

  8. #8
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Automatic date, but after a dropdown selection is made, not before

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    09-22-2013
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Automatic date, but after a dropdown selection is made, not before

    Thanks for the try but this doesn't work, ElseIf I think is giving me problems.

  10. #10
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Automatic date, but after a dropdown selection is made, not before

    Can you be more specific about what doesn't work? Does it still work for changes to dropdowns in Column A? Does it halt with an error? What does it do? I am willing to test and correct if you provide your file.

  11. #11
    Registered User
    Join Date
    09-22-2013
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Automatic date, but after a dropdown selection is made, not before

    Thanks for your questions. Here is the code that works to provide auto date in Cell L when an entry is made in A (and code for the auto hyperlink):
    HTML Code: 
    As you know I'm trying to add auto date for other areas on the sheet. So here's what it looks like when I added your changes:
    HTML Code: 

  12. #12
    Registered User
    Join Date
    09-22-2013
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Automatic date, but after a dropdown selection is made, not before

    When I add an entry to Cell A, Q, and S It says, "Compile error: For without next"

  13. #13
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Automatic date, but after a dropdown selection is made, not before

    In the process of adding my code you removed the line
    Please Login or Register  to view this content.
    It should go immediately after the
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    09-22-2013
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Automatic date, but after a dropdown selection is made, not before

    I get the same error message...maybe I'm still doing something wrong....but here it is:

    HTML Code: 

  15. #15
    Registered User
    Join Date
    09-22-2013
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Automatic date, but after a dropdown selection is made, not before

    Oh the error this time actually says: Compile error Next without For

  16. #16
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Automatic date, but after a dropdown selection is made, not before

    The error messages are accurately describing what's wrong. I don't know how you managed to do this while adding line, but now you've deleted the line
    Please Login or Register  to view this content.
    For me to be of any further help I would need you to attach your file.

  17. #17
    Registered User
    Join Date
    09-22-2013
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Automatic date, but after a dropdown selection is made, not before

    Ok sorry...I tried this again and the error is "Compile error: Else without If" and the row that is highlighed in the Visual Basic viewer is the ElseIf Line.
    HTML Code: 

  18. #18
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Automatic date, but after a dropdown selection is made, not before

    I am sorry to be blunt but every time you fix one error you are changing something else that causes another error. You need to change one thing at a time. In this case you merged the If...Then line with the line following it, causing this latest error.

+ 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] create hyperlink to another sheet when specific selection is made on a dropdown
    By FlyFisherman in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-17-2013, 11:32 AM
  2. Automatic selection of Date from dropdown list
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-08-2013, 08:57 AM
  3. [SOLVED] Autofill data once a selection is made from a dropdown box.
    By ggummo in forum Excel General
    Replies: 3
    Last Post: 03-13-2013, 11:55 AM
  4. [SOLVED] Want to populate a group of cells when a particular selection is made from a dropdown list
    By ChezHenri in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-30-2012, 01:08 PM
  5. Replies: 2
    Last Post: 09-21-2011, 09:21 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