LordDragon
08-14-2015, 11:16 AM
Greetings,
I have a small problem with my workbook and am looking for any help I can get.
There are 16 Worksheets, most of which have a Worksheet_Change() event.
Private Sub Worksheet_Change(ByVal Target As Range)
'Allows the Yes/No fields to accept partial entries and lower case.
Application.ScreenUpdating = False
'Declare variables
Dim lngRow As Long
'Determine how many rows there are
lngRow = Range("A" & Rows.Count).End(xlUp).Row
'Set the acceptance to include lower case and single letters.
For Each cell In Range("A3:A" & lngRow)
If cell = "y" Then cell.Value = strYes
If cell = "yes" Then cell.Value = strYes
If cell = "n" Then cell.Value = strNo
If cell = "no" Then cell.Value = strNo
Next cell
Application.ScreenUpdating = True
End Sub
There is also a Workbook_Activate() event and 8 Modules. Everything seemed to be working fine, until I made a small change to one of the Worksheet_Change events that simply looked for a value in one cell and if it existed, then it changed the value of two other cells.
If ActiveWorkbook.Worksheets("EDR").Range("A65").Value = "Yes" Then
With ActiveWorkbook.Worksheets("EDR")
.Range("A43").Value = "Yes"
.Range("E43").Value = 1
End With
End If
I don't see how it was causing a problem. But I kept getting an "Out of Stack Space" message.
I did some research and I found this thread (http://www.vbaexpress.com/forum/showthread.php?50830-Worksheet-sheetchange-out-of-stack)posted by SamT (http://www.vbaexpress.com/forum/member.php?6494-SamT) last year to someone else with the same problem. The thread was not marked solved and the original poster never posted anything after SamT. So I don't know if the suggestion worked for him, but I gave it a try with my workbook.
Private Sub Worksheet_Change(ByVal Target As Range, ByVal Sh As Object)
'Allows the Yes/No fields to accept partial entries and lower case.
'Provide a way to watch a secific range for changes (instead of the entire sheet).
Dim RangeToWatch As Range
'Check to see if this sub is already running.
Static MeAlreadyRunning As Boolean
If MeAlreadyRunning Then Exit Sub
MeAlreadyRunning = True
'Check to make sure that this is the sheet that is being watched by the sub.
If Not Sh.Name = "Master Parts List" Then Exit Sub
'Set the range to watch.
Set RangeToWatch = Sh.Range("A3", "A1958")
If Intersect(Target, RangeToWatch) Is Nothing Then Exit Sub
'Disable application level events.
With Application
.EnableEvents = False
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
'Declare variables.
Dim lngRow As Long
'Determine how many rows there are.
lngRow = Range("A" & Rows.Count).End(xlUp).Row
'Set the acceptance to include lower case and single letters.
For Each cell In Range("A3" & lngRow)
If cell = "y" Then cell.Value = strYes
If cell = "yes" Then cell.Value = strYes
If cell = "n" Then cell.Value = strNo
If cell = "no" Then cell.Value = strNo
Next cell
'Enable the application level events.
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
'Turn off the sub is running event.
MeAlreadyRunning = False
End Sub
Now I'm getting the following message box.
14166
I know my workbook has a lot of code and is trying to do a lot of things, but I've been trying to be careful to not have any one sub try to do too much. Or to have more than one sub doing anything at one time. So I'm looking for any methods I can use to speed up my code and make it work smoother.
I read about using Workbook level constants for things that won't change, so I removed all the duplicated variables and put them in a module as Workbook level constants (unless their value actually changes). This did speed it up a little, but apparently there is still a long way to go.
'Decalre the Constants for the entire workbook.
Public Const strYes As String = "Yes"
Public Const strNo As String = "No"
Public Const strVolt1 As String = "110V"
Public Const strVolt2 As String = "220V"
Public Const strPrbR As String = "Radar"
Public Const strPrbM As String = "Mud"
Public Const strPrbB As String = "Both"
I have also attached a copy of the entire workbook here. This copy does NOT include the code I got from SamT, or my code to check for that value. Basically, it's the last working version before attempting these changes.
I'm not dead set on using any particular method, I'm just looking for guidance on how to make my code smoother. I have a list of requirements for this project and am trying to meet them all. However, the more of them I meet, the slower it runs and the more it seems to want to crash.
I have a small problem with my workbook and am looking for any help I can get.
There are 16 Worksheets, most of which have a Worksheet_Change() event.
Private Sub Worksheet_Change(ByVal Target As Range)
'Allows the Yes/No fields to accept partial entries and lower case.
Application.ScreenUpdating = False
'Declare variables
Dim lngRow As Long
'Determine how many rows there are
lngRow = Range("A" & Rows.Count).End(xlUp).Row
'Set the acceptance to include lower case and single letters.
For Each cell In Range("A3:A" & lngRow)
If cell = "y" Then cell.Value = strYes
If cell = "yes" Then cell.Value = strYes
If cell = "n" Then cell.Value = strNo
If cell = "no" Then cell.Value = strNo
Next cell
Application.ScreenUpdating = True
End Sub
There is also a Workbook_Activate() event and 8 Modules. Everything seemed to be working fine, until I made a small change to one of the Worksheet_Change events that simply looked for a value in one cell and if it existed, then it changed the value of two other cells.
If ActiveWorkbook.Worksheets("EDR").Range("A65").Value = "Yes" Then
With ActiveWorkbook.Worksheets("EDR")
.Range("A43").Value = "Yes"
.Range("E43").Value = 1
End With
End If
I don't see how it was causing a problem. But I kept getting an "Out of Stack Space" message.
I did some research and I found this thread (http://www.vbaexpress.com/forum/showthread.php?50830-Worksheet-sheetchange-out-of-stack)posted by SamT (http://www.vbaexpress.com/forum/member.php?6494-SamT) last year to someone else with the same problem. The thread was not marked solved and the original poster never posted anything after SamT. So I don't know if the suggestion worked for him, but I gave it a try with my workbook.
Private Sub Worksheet_Change(ByVal Target As Range, ByVal Sh As Object)
'Allows the Yes/No fields to accept partial entries and lower case.
'Provide a way to watch a secific range for changes (instead of the entire sheet).
Dim RangeToWatch As Range
'Check to see if this sub is already running.
Static MeAlreadyRunning As Boolean
If MeAlreadyRunning Then Exit Sub
MeAlreadyRunning = True
'Check to make sure that this is the sheet that is being watched by the sub.
If Not Sh.Name = "Master Parts List" Then Exit Sub
'Set the range to watch.
Set RangeToWatch = Sh.Range("A3", "A1958")
If Intersect(Target, RangeToWatch) Is Nothing Then Exit Sub
'Disable application level events.
With Application
.EnableEvents = False
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
'Declare variables.
Dim lngRow As Long
'Determine how many rows there are.
lngRow = Range("A" & Rows.Count).End(xlUp).Row
'Set the acceptance to include lower case and single letters.
For Each cell In Range("A3" & lngRow)
If cell = "y" Then cell.Value = strYes
If cell = "yes" Then cell.Value = strYes
If cell = "n" Then cell.Value = strNo
If cell = "no" Then cell.Value = strNo
Next cell
'Enable the application level events.
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
'Turn off the sub is running event.
MeAlreadyRunning = False
End Sub
Now I'm getting the following message box.
14166
I know my workbook has a lot of code and is trying to do a lot of things, but I've been trying to be careful to not have any one sub try to do too much. Or to have more than one sub doing anything at one time. So I'm looking for any methods I can use to speed up my code and make it work smoother.
I read about using Workbook level constants for things that won't change, so I removed all the duplicated variables and put them in a module as Workbook level constants (unless their value actually changes). This did speed it up a little, but apparently there is still a long way to go.
'Decalre the Constants for the entire workbook.
Public Const strYes As String = "Yes"
Public Const strNo As String = "No"
Public Const strVolt1 As String = "110V"
Public Const strVolt2 As String = "220V"
Public Const strPrbR As String = "Radar"
Public Const strPrbM As String = "Mud"
Public Const strPrbB As String = "Both"
I have also attached a copy of the entire workbook here. This copy does NOT include the code I got from SamT, or my code to check for that value. Basically, it's the last working version before attempting these changes.
I'm not dead set on using any particular method, I'm just looking for guidance on how to make my code smoother. I have a list of requirements for this project and am trying to meet them all. However, the more of them I meet, the slower it runs and the more it seems to want to crash.