I'm experiencing around a 4 second delay between when I click the ActiveX Spin Button and when the workbook calculates. I'm trying to figure out what is causing the delay. Some background, I have a dashboard with multiple check boxes, when a checkbox is selected a "Sliders" worksheet updates with the cell reference that should be changed by the specific Spin Button. The current code I'm using to accomplish this is:
Private Sub SpinButton1_Change()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Dim rng As Range
Set ws = wb.Sheets("Sliders")
Set rng = ws.Range("indirect(c4)")
rng.Value = SpinButton1.Value
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
I'm working in a .xlsb file with Excel 2016 on my local hard drive and the file is 4.2MB. I've also set the delay property of the spin button to 1. Any insight as to what could be causing this delay or what other information would be helpful?
Private Sub SpinButton1_Change()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Dim rng As Range
Set ws = wb.Sheets("Sliders")
Set rng = ws.Range("indirect(c4)")
rng.Value = SpinButton1.Value
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
I'm working in a .xlsb file with Excel 2016 on my local hard drive and the file is 4.2MB. I've also set the delay property of the spin button to 1. Any insight as to what could be causing this delay or what other information would be helpful?
