Strange Behavior of Spinner Up/Down Buttons, Multiple Event Firings

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:
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,223,270
Messages
6,171,102
Members
452,379
Latest member
IainTru

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