VBA for queue and countdown timer

shahdelsol

Active Member
Joined
Jul 21, 2009
Messages
276
Office Version
  1. 365
Platform
  1. Windows
I have this VBA code that I use to create queue and remove from the queue and start countdown timer. C1 has drop down menu that user can select from it and click on ADD button and then you have a new name in queue which is column A. It is working but I have some issues:

Issue1: I only want the countdown timer starts if only value in A2 changes but currently timer starts every time when adding or removing a name from the queue.
Issue2: If use adds a name from C1 it will add to the top of list where I like to go the bottom of list in Column A
Issue3: how do I create another button that this time user will be able to select a name in queue ( column A) and move it to any position (any Cell) in Column A without deleting any name from the queue.

Add and Remove Code

Code:
Sub Button1_Click()
Range("A2").Insert shift:=xlDown
Range("C1").Copy
Range("A2").PasteSpecial
ActiveCell = ActiveCell.Value & Chr(10) & Format(Now, "mm/dd/yyyy hh:mmam/pm")
Range("A:B").Sort Key1:=Range("B1"), order1:=xlAscending, Header:=xlYes
End Sub
Sub Button2_Click()
ActiveCell.EntireRow.Delete
End Sub

Timer Code

Code:
Sub starttimer()
Application.OnTime Now + TimeValue("00:00:01"), "nexttick"
End Sub
Sub nexttick()
If Sheet1.Range("f1") = 0 Then Exit Sub
Sheet1.Range("f1").Value = Sheet1.Range("f1").Value - TimeValue("00:00:01")
If Sheet1.Range("f1").Value <= TimeValue("00:00:10") Then
Sheet1.Shapes("textBox 1").Fill.ForeColor.RGB = RGB(255, 0, 0)
Else
Sheet1.Shapes("textBox 1").Fill.ForeColor.RGB = RGB(255, 255, 255)
End If
If Sheet1.Range("f1").Value <= TimeValue("00:00:10") Then
Beep
End If
If Sheet1.Range("f1").Value <= TimeValue("00:00:7") Then
Beep
End If
If Sheet1.Range("f1").Value <= TimeValue("00:00:4") Then
Beep
End If
If Sheet1.Range("f1").Value <= TimeValue("00:00:1") Then
Beep
End If
starttimer

End Sub

Code for starting timer when there is change in A2

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheet1.Range("A2") > 0 Then
starttimer
End If
If Target.Address = "$A$2" Then
 Range("F1") = "00:00:15"
 End If

End Sub
 
Last edited:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,214,987
Messages
6,122,614
Members
449,091
Latest member
gaurav_7829

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top