rizwanulhasan
New Member
- Joined
- Jan 1, 2022
- Messages
- 4
- Office Version
- 2019
- Platform
- Windows
Hi All,
I have a sheet as below where when cell C4 is set to Do Not Disturb, B4 switches to Not Available. After 29 seconds, cell C4 deletes automatically thus switching B4 back to Available.
This is the VBA used and a sheet "Time Log" has been created.
Private Sub Worksheet_Activate()
Dim stampcell As Range
Dim TLSh As Worksheet
Set TLSh = Worksheets("Time Log")
Application.ScreenUpdating = False
''' Check if pass the 24h period to delete content
For Each stampcell In TLSh.Range("C4:C100")
If Now > stampcell.Value2 + TimeValue("00:00:29") Then
Me.Range(stampcell.Address).ClearContents
stampcell.ClearContents
End If
Next stampcell
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim stampcell As Range
Dim TLSh As Worksheet
Application.EnableEvents = False
Set TLSh = Worksheets("Time Log") ''' Time stamp sheet
Set myRng = Range("C4:C100") ''' Your targeted range
If Not Intersect(Target, myRng) Is Nothing Then '''Time-stamping
TLSh.Range(Target.Address).Value2 = Now
TLSh.Range(Target.Address).NumberFormat = "MM/DD/YYYY hh:mm:ss"
End If
Application.EnableEvents = True
End Sub
However, what I want is that once C4 is set to Do Not Disturb, instead of deleting it automatically after 29 seconds, it should ask to enter the date and time in the adjacent CELL D4.
For ex. D4 = 02-01-2022 12:00 PM. Now the cell C4 should remain as Do Not Disturb until the date and time mentioned in D4. Post this, it should delete itself thus switching B4 back to Available.
Can someone please help with such kind of VBA.
Thanks in advance.
Note: same thread also posted on excelforum.
I have a sheet as below where when cell C4 is set to Do Not Disturb, B4 switches to Not Available. After 29 seconds, cell C4 deletes automatically thus switching B4 back to Available.
| Book1.xlsm | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | ||||||
| 2 | ||||||
| 3 | Name | Availability | DND Status | Date | ||
| 4 | A | Available | 02-01-2022 12:00 PM | |||
| 5 | B | Available | ||||
| 6 | C | Available | ||||
| 7 | D | Available | ||||
Sheet1 | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B4:B7 | B4 | =IF(C4="Do Not Disturb","Not Available","Available") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| C4:C7 | Cell Value | ="Do Not Disturb" | text | NO |
| B4:B7 | Expression | =B4="Available" | text | NO |
| B4:B7 | Expression | =B4="Not Available" | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B4:B7 | List | Available, Not Available |
| C4:C7 | List | Do Not Disturb |
This is the VBA used and a sheet "Time Log" has been created.
VBA Code:
Dim stampcell As Range
Dim TLSh As Worksheet
Set TLSh = Worksheets("Time Log")
Application.ScreenUpdating = False
''' Check if pass the 24h period to delete content
For Each stampcell In TLSh.Range("C4:C100")
If Now > stampcell.Value2 + TimeValue("00:00:29") Then
Me.Range(stampcell.Address).ClearContents
stampcell.ClearContents
End If
Next stampcell
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim stampcell As Range
Dim TLSh As Worksheet
Application.EnableEvents = False
Set TLSh = Worksheets("Time Log") ''' Time stamp sheet
Set myRng = Range("C4:C100") ''' Your targeted range
If Not Intersect(Target, myRng) Is Nothing Then '''Time-stamping
TLSh.Range(Target.Address).Value2 = Now
TLSh.Range(Target.Address).NumberFormat = "MM/DD/YYYY hh:mm:ss"
End If
Application.EnableEvents = True
End Sub
VBA Code:
However, what I want is that once C4 is set to Do Not Disturb, instead of deleting it automatically after 29 seconds, it should ask to enter the date and time in the adjacent CELL D4.
For ex. D4 = 02-01-2022 12:00 PM. Now the cell C4 should remain as Do Not Disturb until the date and time mentioned in D4. Post this, it should delete itself thus switching B4 back to Available.
Can someone please help with such kind of VBA.
Thanks in advance.
Note: same thread also posted on excelforum.
How to auto delete cells after a specific time period
Hi All, I have a sheet attached where when cell C4 is set to Do Not Disturb, B4 switches to Not Available. After 29 seconds, cell C4 deletes automatically thus switching B4 back to Available. However, what I want is that once C4 is set to Do Not Disturb, instead of deleting it...
www.excelforum.com
