I have several subs that run in a chain. If the first sub meets a certain criteria then it skips to the next sub and so on. There are five subs in all.
The problem: When it finishes running the last sub, in starts backtracking to the previous sub until it gets back to the first sub. This creates a long running procedure. (does this make any sense to anyone?)
What I need it to do when it runs the last sub is to simply stop running all of the subs instead of backtracking.
Let me post the code to hopefully clear things up. Maybe you can even give me some ideas on how to clean this code up!!! (p.s. this code is only a begining, there will be more added to it as you will see by the first "if" statement) Here it is: Thanks in advance to anyone that can help.
Private Sub cmdSearch_Click()
Dim x As Integer
Dim i As Integer
If cmbBrand.Value = "Etch Art" Then
Workbooks("Etch_Art_Inventory.xls").Activate
End If
If txtItem1.Value = "" Then
run2
End If
Application.ScreenUpdating = False
x = 3
For i = 3 To 50
Range("b" & x).Select
If Range("b" & x) = txtItem1.Value Then
lblOnhand1.Caption = Range("k" & x)
lblOnorder1.Caption = Range("l" & x)
lblAvailable1.Caption = Range("m" & x)
run2
Else
x = x + 1
End If
Next i
Workbooks("Tools_Inventory.xls").Activate
End Sub
Private Sub run2()
If txtItem2.Value = "" Then
run3
End If
Dim x As Integer
Dim i As Integer
Application.ScreenUpdating = False
x = 3
For i = 3 To 50
If txtItem2.Value = "" Then
Exit Sub
End If
Range("b" & x).Select
If Range("b" & x) = txtItem2.Value Then
lblOnhand2.Caption = Range("k" & x)
lblOnorder2.Caption = Range("l" & x)
lblAvailable2.Caption = Range("m" & x)
run3
Else
x = x + 1
End If
Next i
Workbooks("Tools_Inventory.xls").Activate
End Sub
Private Sub run3()
If txtItem3.Value = "" Then
run4
End If
Dim x As Integer
Dim i As Integer
Application.ScreenUpdating = False
x = 3
For i = 3 To 50
Range("b" & x).Select
If Range("b" & x) = txtItem3.Value Then
lblOnhand3.Caption = Range("k" & x)
lblOnorder3.Caption = Range("l" & x)
lblAvailable3.Caption = Range("m" & x)
run4
Else
x = x + 1
End If
Next i
Workbooks("Tools_Inventory.xls").Activate
End Sub
Private Sub run4()
If txtItem4.Value = "" Then
run5
End If
Dim x As Integer
Dim i As Integer
Application.ScreenUpdating = False
x = 3
For i = 3 To 50
Range("b" & x).Select
If Range("b" & x) = txtItem4.Value Then
lblOnhand4.Caption = Range("k" & x)
lblOnorder4.Caption = Range("l" & x)
lblAvailable4.Caption = Range("m" & x)
run5
Else
x = x + 1
End If
Next i
Workbooks("Tools_Inventory.xls").Activate
End Sub
Private Sub run5()
If txtItem5.Value = "" Then
Exit Sub
End If
Dim x As Integer
Dim i As Integer
Application.ScreenUpdating = False
x = 3
For i = 3 To 50
Range("b" & x).Select
If Range("b" & x) = txtItem5.Value Then
lblOnhand5.Caption = Range("k" & x)
lblOnorder5.Caption = Range("l" & x)
lblAvailable5.Caption = Range("m" & x)
Workbooks("Tools_Inventory.xls").Activate
Exit Sub
Else
x = x + 1
End If
Next i
Workbooks("Tools_Inventory.xls").Activate
End Sub
The problem: When it finishes running the last sub, in starts backtracking to the previous sub until it gets back to the first sub. This creates a long running procedure. (does this make any sense to anyone?)
What I need it to do when it runs the last sub is to simply stop running all of the subs instead of backtracking.
Let me post the code to hopefully clear things up. Maybe you can even give me some ideas on how to clean this code up!!! (p.s. this code is only a begining, there will be more added to it as you will see by the first "if" statement) Here it is: Thanks in advance to anyone that can help.
Private Sub cmdSearch_Click()
Dim x As Integer
Dim i As Integer
If cmbBrand.Value = "Etch Art" Then
Workbooks("Etch_Art_Inventory.xls").Activate
End If
If txtItem1.Value = "" Then
run2
End If
Application.ScreenUpdating = False
x = 3
For i = 3 To 50
Range("b" & x).Select
If Range("b" & x) = txtItem1.Value Then
lblOnhand1.Caption = Range("k" & x)
lblOnorder1.Caption = Range("l" & x)
lblAvailable1.Caption = Range("m" & x)
run2
Else
x = x + 1
End If
Next i
Workbooks("Tools_Inventory.xls").Activate
End Sub
Private Sub run2()
If txtItem2.Value = "" Then
run3
End If
Dim x As Integer
Dim i As Integer
Application.ScreenUpdating = False
x = 3
For i = 3 To 50
If txtItem2.Value = "" Then
Exit Sub
End If
Range("b" & x).Select
If Range("b" & x) = txtItem2.Value Then
lblOnhand2.Caption = Range("k" & x)
lblOnorder2.Caption = Range("l" & x)
lblAvailable2.Caption = Range("m" & x)
run3
Else
x = x + 1
End If
Next i
Workbooks("Tools_Inventory.xls").Activate
End Sub
Private Sub run3()
If txtItem3.Value = "" Then
run4
End If
Dim x As Integer
Dim i As Integer
Application.ScreenUpdating = False
x = 3
For i = 3 To 50
Range("b" & x).Select
If Range("b" & x) = txtItem3.Value Then
lblOnhand3.Caption = Range("k" & x)
lblOnorder3.Caption = Range("l" & x)
lblAvailable3.Caption = Range("m" & x)
run4
Else
x = x + 1
End If
Next i
Workbooks("Tools_Inventory.xls").Activate
End Sub
Private Sub run4()
If txtItem4.Value = "" Then
run5
End If
Dim x As Integer
Dim i As Integer
Application.ScreenUpdating = False
x = 3
For i = 3 To 50
Range("b" & x).Select
If Range("b" & x) = txtItem4.Value Then
lblOnhand4.Caption = Range("k" & x)
lblOnorder4.Caption = Range("l" & x)
lblAvailable4.Caption = Range("m" & x)
run5
Else
x = x + 1
End If
Next i
Workbooks("Tools_Inventory.xls").Activate
End Sub
Private Sub run5()
If txtItem5.Value = "" Then
Exit Sub
End If
Dim x As Integer
Dim i As Integer
Application.ScreenUpdating = False
x = 3
For i = 3 To 50
Range("b" & x).Select
If Range("b" & x) = txtItem5.Value Then
lblOnhand5.Caption = Range("k" & x)
lblOnorder5.Caption = Range("l" & x)
lblAvailable5.Caption = Range("m" & x)
Workbooks("Tools_Inventory.xls").Activate
Exit Sub
Else
x = x + 1
End If
Next i
Workbooks("Tools_Inventory.xls").Activate
End Sub