Announcement

Collapse
No announcement yet.

VBA time stamp based on drop down

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA time stamp based on drop down



    Hi,

    I'm trying to use a bit of VBA code to time stamp some columns in a spreadsheet based on a drop down.

    I used the code from this post to get most of the way here: https://www.ozgrid.com/forum/forum/h...drop-down-list as it is basically what I want to do or very close.

    I'm stuck on what happens when the drop down option selected is not defined, I get an error 91 then.

    So for example

    Column D4:D13 have drop downs.

    Dropdown options are "Not Started", "Started", and "Complete"

    There is not time stamp needed for "Not Started"
    Column E4:E13 captures Started Time
    Column F4:F13 captures Complete Time

    Here is my code.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim fn As Integer
        If Not Intersect(Target, Range("D4:D13")) Is Nothing Then
            fn = [E3:F3].Find(Target.Value).Column
                If Cells(Target.Row, fn) = "" Then
                    Cells(Target.Row, fn).Value = Now
                    Cells(Target.Row, fn).NumberFormat = "mm/dd/yy hh:mm AM/PM"
                    Cells(Target.Row, fn).Locked = True
                End If
        End If
    
    End Sub
    The code works just fine for everything except when I select "Not Started". Ideally "Not Started" would reset the times to "blank" Right now I get an error. I'm not familiar with using the "If Not Intersect Is Nothing Then" statement to figure this out. Could someone point me in the right direction?

    thanks!

  • #2
    Amend your code as below. Add a value to G3 of "Open" I would then hide column G as it will always be blank and it will only confuse the reader if it is showing. Change your validation options to Open, Started, Completed.

    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim fn As Integer
        If Not Intersect(Target, Range("D4:D13")) Is Nothing Then
            fn = [E3:G3].Find(Target.Value).Column
            If Intersect(Target, Range("D4:D13")) = "Open" Then
                Cells(Target.Row, fn) = ""
            ElseIf Cells(Target.Row, fn) = "" Then
                Cells(Target.Row, fn).Value = Now
                Cells(Target.Row, fn).NumberFormat = "mm/dd/yy hh:mm AM/PM"
                Cells(Target.Row, fn).Locked = True
            End If
        End If
    
    End Sub
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on the Like Button.

    Comment


    • #3
      Keep it simple.

      Code:
      Private Sub Worksheet_Change(ByVal Target As Range)
      
          If Not Application.Intersect(Target, Range("D4:D13")) Is Nothing Then
      
              If Target.Value = "Started" Then Target.Offset(, 1) = Format(Now, "mm/dd/yy hh:mm AM/PM")
              If Target.Value = "Complete" Then Target.Offset(, 2) = Format(Now, "mm/dd/yy hh:mm AM/PM")
      
              If Target.Value = "Not Started" Then
                  Target.Offset(, 1) = ""
                  Target.Offset(, 2) = ""
              End If
      
          End If
      
      End Sub
      Last edited by davesexcel; 1 week ago.
      Dave
      XLorate

      Comment


      • #4
        Edit: Posted this before I saw Dave's post.

        Yeah this came close to doing what I wanted but I still didn't like the idea of putting a column to do it. After reading up some more on Intersect is Nothing and looking and your idea I came up with this which seems to work.

        Code:
        Private Sub Worksheet_Change(ByVal Target As Range)
            Dim fn As Integer
            If Not Intersect(Target, Range("D3:D12")) Is Nothing Then
                If Intersect(Target, Range("D3:D12")) = "" Then
                    Target.Offset(0, 1).ClearContents
                    Target.Offset(0, 2).ClearContents
                ElseIf Intersect(Target, Range("D3:D12")) = "Not Started" Then
                    Target.Offset(0, 1).ClearContents
                    Target.Offset(0, 2).ClearContents
                Else
                    fn = [E2:F2].Find(Target.Value).Column
                    Cells(Target.Row, fn).Value = Now
                    Cells(Target.Row, fn).NumberFormat = "mm/dd/yy hh:mm AM/PM"
                    Cells(Target.Row, fn).Locked = True
                End If
            End If
        
        End Sub
        Question: Does VB code in excel cause a slow down while it runs or should it be fast? Might be my PC acting up.

        thanks for the help!
        Attached Files

        Comment


        • #5
          Look at Dave's simpler approach.
          Alan

          Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
          FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

          If someone has helped you, say "thank you" by clicking on the Like Button.

          Comment


          • #6


            Yep that did it. Too many Intersects I guess? Thanks for the help.

            Comment

            Working...
            X