+ Reply to Thread
Results 1 to 18 of 18

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

Hybrid View

  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.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    For Each Cell In Target
        If Cell.Column = 1 Then Cell.Offset(0, 11) = Date
    Next Cell
    End Sub
    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

    Private Sub Worksheet_Change(ByVal Target As Range)
    For Each Cell In Target
        If Cell.Column = 1 Then Cell.Offset(0, 11) = Date
    Next Cell
    End Sub

  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
    MS 365 Family 64-bit 2411
    Posts
    26,697

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

        If Target.Count > 1 Or Target.Column <> 11 Then Exit Sub
    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:
    'This is option A to add hyperlink automatically. When "Cable Assy..." in column k dropdownn is selected then
    'a hyperlink is automatically created which links to the next blank cell A on the Cable Matrix Worksheet.
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Rws As Long, sh As Worksheet
        Set sh = Worksheets("CABLE_MATRIX")
        With sh
            Rws = .Cells(Rows.Count, "A").End(xlUp).Row + 1
        End With
    
        If Target.Count = 1 And Target.Column = 11 Then
           If InStr(Target, "Cable Assy") <> 0 Then
               ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(0, 5), Address:="", SubAddress:= _
                                          "CABLE_MATRIX!A" & Rws, TextToDisplay:="CABLE_MATRIX!A" & Rws
           End If
        End If
        
        ' CHECK FOR NEXT CONDITION
    
       For Each Cell In Target
          If Cell.Column = 1 Then Cell.Offset(0, 11) = Date
       Next Cell
    End Sub
    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
    MS 365 Family 64-bit 2411
    Posts
    26,697

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

          If Cell.Column = 1 Then 
             Cell.Offset(0, 11) = Date
          ElseIf Cell.Column = 17 Or Cell.Column = 19 Then
             Cell.Offset(0, 1) = Date
          End If

  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
    MS 365 Family 64-bit 2411
    Posts
    26,697

    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
    MS 365 Family 64-bit 2411
    Posts
    26,697

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

    In the process of adding my code you removed the line
    Next Cell
    It should go immediately after the
    End If

  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
    MS 365 Family 64-bit 2411
    Posts
    26,697

    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
       For Each Cell In Target
    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
    MS 365 Family 64-bit 2411
    Posts
    26,697

    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