Worksheet_Change issues

I

Ignorant Guru

New Member
Joined
Dec 21, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Please forgive my ignorance, but I am not even sure I am approaching my issue correctly

What I am trying to achieve:
Run (25) individual timers triggered by list drop down selection.

What I have:
One timer working correctly via:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G6")) Is Nothing Then
 
    
    Select Case Range("G6")
 
        
        Case "Empty": timer_truck_lane_21
 
       
        Case "Assigned": stop_timer_truck_lane_21
 
 
   End Select

End If

End Sub

When I try to add more to my range and select case, I get an error from my stop timer macro.
EX:
VBA Code: 
Sub timer_truck_lane_21()
 
    interval = Now + TimeValue("00:00:01")
      
    Range("G7") = Range("G7") + TimeValue("00:00:01")
    
    Application.OnTime interval, "timer_truck_lane_21"
     
End Sub
Sub stop_timer_truck_lane_21()
 
    Application.OnTime EarliestTime:=interval, Procedure:="timer_truck_lane_21", Schedule:=False
    

End Sub
Sub reset_truck_lane_21()

    Range("G7").Value = "00:00:00"
    
End Sub

Is there an easier/cleaner way to achieve this? Or do I have to nest 25 select cases?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
I was able to get 8 timers to work using "else" but when expanding to 12 the functionality stopped. Any help would be appreciated


VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G6")) Is Nothing Then
    
    Select Case Range("G6")
         
        Case "Empty": timer_truck_lane_21
        
        Case "Assigned": stop_timer_truck_lane_21
  
   End Select
   
Else

If Not Intersect(Target, Range("I6")) Is Nothing Then
    
    Select Case Range("I6")
         
        Case "Empty": timer_truck_lane_22
        
        Case "Assigned": stop_timer_truck_lane_22
  
   End Select
   
Else

If Not Intersect(Target, Range("K6")) Is Nothing Then
    
    Select Case Range("K6")
         
        Case "Empty": timer_truck_lane_23
        
        Case "Assigned": stop_timer_truck_lane_23
  
   End Select
 
 Else

If Not Intersect(Target, Range("M6")) Is Nothing Then
    
    Select Case Range("M6")
         
        Case "Empty": timer_truck_lane_24
        
        Case "Assigned": stop_timer_truck_lane_24
  
   End Select
 
Else

If Not Intersect(Target, Range("G17")) Is Nothing Then
    
    Select Case Range("G17")
         
        Case "Empty": timer_truck_lane_30
        
        Case "Assigned": stop_timer_truck_lane_30
  
   End Select
   
Else

If Not Intersect(Target, Range("I17")) Is Nothing Then
    
    Select Case Range("I17")
         
        Case "Empty": timer_truck_lane_31
        
        Case "Assigned": stop_timer_truck_lane_31
  
   End Select
 
 Else

If Not Intersect(Target, Range("K17")) Is Nothing Then
    
    Select Case Range("K17")
         
        Case "Empty": timer_truck_lane_32
        
        Case "Assigned": stop_timer_truck_lane_32
  
   End Select
   
Else

If Not Intersect(Target, Range("M17")) Is Nothing Then
    
    Select Case Range("M17")
         
        Case "Empty": timer_truck_lane_34
        
        Case "Assigned": stop_timer_truck_lane_34
  
   End Select
   
If Not Intersect(Target, Range("G28")) Is Nothing Then
    
    Select Case Range("G28")
         
        Case "Empty": timer_truck_lane_45
        
        Case "Assigned": stop_timer_truck_lane_45
  
   End Select
   
Else

If Not Intersect(Target, Range("I28")) Is Nothing Then
    
    Select Case Range("I28")
         
        Case "Empty": timer_truck_lane_46
        
        Case "Assigned": stop_timer_truck_lane_46
  
   End Select
 
 Else

If Not Intersect(Target, Range("K28")) Is Nothing Then
    
    Select Case Range("K28")
         
        Case "Empty": timer_truck_lane_47
        
        Case "Assigned": stop_timer_truck_lane_47
  
   End Select
   
Else

If Not Intersect(Target, Range("M28")) Is Nothing Then
    
    Select Case Range("M28")
         
        Case "Empty": timer_truck_lane_48
        
        Case "Assigned": stop_timer_truck_lane_48
  
   End Select
   
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

End Sub

Shipping Dashboard Master.xlsm
FGHIJKLMN
2
3EXPORT
4
5Truck Lane 21Truck Lane 22Truck Lane 23Truck Lane 24
6AssignedAssignedAssignedAssigned
70:00:000:00:000:00:000:00:00
8
9Team LeadQuality
10OKOK
11
12
13
14PACCAR
15
16Truck Lane 30Truck Lane 31Truck Lane 32Truck Lane 34
17AssignedAssignedAssignedAssigned
180:00:000:00:000:00:000:00:00
19
20Team LeadQuality
21OKOK
22
23
24
25OE
26
27Truck Lane 45Truck Lane 46Truck Lane 47Truck Lane 48
28AssignedAssignedAssignedAssigned
290:00:000:00:000:00:000:00:00
30
31Team LeadQuality
32OKOK
33
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G5Cell Value="Assigned"textNO
G5Cell Value=$G$6textNO
G27Cell Value="Assigned"textNO
G27Cell Value=$G$6textNO
G29Cell Value>0.00347222222222222textNO
M29Cell Value>0.00347222222222222textNO
K29Cell Value>0.00347222222222222textNO
I29Cell Value>0.00347222222222222textNO
M30:M31Cell Value=Sheet2!$D$4textNO
M30:M31Cell Value=Sheet2!$D$5textNO
I30Cell Value=Sheet2!$D$4textNO
I30Cell Value=Sheet2!$D$5textNO
G30:G31Cell Value=Sheet2!$D$4textNO
G30:G31Cell Value=Sheet2!$D$5textNO
I19Cell Value=Sheet2!$D$4textNO
I19Cell Value=Sheet2!$D$5textNO
M18Cell Value>0.00347222222222222textNO
K18Cell Value>0.00347222222222222textNO
I18Cell Value>0.00347222222222222textNO
G18Cell Value>0.00347222222222222textNO
I21Cell Value=Sheet2!$D$4textNO
I21Cell Value=Sheet2!$D$5textNO
K21Cell Value=Sheet2!$D$4textNO
K21Cell Value=Sheet2!$D$5textNO
G21Cell Value=Sheet2!$D$4textNO
G21Cell Value=Sheet2!$D$5textNO
I17Cell Value=Sheet2!$C$4textNO
I17Cell Value="Assigned"textNO
I16Cell Value="Assigned"textNO
I16Cell Value=$G$6textNO
K16Cell Value="Assigned"textNO
K16Cell Value=$G$6textNO
M16:M17Cell Value="Assigned"textNO
M16Cell Value=$G$6textNO
K17Cell Value=Sheet2!$C$4textNO
K17Cell Value="Assigned"textNO
G19,M19,M21Cell Value=Sheet2!$D$4textNO
G19,M19,M21Cell Value=Sheet2!$D$5textNO
G17,M17Cell Value=Sheet2!$C$4textNO
G16:G17Cell Value="Assigned"textNO
G16Cell Value=$G$6textNO
I8Cell Value=Sheet2!$D$4textNO
I8Cell Value=Sheet2!$D$5textNO
M7Cell Value>0.00347222222222222textNO
K7Cell Value>0.00347222222222222textNO
I7Cell Value>0.00347222222222222textNO
G7Cell Value>0.00347222222222222textNO
I10Cell Value=Sheet2!$D$4textNO
I10Cell Value=Sheet2!$D$5textNO
K10Cell Value=Sheet2!$D$4textNO
K10Cell Value=Sheet2!$D$5textNO
K32Cell Value=Sheet2!$D$4textNO
K32Cell Value=Sheet2!$D$5textNO
G32Cell Value=Sheet2!$D$4textNO
G32Cell Value=Sheet2!$D$5textNO
G10Cell Value=Sheet2!$D$4textNO
G10Cell Value=Sheet2!$D$5textNO
I6Cell Value=Sheet2!$C$4textNO
I6Cell Value="Assigned"textNO
I5Cell Value="Assigned"textNO
I5Cell Value=$G$6textNO
K5Cell Value="Assigned"textNO
K5Cell Value=$G$6textNO
M27:M28Cell Value="Assigned"textNO
M27Cell Value=$G$6textNO
K30Cell Value=Sheet2!$D$4textNO
K30Cell Value=Sheet2!$D$5textNO
K28Cell Value=Sheet2!$C$4textNO
K27:K28Cell Value="Assigned"textNO
K27Cell Value=$G$6textNO
I32Cell Value=Sheet2!$D$4textNO
I32Cell Value=Sheet2!$D$5textNO
I28Cell Value=Sheet2!$C$4textNO
I27:I28Cell Value="Assigned"textNO
I27Cell Value=$G$6textNO
G28Cell Value=Sheet2!$C$4textNO
G28Cell Value="Assigned"textNO
M5:M6Cell Value="Assigned"textNO
M5Cell Value=$G$6textNO
K6Cell Value=Sheet2!$C$4textNO
K6Cell Value="Assigned"textNO
G8,S13,Q13,K11:K13,I11:I13,G11:G13,G22:G24,I22:I24,K22:K24,Q22,S22,M8:O8,N19:O19,N30:O30,M45:O48,M58:O60,M32:O32,C8:E8,C30:E30,M10:O13,C10:E10,M22:O24,N21:O21,D19:E19,C21:E25,D32:E32,C28,C13:E15,D11:E12Cell Value=Sheet2!$D$4textNO
G8,S13,Q13,K11:K13,I11:I13,G11:G13,G22:G24,I22:I24,K22:K24,Q22,S22,M8:O8,N19:O19,N30:O30,M45:O48,M58:O60,M32:O32,C8:E8,C30:E30,M10:O13,C10:E10,M22:O24,N21:O21,D19:E19,C21:E25,D32:E32,C28,C13:E15,D11:E12Cell Value=Sheet2!$D$5textNO
G6,M6:O6,N17:O17,M28:O28,M40:O42,M53:O55,C6:E6,C10,D28:E28,C17:E17Cell Value=Sheet2!$C$4textNO
G6Cell Value="Assigned"textNO
Cells with Data Validation
CellAllowCriteria
G6List=Sheet2!$C$4:$C$5
K6List=Sheet2!$C$4:$C$5
M6List=Sheet2!$C$4:$C$5
G28List=Sheet2!$C$4:$C$5
I28List=Sheet2!$C$4:$C$5
K28List=Sheet2!$C$4:$C$5
M28List=Sheet2!$C$4:$C$5
I6List=Sheet2!$C$4:$C$5
G17List=Sheet2!$C$4:$C$5
K17List=Sheet2!$C$4:$C$5
M17List=Sheet2!$C$4:$C$5
I17List=Sheet2!$C$4:$C$5
N32:O32List=Sheet2!$D$4:$D$5
N10:N13List=Sheet2!$D$4:$D$5
N21:N24List=Sheet2!$D$4:$D$5
N19:O19List=Sheet2!$D$4:$D$5
K21:K24List=Sheet2!$D$4:$D$5
K10:K13List=Sheet2!$D$4:$D$5
G11:G13List=Sheet2!$D$4:$D$5
G22:G24List=Sheet2!$D$4:$D$5
I32List=Sheet2!$D$4:$D$5
K32List=Sheet2!$D$4:$D$5
N30:O30List=Sheet2!$D$4:$D$5
M11:M13List=Sheet2!$D$4:$D$5
I21:I24List=Sheet2!$D$4:$D$5
N8:O8List=Sheet2!$D$4:$D$5
M22:M24List=Sheet2!$D$4:$D$5
I10:I13List=Sheet2!$D$4:$D$5
 
Upvote 0
Is the cell's value either "Empty" or "Assigned" (no other possibility)?
If yes then try this:
VBA Code: 
Private Sub Worksheet_Change__(ByVal Target As Range)

    If Target.Cells.CountLarge > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("G6,I6")) Is Nothing Then 'change the reference to suit
        If Target <> "" Then
            Select Case Target.Address(0, 0)
                Case "G6": If Target = "Empty" Then timer_truck_lane_21 Else stop_timer_truck_lane_21
                Case "I6": If Target = "Empty" Then timer_truck_lane_22 Else stop_timer_truck_lane_22
                '... the rest
            End Select
        End If
    End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
set timer with a variable
Replies
2
Views
123
bbxrider
B
V
  • Solved
Workbook with Timer Macro Saving After Closing
Replies
1
Views
473
John_w
J
L
  • Question
VBA to autosave and close after a set amount of time
Replies
3
Views
174
Anthony47
A
B
  • Question
Timer causing glitches every second
Replies
3
Views
297
B-Man
B
M
  • Question
My macro —which sets a timer— stops running when I open other windows.
Replies
0
Views
492
Miguelcb
M

Forum statistics

Threads
1,215,523
Messages
6,125,321
Members
449,218
Latest member
Excel Master

Share this page

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