macro to automatically insert static time in a cell

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I'm creating a sign-in worksheet for "retired volunteers" in a non-profit
organization. The sign-in sheet is to keep up with the time worked by
volunteers. As many are computer illiterate, I need the time to enter
automatically the easiest way when they log in so as to eliminate typing
errors. Maybe a macro? Can anyone help? Most have poor computer skills.
Thanks in advance.
 
You could tell them to hold down the Ctrl key and the Shift key and press the
semi-colon key. But somehow I don't think that will fly.
Or...
You could tell them to click the cell twice in the "Started At" column.

To make that happen...
Right-click the sheet tab, select View Code and paste the following code into
the big window on the right.
'--Code Starts Below

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Target(1), Me.Columns("C")) Is Nothing Then
Target(1).Value = Time
End If
End Sub

'----Code Ends Above
In the second line of the code change "C" to the appropriate column letter
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

..


"Bill" <[email protected]>
wrote in message
I'm creating a sign-in worksheet for "retired volunteers" in a non-profit
organization. The sign-in sheet is to keep up with the time worked by
volunteers. As many are computer illiterate, I need the time to enter
automatically the easiest way when they log in so as to eliminate typing
errors. Maybe a macro? Can anyone help? Most have poor computer skills.
Thanks in advance.
 
This variation might work better for you...
'--
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Target(1), Me.Columns("C")) Is Nothing Then
If Len(Target(1).Value) = 0 Then
Target(1).Value = Time
Cancel = True
End If
End If
End Sub
'--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
 
Thanks Jim. That will work. Except I have one other question. Is there a way
to make that work for the next column as well? The volunteers sign and then
sign out and the total time is recorded for the organizational records.
Thanks again.
Bill
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Target(1), Me.Columns("C")) Is Nothing Then
If Len(Target(1).Value) = 0 Then
Target(1).Value = Time
Cancel = True
End If
ElseIf Not Application.Intersect(Target(1), Me.Columns("D")) Is Nothing Then
If Len(Target(1).Value) = 0 Then
Target(1).Value = Time
Cancel = True
End If
End If
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Bill"
wrote in message
Thanks Jim. That will work. Except I have one other question. Is there a way
to make that work for the next column as well? The volunteers sign and then
sign out and the total time is recorded for the organizational records.
Thanks again.
Bill
 
Thanks again Jim. By the way, I decided to try just listing several columns
and only the time-in, time-out columns were empty. That seemed to work also
by inserting the time with just a double-click. This is greatly appreciated
and will really help a non-profit organization helping needy girls.
 
You are welcome. I did make one more code variation. In this one,
the "Log Out" cell will accept a time entry only if the "Log In" cell has data in it.
The two time columns must be adjacent for this to work.
It may or may not be a better fit for you.
'------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Target(1), Me.Columns("C")) Is Nothing Then
If Len(Target(1).Value) = 0 Then
Target(1).Value = Time
Cancel = True
End If
ElseIf Not Application.Intersect(Target(1), Me.Columns("D")) Is Nothing Then
If Len(Target(1).Value) = 0 And Len(Target(1)(1, 0).Value) > 0 Then
Target(1).Value = Time
Cancel = True
End If
End If
End Sub
------
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Bill"
<[email protected]>
wrote in message
Thanks again Jim. By the way, I decided to try just listing several columns
and only the time-in, time-out columns were empty. That seemed to work also
by inserting the time with just a double-click. This is greatly appreciated
and will really help a non-profit organization helping needy girls.
 
Back
Top