CODEWITHSUNDEEP
vbaexceluserform
Sid. T.
Sid. T.

Reputation: 93

Automatic Date Entered after userform data entered

Is there a way I can automate the date or time once the I enter the data from the User_Form via "enter/click" button? I've tried this code but it keeps restarting my excel workbook. On top of that, I have a ton of other codes in the Private Sub Worksheet_Change(ByVal Target As Range)

Is it possible it's overloaded ?

So I'm thinking if I could code it with my Userform_click() I'd be better off?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer

For i = 16 To 100 
    If Cells(i, 3).Value <> "" Then
        Cells(i, 1).Value = Date & " " 
        Cells(i, 1).NumberFormat = "mm/dd/yy"
    End If
Next

End Sub

Upvotes: 1

Views: 205

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

You need to add Application.EnableEvents = False in the beginning of your Sub, otherwise it will keep running it every time a value is changed inside the worksheet (like when you change it inside your For i = 16 To 100 loop).

Code

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer

Application.EnableEvents = False

For i = 16 To 100
    If Cells(i, 3).Value <> "" Then
        Cells(i, 1).Value = Date & " "
        Cells(i, 1).NumberFormat = "mm/dd/yy"
    End If
Next i
Application.EnableEvents = True '<-- restore to original setting

End Sub

Edit 1: You can write your Sub in another way, that it will enter it only if a cell is changed inside the searched Range("C16:C100"). Only if the modified cell is inside that range, then check each cell if the Value <> "".

Private Sub Worksheet_Change(ByVal Target As Range)

Dim C As Range

Application.EnableEvents = False
If Not Intersect(Range("C16:C100"), Target) Is Nothing Then
    For Each C In Intersect(Range("C16:C100"), Target)
        If C.Value <> "" Then
            C.Offset(, -2).Value = Date & " "
            C.Offset(, -2).NumberFormat = "mm/dd/yy"
        End If
    Next C
End If
Application.EnableEvents = True

End Sub

Upvotes: 1

Related Questions