Unexplained Change of the Active Sheet

[bisel]

In my workbook, I have VBA code on a worksheet that includes some spin buttons that increment or decrement a cell's value. Most of the time these spin buttons work as expected without any problems. I also have some code that is looking to see if a specific cell within a range has changed its value and then run some code if it does. And, this is working as expected.

I have come across a problem that I cannot explain.

For some reason, when I execute the spin button, the active sheet changes. This happens only occasionally. I do not have any code to either select or activate that sheet, it just happens and not all the time. I have been trying to debug this by inserting into the various macros "msgbox activesheet.name" just to see what the current name of the active sheet ... and as I stated, sometimes it is as I would have expected and sometimes the active sheet changes.

Has anyone experienced this behavior? Do have ideas on what I might look for to prevent this?

Thanks,

Steve

 

[bisel]

daft ideas, how big are the spin buttons, is there a possibility that sometime you touch the worksheet and trigger a different sequence

There is no possibility of that.

By the way ... why do you believe that inserting Application.EnableEvents = False at the beginning of the macro and then follow up with Application.EnableEvents = True at the end to be a bad idea?

Thanks,

Steve

 

[bisel]

Thought I would update this ...

Everything is now working fine. The spin buttons are working as I expect and there are not problems. Hopefully, this remains so and I can consider this closed.

Thanks for everyone's help.

 

[mole999]

so what did you change to make it work

 

[bisel]

What seems to have resolved the problem is the inclusion of ...

Application.ScreenUpdating = False​

Application.EnableEvents = False​

... at the start of the spin button event and then ensuring that they are reversed at the end of the event.

Here is the actual code ...

Private Sub SpinButton1_SpinDown()

On Error GoTo errorhandler

Application.ScreenUpdating = False
Application.EnableEvents = False

Range("annual_yrs_incr1").Value = Range("annual_yrs_incr1").Value - 1

' Check if autoadjust is turned on
If Sheet19.Range("autoadjust").Value = True Then
        Range("hoa_dues_start2").Value = Range("incr2_earlystart").Value
        Range("hoa_dues_start3").Value = Range("incr3_earlystart").Value
Else
End If

Sheet19.Select
Range("annual_yrs_incr1").Select

errorhandler:

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

I used both the spin up and spin down events with the only difference is that one increments a value and the other decrements the value. At one point I was using the change event and then used the property of small change in the spin button property. I moved over to this to make sure it was not the cause and just did not change back.

Steve

 

[mole999]

Enable Events TRUE on a worksheet Change Event causes the code to shift to the change events, do that and step back. If set to false then it carries on in the macro as displayed. You can see that if you trap the module F9 and trigger the event, stepping through with F8