Olisthoughts
New Member
- Joined
- Apr 16, 2020
- Messages
- 33
- Office Version
- 365
- Platform
- Windows
Hi,
I'm making a break list for my team at my new job.
No more than 3 employees are allowed to go on break at the same time, and I want a 4th employee (and more) to be able to reserve a break as soon as the 3rd employee comes back.
There are 3 types of breaks allowed "30 min Lunch break, two 15 Min breaks, and one 7 minute break". In the reservations column, when selecting a cell the 4th employee should have as a drop-down menu to select the type of break. (essentially selecting either a lunch break, a 15 min break, or a 7 min break) When a 3rd employee comes back - the current time is input in the cell under the "start time" column for the break selected under reserved.
Since more employees should be able to reserve their breaks - the current time should be associated with the reservation. When a 3rd employee is back - the reservation with the smallest value would be activated.
I'm assuming the pseudo-code would be something like "If currently on break is equal or smaller than 2, then calculate smallest value in the reservations column AND match text (type of break), and enter current time in the same row as smallest value, under start time column with matching break text". Since I'm new to Excel I don't know how to write the actual code/formula, or if it's even possible for anyone to do.
Another fantasy that I'm pretty sure is impossible was for as soon as current time is input as a result of the reservation, to have a notification pop-up on screen, or send an e-mail to the person who's reserved break has started, since they don't know when someone actually came back.
I'll attach a screenshot and more info for context.
Each break has 4 columns (Start time = double-click for time input), Estimated return time = adds the time allowed for the break to the current time, calculating est return time). Actual return time = current time input, and Actual break time = Subtracts start time from Actual return time.
I'm using a Helper column to accurately calculate employees on break (without it 3 open breaks from the same employee were counted as 3 employees).
Also important for context: The break list is likely to be uploaded and used from Microsoft Teams, where as I understand macros and VBA don't work. But formulas still do.
The codes that I use in local Excel:
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
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
I'm making a break list for my team at my new job.
No more than 3 employees are allowed to go on break at the same time, and I want a 4th employee (and more) to be able to reserve a break as soon as the 3rd employee comes back.
There are 3 types of breaks allowed "30 min Lunch break, two 15 Min breaks, and one 7 minute break". In the reservations column, when selecting a cell the 4th employee should have as a drop-down menu to select the type of break. (essentially selecting either a lunch break, a 15 min break, or a 7 min break) When a 3rd employee comes back - the current time is input in the cell under the "start time" column for the break selected under reserved.
Since more employees should be able to reserve their breaks - the current time should be associated with the reservation. When a 3rd employee is back - the reservation with the smallest value would be activated.
I'm assuming the pseudo-code would be something like "If currently on break is equal or smaller than 2, then calculate smallest value in the reservations column AND match text (type of break), and enter current time in the same row as smallest value, under start time column with matching break text". Since I'm new to Excel I don't know how to write the actual code/formula, or if it's even possible for anyone to do.
Another fantasy that I'm pretty sure is impossible was for as soon as current time is input as a result of the reservation, to have a notification pop-up on screen, or send an e-mail to the person who's reserved break has started, since they don't know when someone actually came back.
I'll attach a screenshot and more info for context.
Each break has 4 columns (Start time = double-click for time input), Estimated return time = adds the time allowed for the break to the current time, calculating est return time). Actual return time = current time input, and Actual break time = Subtracts start time from Actual return time.
I'm using a Helper column to accurately calculate employees on break (without it 3 open breaks from the same employee were counted as 3 employees).
Also important for context: The break list is likely to be uploaded and used from Microsoft Teams, where as I understand macros and VBA don't work. But formulas still do.
The codes that I use in local Excel:
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
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
