shahdelsol
Active Member
- Joined
- Jul 21, 2009
- Messages
- 276
- Office Version
- 365
- Platform
- 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
Timer Code
Code for starting timer when there is change in A2
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: