Select item from data validation dropdown list, then jump to cell and begin countdown timer

[aftbrah]

Hi

I have a data validation drop down list containing many items, but two of the items on the list when they are selected I want to jump to a cell and begin a count down timer in that cell.

The drop down list is at E16:E150. When item "X" is selected, I want it it to go to H16 and begin a countdown timer of 15 minutes. I'd like that cell to turn a certain color when the countdown timer is done.

I have another item in the drop down list, item "Z" when it is selected I want it to go to H16 and begin a countdown timer of 30 minutes. I'd also like this cell to turn a certain color when the countdown timer is done.

This is a data entry excel sheet, so I would like this timer option to be available on each row (H16:H150) if I decide to choose it from the drop down list.

Thank you

 

[cooper645]

Hi. Is it possible for a VBA solution to be accepted?

i was going down the route of the following without VBA;

=IF(E16="z",NOW()+"00:30",IF(E16="x",NOW()+"00:15","")

but it this runs into problems if the user doesn't press F9 followed by enter. Then the conditional formatting would be based on the cell value and a formula based on the time somehow

 

[aftbrah]

Hi. Is it possible for a VBA solution to be accepted?

i was going down the route of the following without VBA;

=IF(E16="z",NOW()+"00:30",IF(E16="x",NOW()+"00:15","")

but it this runs into problems if the user doesn't press F9 followed by enter. Then the conditional formatting would be based on the cell value and a formula based on the time somehow

Yes VBA solution is what I was looking for and thank you

 

[cooper645]

Right, i have had a messa about, i have a semi working solution for you, which someone may be able to develope further

I have first created a custom function as follows;

Public Function AddTime(Text As String, Cell As Range) As String
   If InStr(1, Cell.Value, Text, vbTextCompare) Then
       AddTime = Date + Time
   Else
       AddTime = ""
   End If
End Function

Then on the sheet i have emulated your dropdown with various letters in cells "E16:E20". of which X and Z are part of them.

Then in Cell "H16" i have the following Formula;

=IF(E16="z",J16+"00:15",IF(E16="x",I16+"00:30",""))
This looks at E16 and adds 15 mins if there is a Z, 30 mins if an X, to the time stamp from the relevant helper column.



I also have two other columns in use, I and J.
In "I16" i have the following which refers to the custom formula;

=AddTime("x",E16) #This puts the timestamp in the column I when "x" was selected in column E



In "J16" i have the following which refers to the custom formula;

=AddTime("z",E16) #This puts the timestamp in the column J when "z" was selected in column E




The whole Range "H16:J20" has a custom cell format which is;
dd/mm/yyyy hh:mm:ss



The Conditional formatting which works when the custom format is set, is applied to the range "H16:H20" as follows;

Format Cells only with:
Cell Value ~ Less than or equal to ~ =NOW()

Format set to fill red.


You can then hide columns I and J for appearance.

NB: the time stamps don't update on sheet changes, only the dropdown referenced change event.

also the conditional formatting works but you may wish to have a macro that refresshes on a timer as the contional formatting reapplies on worksheet changes, so if the time passes with no change to the worksheet the format won't apply until the next sheet calcualtion


Edit: this link for a way to update certain cells every "x" seconds


I hope this in some way helps you a little. As i say, someone else may have a neater solution for you, but this is the best i can offer up at the moment.

Regards,

Coops

Also
Heres the link to the file i used. Its on dropbox

 

[aftbrah]

Thank you cooper645!

However, I tried the file you linked, but the timer doesn't countdown. It simply adds 15 or 30 minutes to the current time and then creates a frozen timestamp in H16. I don't see seconds and/or minutes counting down.

Also for Columns I and J, I already have values in those columns for other things, so it would be best if that part was excluded.

Thank you for all your help and I will await for a further solution

 

[cooper645]

Right,

So i have spent some serious time on this and feel its nearly where you want it. I can't get two timers to count at the same time!

first the code for the timers etc, this goes in a VBA module

Option Explicit
Dim CountDown As Date, CountCell As Range
Sub Option_X()
ActiveCell.Offset(0, 3).Value = TimeValue("00:00:30") 'change this for the countdown if X selected (currently 30secs)
Set CountCell = ActiveCell.Offset(0, 3)
Call Timer
End Sub
Sub Option_Z()
ActiveCell.Offset(0, 3).Value = TimeValue("00:00:15") 'change this for the countdownv if Z selected (currently 15secs)
Set CountCell = ActiveCell.Offset(0, 3)
Call Timer
End Sub
Sub Timer()
    CountDown = Now + TimeValue("00:00:01")
    Application.OnTime CountDown, "ResetTime"
End Sub
Sub ResetTime()
Dim xRng As Range
Set xRng = CountCell
xRng.Value = xRng.Value - TimeSerial(0, 0, 1)
If xRng.Value <= 0 Then
    CountCell.Interior.Color = RGB(255, 50, 50) 'at zero color cell red
        MsgBox "Times up buddy", vbCritical, "Times Up!" 'at zero let the user know in a message box times up
            With CountCell
                .ClearContents      'at zero after msgbox, clear the timer out of cell
                .Interior.Color = xlNone  'then color the cell white again
                .Offset(0, -3).Value = ""   'finally empty the dropdown selction
            End With
    Exit Sub
End If
Call Timer
End Sub

Next the sheet code for the sheet you want this to work on

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, ActiveCell) Is Nothing Then
        Select Case ActiveCell
            Case "X": Option_X
            Case "Z": Option_Z
        End Select
    End If

End Sub

I hope this is what you were after.

Fingers crossed we are there or there abouts.

Coops


NB
I have once again supplied a sample file here

 

[aftbrah]

Thank you again cooper!

I tried the sample file and the timer works but only one at a time unfortunately . I notice if I select "X" but then I decide to choose "Z" instead, the timer counts down at 2 seconds per second. Also, when this happens it returns with a run-time error '1004' at the end of the countdown after the "Times up buddy" message box saying "Application-defined or object-defined error."

Although the pop-up message box is a neat feature, I won't be needing it for my purpose.

Thank you again cooper, you've still been super helpful!

 

[cooper645]

No problem, I don't think I can do any more to get it working for multiple cases. I hope that an MVP or someone more capable than me picks up where I left off for you.