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

 

[mole999]

are you having any variables that stay populated that might drag the sheets to change or could you and a specific sheet reference to the spin button so regardless it uses the same page

 

[bisel]

are you having any variables that stay populated that might drag the sheets to change or could you and a specific sheet reference to the spin button so regardless it uses the same page

Not that I can determine. To that end, I have tried inserting the statement:
Sheet19.select (where sheet19 is the sheet number). This has no effect. I also tried Sheet19.Activate and no difference.

For the spin button event, I am using the change event. Among the other event types, two are spin up and spin down event. In your experience, is it better to use the spin up and spin down events versus the single change event?

Steve

 

[bisel]

I answered my own question ... at least it seems so.

I replaced the spin button change event with a spin up and a spin down event. At first blush, the problem seems to have resolved itself. It would be worth knowing why this seems to work, but for my purposes, it does seem to work.

Steve

 

[bisel]

I answered my own question ... at least it seems so.

I replaced the spin button change event with a spin up and a spin down event. At first blush, the problem seems to have resolved itself. It would be worth knowing why this seems to work, but for my purposes, it does seem to work.

Steve

Spoke too soon. Still got same event. What I am finding is that if I first change a cell in the key cell range that triggers the event the macro ...
Private Static Sub Worksheet_SelectionChange(ByVal Target As Range). If I then click a spin button, it then changes the active sheet to a different worksheet. I do not see any reason for this behavior.

I have now inserted the following statements ...

Private Sub SpinButton_SpinUp()

Application.ScreenUpdating = False
Application.EnableEvents = False

     .... vba code does stuff  ....

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Similar code for SpinDown event

First blush, seems to work by adding the EnableEvents = False / True

Steve

 

[mikerickson]

You click on a SpinButton on the ActiveSheet, and then a different sheet becomes active.
I suspect something in the "vba code does stuff".
Have you tried inserting a Worksheet_Activate event in the ThisWorkbook module, a breakpoint in your SpinUp routine and then stepping?

 

[bisel]

You click on a SpinButton on the ActiveSheet, and then a different sheet becomes active.
I suspect something in the "vba code does stuff".
Have you tried inserting a Worksheet_Activate event in the ThisWorkbook module, a breakpoint in your SpinUp routine and then stepping?

Yes. Did all that. Put in break points, added msg boxes to examine value of the variables and the name of the current active sheet, etc.

The enable events false/true statements are holding up so far.

 

[mikerickson]

The idea would be to leave events enabled in a pre-error state and sneak up on the line that activates the other sheet, as you step through the code, the Activate event (Dim i As Long:i = 1 would be sufficient) would be your flag as to what line of code activated the other sheet.

 

[bisel]

The idea would be to leave events enabled in a pre-error state and sneak up on the line that activates the other sheet, as you step through the code, the Activate event (Dim i As Long:i = 1 would be sufficient) would be your flag as to what line of code activated the other sheet.

Not sure how to finitely sneak up on the line as this only occurs with a spin button on spin up or spin down. Without actually "spinning" the button, trying to step through the VBA does not work for me. Also, this only occurs when I first change a cell's value using the Worksheet_SelectionChange(ByVal Target As Range) routine. Where the user clicks on cell, and the routine first determines if the cell is the keycell range and then changes the value based on the current value. For example, if a cell, call it "cellxyz" is part of the keycells named range, then the VBA will select an action depending on the cell's value. For example, if the current value is YES then clicking on it toggles it to NO.

Once the above had been performed, then spinning button causes the active sheet to change. But there is no code in the VBA to do this.

If one does not perform the Worksheet_SelectionChange(ByVal Target As Range) to change a cell's value, then the spin button works as expected.

But, inserting the Application.EnableEvents = False statement resolves the issue.

I cannot explain why this works, but I saw in another posting that by disabling events has the efffect of preventing unexpected action occurring as a result of a change event on the worksheet. It seemed to me that was what was happened ... that is, I was triggering a change event on the worksheet and something unexpected was happening. So, I disabled events at the start of the macro and enabled at the end.

Regards,

Steve

 

[mole999]

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