biocentrism
Board Regular
- Joined
- Jul 28, 2014
- Messages
- 187
I have a spreadsheet where I can enter a date in either of 2 ways:
1) manually type in a date;
2) click an ActiveX Spin Up and Spin Down button which triggers an event to increment up or down by 1 day.
If I change the date manually, it produces the desired result of triggering a Worksheet_Change Event which causes the spreadsheet to do some computations and display the resulting data and some charts.
However, if I increment the date using the Spinner Button, it triggers 20+ Worksheet_Change Events which does the calculations 20+ times.
I have spent 2 days trying to debug this line by line and cannot find any reason that this would be happening. The triggering event is the same, i.e. the value in a cell gets incremented by 1. How can the spinner button make such a difference?
Is there a known bug with these controls which could produce such drastically different results?
Here is the code for the Spin Up Button:
1) manually type in a date;
2) click an ActiveX Spin Up and Spin Down button which triggers an event to increment up or down by 1 day.
If I change the date manually, it produces the desired result of triggering a Worksheet_Change Event which causes the spreadsheet to do some computations and display the resulting data and some charts.
However, if I increment the date using the Spinner Button, it triggers 20+ Worksheet_Change Events which does the calculations 20+ times.
I have spent 2 days trying to debug this line by line and cannot find any reason that this would be happening. The triggering event is the same, i.e. the value in a cell gets incremented by 1. How can the spinner button make such a difference?
Is there a known bug with these controls which could produce such drastically different results?
Here is the code for the Spin Up Button:
Code:
Private Sub SpinButtonDate_SpinUp()
Debug.Print "SUB SPIN UP"
Dim TargetSelection As Range
Dim Col As Long
Dim Row As Long
'Application Settings
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim rowNewValidDate As Long
Dim NewValidDate As Date
'Validates the Date and then Increment Date to Next ValidDate
rowNewValidDate = Application.Match(CLng(Range("B1").Value), WSLists.Range("C:C"), 0) + 1
NewValidDate = WSLists.Cells(rowNewValidDate, 3).Value
Range("B1").Value = NewValidDate
'Update Dashboard
Call UpdateDashboard(DashboardRow)
End If
ExitSub:
'Enable Settings
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub