# VBA Timestamp for non-active worksheet?



## sky5ky (Nov 29, 2022)

Hello, im currently trying to timestamp cells in a non-active worksheet, my "sheet1" loads a Userform box with a command button to load data into the next sheet...i need to timestamp the cells into the sheet the data is being dumped in, although it looks like the command button needs a named table/range to dump both data values into, any help?


----------



## Deek6t8 (Nov 29, 2022)

does using something like sheet1.range("A1").value = now() not work?  I have used this from form code many times in the past successfully


----------



## sky5ky (Dec 14, 2022)

Deek6t8 said:


> does using something like sheet1.range("A1").value = now() not work?  I have used this from form code many times in the past successfully


it might be messy but this is what im using atm
Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
xOffsetColumn = 1
Dim WorkRng1 As Range
Dim Rng1 As Range
Dim xOffsetColumn1 As Integer
Set WorkRng1 = Intersect(Application.ActiveSheet.Range("D:D"), Target)
xOffsetColumn1 = 2
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If

If Not WorkRng1 Is Nothing Then
    Application.EnableEvents = False
    For Each Rng1 In WorkRng1
        If Not VBA.IsEmpty(Rng1.Value) Then
            Rng1.Offset(0, xOffsetColumn1).Value = Now
            Rng1.Offset(0, xOffsetColumn1).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            Rng1.Offset(0, xOffsetColumn1).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

the text highlighted in orange is whats throwing my debug, im dumping the data into a named table/range on an inactive page and gives me an error, but when i manually type into that table/range it timestamps as it should


----------



## sky5ky (Dec 14, 2022)

Deek6t8 said:


> does using something like sheet1.range("A1").value = now() not work?  I have used this from form code many times in the past successfully


so heres what it looks like, it goes from the user form to the inactive sheet


----------



## sky5ky (Dec 21, 2022)

sky5ky said:


> it might be messy but this is what im using atm
> Private Sub Worksheet_Change(ByVal Target As Range)
> 'Update 20140722
> Dim WorkRng As Range
> ...


My fault i highlighted the wrong line thats the issue.....its actually
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)


----------

