Vba timestamp when pressing a button

[johnny70]

I have started to create a excel with vba that should put a timestamps in cells a row when pressed.
On same sheet I have a counter, calculation of "cycletime" and also a conditional formating to highlight if a cycle is larger that set value (also displayed in sheet)

This is the code used for Clickbutton:
Private Sub Click()


If koll = 1 Then
Worksheets("User Data").Protect Contents:=False
Worksheets("User Data").Cells(countunit, 1) = Format(Now, "mm/dd/yyyy HH:mm:ss")
countunit = countunit + 1
koll = 1

Cells(2, 4) = "Antal enheter"
Cells(3, 4) = countunit - 9

End If

Worksheets("User Data").Protect Contents:=True

End Sub

Initial question is how do I create a sub that make it possible to close and open the file and it continues from last position.
Current I need to use a reset Sub to make ut work but the I lose previous data
This is the code for reseting

Private Sub Reset_Click()
countunit = 9
koll = 1
Worksheets("User Data").Protect Contents:=False
Worksheets("Background Data").Protect Contents:=False

Worksheets("User Data").Cells(8, 1) = "Timestamp for unit to station"


Worksheets("User Data").Cells(3, 4) = 0
Worksheets("User Data").Range("A9:A800").ClearContents
Worksheets("User Data").Range("C9:C800").ClearContents

'Worksheets("Data").Cells.ClearContents

Worksheets("User Data").Cells(8, 1) = "Timestamp for unit to station"
Worksheets("User Data").Cells(2, 4) = "Number of units"
Worksheets("User Data").Cells(9, 1) = 0
Worksheets("Background Data").Cells(2, 6) = "Value for row calculation"
Worksheets("Background Data").Cells(3, 6) = countunit
Worksheets("Background Data").Cells(4, 6) = "Uppdated"
Worksheets("Background Data").Cells(4, 6) = Format(Now, "mm/dd/yyyy HH:mm:ss")


Worksheets("User Data").Protect Contents:=True
Worksheets("Background Data").Protect Contents:=True

End Sub

 

[rollis13]

You don't say where and how the variables "koll" and "countunit" are declared, so I'll just make a guess presuming they are declared as Global. Try this macro of mine to be pasted into the "ThisWorkBook" module. Eventually you can use it as a suggestion and tweak the rest of the project.

Option Explicit
Private Sub Workbook_Open()
    Dim lRow  As Long
    lRow = Sheets("User Data").Cells(Rows.Count, "A").End(xlUp).Row
    If lRow >= 8 Then
        koll = 1
        countunit = lRow + 1
    End If
End Sub

 

[johnny70]

You don't say where and how the variables "koll" and "countunit" are declared, so I'll just make a guess presuming they are declared as Global. Try this macro of mine to be pasted into the "ThisWorkBook" module. Eventually you can use it as a suggestion and tweak the rest of the project.

Option Explicit
Private Sub Workbook_Open()
    Dim lRow  As Long
    lRow = Sheets("User Data").Cells(Rows.Count, "A").End(xlUp).Row
    If lRow >= 8 Then
        koll = 1
        countunit = lRow + 1
    End If
End Sub

This is the initial variable declaration
Public countunit As Integer
Public koll As Integer

I did a copy of first the first code that used koll as a variable, maby I could do without

 

[johnny70]

Tnx.
Placed your code in my click sub: - It works

Public countunit As Integer

Public koll As Integer


Private Sub Click()

Dim lRow As Long
lRow = Sheets("User Data").Cells(Rows.Count, "A").End(xlUp).Row
If lRow >= 8 Then
koll = 1
countunit = lRow + 1
End If

If koll = 1 Then
Worksheets("User Data").Protect Contents:=False
Worksheets("User Data").Cells(countunit, 1) = Format(Now, "mm/dd/yyyy HH:mm:ss")
countunit = countunit + 1
koll = 1

Cells(2, 4) = "Antal enheter"
Cells(3, 4) = countunit - 9

End If

Worksheets("User Data").Protect Contents:=True

End Sub

 

[rollis13]

So I see that you worked it out, glad having been of some help .
By the way, you probably need to mark this thread as [Solved].