Excel Macro, Time input button [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

Excel Macro Help PLEASEE!!!?

Ideally i want a time in and a time out button, so that when workers sign in they click the timein button and when they leave click timeout.

I have this code in a macro assigned to a button in my spreadsheet:

Sub timeIn()
Range("C4").Value = time()
End Sub
--------------------------------(minus lines obv)

I want it so that each consecutive time the button is clicked it will fill out the cell below as opposed to overwriting the one the time has just been entered into.
Something like:

Sub timein()
If ("C4") = 0 Then
Range("C4").Value = time()
Else
??????????

End Sub


Any help would be much apreciated!

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
presuming that time in goes to a blank row, you can locate the last use row and then increment that row by one

Here a function that can give you the last row

sSheetName : the sheet on which you want to find the last used row
myRange : optional, a range in which you want to find the last used cell

It will return 0, if nothing is found or else would return the row number of the last used row


Function getLastRow(sSheetName As String, Optional myRange As Range = Nothing) As Long
' get last use row on the sheet

   Dim Cell          As Range
   
   If (myRange Is Nothing) _
   Then
      Set Cell = Sheets(sSheetName).Cells.Find("*", Cells(1, 1), , , xlByRows, xlPrevious)
   Else
      Set Cell = Sheets(sSheetName).Range(myRange.Address).Find("*", Sheets(sSheetName).Range(myRange.Address).Cells(1, 1), , , xlByRows, xlPrevious)
   End If
   If Cell Is Nothing Then
      getLastRow = 0
   Else
      getLastRow = Cell.Row
   End If
   Set Cell = Nothing

End Function

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!