Help creating staff movements sheet

[nbohane]

Hi all,

I work in healthcare. We have staff that work in the community after hours. We currently have a very old paper and phone call system to conduct welfare checks of staff who are out after hours.
What I would like to achieve (image attached) is:
1. Staff member to open this excel
2. Navigate to their Clinic and name and press a button that would generate a time stamp for them leaving clinic and coloured red (I managed to to do this but only for the active cell not to change button to timestamp)
3. Staff will enter text into D-H columns
4. When staff return to work safely they click a button in I column which timestamps return and turns to green.

Thanks in advance, I hope this makes sense.
Thanks,
Nicola

 

[vcoolio]

Hello Nicola,

Here's a starting point with no buttons required:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.Count > 1 Then Exit Sub
    
    Application.EnableEvents = False
    
        If Target.Column = 1 Then
             Target.Offset(, 1).Value = Now
             Target.Offset(, 1).Interior.ColorIndex = 3
        End If
        
        If Target.Column = 9 Then
             Target.Value = Now
             Target.Interior.ColorIndex = 4
        End If
    
    Application.EnableEvents = True

End Sub

With this event code, the staff member would only have to click on their name in Column A which will then place a time stamp beside their name in Column B and high-light the cell in red. On returning, the staff member clicks on the empty cell in Column I on the same row as their name and a new time stamp will be placed in the cell and it will be high-lighted in green.

To implement this code:-
- Right click on the sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Test the code in a copy of your work book.
Please also note that it could be problematic and give you incorrect results if a staff member accidentally clicks on the wrong cell.

I hope that this helps.

Cheerio,
vcoolio.

 

[nbohane]

Hello Nicola,

Here's a starting point with no buttons required:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
    If Target.Count > 1 Then Exit Sub
   
    Application.EnableEvents = False
   
        If Target.Column = 1 Then
             Target.Offset(, 1).Value = Now
             Target.Offset(, 1).Interior.ColorIndex = 3
        End If
       
        If Target.Column = 9 Then
             Target.Value = Now
             Target.Interior.ColorIndex = 4
        End If
   
    Application.EnableEvents = True

End Sub

With this event code, the staff member would only have to click on their name in Column A which will then place a time stamp beside their name in Column B and high-light the cell in red. On returning, the staff member clicks on the empty cell in Column I on the same row as their name and a new time stamp will be placed in the cell and it will be high-lighted in green.

To implement this code:-
- Right click on the sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Test the code in a copy of your work book.
Please also note that it could be problematic and give you incorrect results if a staff member accidentally clicks on the wrong cell.

I hope that this helps.

Cheerio,
vcoolio.

Thank you so much, this is a great start for me. Really appreciate it

 

[vcoolio]

You're welcome Nicola. I'm happy to have been able to assist.
Let us know if you need any more help with this.

Cheerio,
vcoolio.

 

[vcoolio]

Hello Nicola,

Here's the code again slightly modified to ensure that nothing can be inadvertantly entered from row1 to row4.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.Count > 1 Then Exit Sub
    
    Application.EnableEvents = False
    
        If Target.Column = 1 And Target.Row > 4 And Target.Value <> vbNullString Then
              Target.Offset(, 1).Value = Now
              Target.Offset(, 1).Interior.ColorIndex = 3
        End If
        
        If Target.Column = 9 And Target.Row > 4 And Target.Offset(, -8).Value <> vbNullString Then
              Target.Value = Now
              Target.Interior.ColorIndex = 4
        End If
        
    Me.Columns.AutoFit
    Application.EnableEvents = True

End Sub

Cheerio,
vcoolio.

 

[vcoolio]

Hello Nicola,

Sorry to bother you again. I've noticed a minor error in the code in post #5. Here it is amended:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.Count > 1 Then Exit Sub
    
    Application.EnableEvents = False
    
        If Target.Column = 1 And Target.Row > 4 And Target.Value <> vbNullString Then
              Target.Offset(, 1).Value = Now
              Target.Offset(, 1).Interior.ColorIndex = 3
        End If
        
        If Target.Column = 9 And Target.Row > 4 Then
              If Target.Offset(, -7) <> "" Then
                    Target.Value = Now
                    Target.Interior.ColorIndex = 4
              End If
        End If
        
    Me.Columns.AutoFit
    Application.EnableEvents = True

End Sub

Cheerio,
vcoolio.

 

[nbohane]

Thank you! I will try it out now
Many thanks

 

[nbohane]

Hi vcoolio
Is there a way to automate a mailto response in excel if a field (Estimated return time) is past current time of day? This would allow me to notify our Main nurses someone hasn't returned. I did do alot of googling on this but couldn't solve the part where its greater than the time in estimated return time.
Thanks,

 

[vcoolio]

Hello Nicola,
Could you elaborate further on how you'd like this to work. Would you require a separate column to enter 'estimated return times' set up beside the actual logged back in (return) time? Did you want a pop-up message alerting you to a later than expected return time each time the workbook is opened or just an email sent to the head nurse advising of a late return (based on the actual logged back in time being past the estimated time rather than the current time of day)?

Cheerio,
vcoolio.

 

[nbohane]

Hello Nicola,
Could you elaborate further on how you'd like this to work. Would you require a separate column to enter 'estimated return times' set up beside the actual logged back in (return) time? Did you want a pop-up message alerting you to a later than expected return time each time the workbook is opened or just an email sent to the head nurse advising of a late return (based on the actual logged back in time being past the estimated time rather than the current time of day)?

Cheerio,
vcoolio.

Thanks vcoolio sorry for delayed reply. Yes a column I added was Estimated Time of return. An email sent to a specific email address that would alert if thevestimated time of return has past and the logged back in time is empty. If that makes sense. I maybe pushing my luck with what I am trying to achieve Thanks again