Olisthoughts
New Member
- Joined
- Apr 16, 2020
- Messages
- 33
- Office Version
- 365
- Platform
- Windows
Hi,
I'm making a break list at my new job for my team.
Because the list has multiple entries we are forced to concentrate unnecessarily to calculate how many agents are on break. It would be helpful for my colleagues to see how many agents are on break right now, since we have a rule of no more than 3 agents on break at the same time.
As you'll see in the screenshot, the agents can double-click on a cell for time input under Start time, and double-click on actual return time when they're back.
I'm assuming the way to do it is count the number of agents (rows) that have a value in start time with no value in return time, as that would be an indicator they're on break.
But no matter how I tried I couldn't figure it out. The closest I got was a formula that in pseudo code, basically said If start time + End time equals 1, then 1 agent on break, else nothing. However when more than 1 person was on break it would show 11 instead of 2.
For context, I will attach the codes I'm using (that are working), a screenshot or two, and also, for information, after an agent enters a value in start time or end time, those cells are locked. The estimated return time and actual break time are alway
s locked. And there are actually 15 agents on the team, I've only added 4 to work with.
Codes:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cell As Range
' See if updated cells in watched range
Set rng = Intersect(Target, Range("c:u"))
' Loop through cells in watched range
If Not rng Is Nothing Then
For Each cell In rng
If cell <> "" Then
ActiveSheet.Unprotect Password:="Break"
cell.Locked = True
ActiveSheet.Protect Password:="Break"
End If
Next cell
End If
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("c:u")) Is Nothing Then
Cancel = True
Target.Formula = Time
End If
End Sub
I'm making a break list at my new job for my team.
Because the list has multiple entries we are forced to concentrate unnecessarily to calculate how many agents are on break. It would be helpful for my colleagues to see how many agents are on break right now, since we have a rule of no more than 3 agents on break at the same time.
As you'll see in the screenshot, the agents can double-click on a cell for time input under Start time, and double-click on actual return time when they're back.
I'm assuming the way to do it is count the number of agents (rows) that have a value in start time with no value in return time, as that would be an indicator they're on break.
But no matter how I tried I couldn't figure it out. The closest I got was a formula that in pseudo code, basically said If start time + End time equals 1, then 1 agent on break, else nothing. However when more than 1 person was on break it would show 11 instead of 2.
For context, I will attach the codes I'm using (that are working), a screenshot or two, and also, for information, after an agent enters a value in start time or end time, those cells are locked. The estimated return time and actual break time are alway
Codes:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cell As Range
' See if updated cells in watched range
Set rng = Intersect(Target, Range("c:u"))
' Loop through cells in watched range
If Not rng Is Nothing Then
For Each cell In rng
If cell <> "" Then
ActiveSheet.Unprotect Password:="Break"
cell.Locked = True
ActiveSheet.Protect Password:="Break"
End If
Next cell
End If
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("c:u")) Is Nothing Then
Cancel = True
Target.Formula = Time
End If
End Sub
