Please forgive this lengthy explanation. I’m trying to do my best with my poor English. Linked is an EXCEL 2007 Workbook with some VBA code ( approx. 70 lines ). There is a simple worksheet on which the user is selecting a cell in column D. When selected, the window is zoomed 120% and the selected cell goes to the top left of the window. After leaving the cell the focus should go to the same row cell column 5, and the zoom goes back to 100% with cells(1,1) now in the top left corner of the sheet. This is all managed with some VBA code in the Sheet1 IDE.
The strange behavior is as such:
- First, a Cell in column 4 is selected by the user. This is handled by the SelectionChange event. Here the Zoom is changed to 120% and the selected Cell is relocated to the upper left corner of the window.
- Secondly, the user should enter a string value of any kind. This is first captured by the Change event. Here, the Cell to the right, same row, is selected by VBA code and this triggers another SelectionChange event.
- Third, the SelectionChange event include an Exit_Sub test to be canceled and return back to the Change sub where it left.
- Four, returning back to the Change sub, a Zoom is adjusted back to 100% and then the strange behavior occur. VBA here should terminate the Change subroutine. Instead it returns back to the SelectionChange and continue where the If_Then test exited the sub.
I cannot understand why VBA returns to the SelectionChange event sub. Furthermore, why return in the middle of the subroutine and not at the beginning, since that SelectionChange sub was exited with a Exit_Sub command. Exit is not Gosub?
I’m struggling to find why this strange abnormal behavior occur. There are two events taking place: Worksheet_SelectionChange and Worksheet_Change. I tried to clean up the code to its simplest form while reproducing the strange behavior. In the Worksheet_Change code the cell coordinates are captured and a series of If_Then are changing the zoom, selection and other verifications.
The Question: Why is the SelectionChange subroutine re-instantiated in the middle of its code after the Change event subroutine has terminated?
Further investigation revealed some bizarre flag values while the code is executed. I added some Debug.Print lines to try debugging this problem and found the following: Flag ToCol (long variable) records the column value of the target cell. When the Change event is just prior End_Sub, the flag value is equal to 5. When the jump to SelectionChange subroutine occur, the ToCol flag is now equal to value 4, as if the Target column is returned to prior position column 4. Notice that the code did not change ToCol value when jumping to the SelectionChange sub. And, why jumping back in the middle of the subroutine?
I googled trying to find anything resembling to my problem but no success, either on Stack_Overflow nor Google in general.
See entire VBA code on SelectionChangeBug EXCEL workbook on my Google Drive Note: The code is signed. Any attempt to run it will require to change the signature. I couldn't figure out how to remove the signature.
Option Explicit
Public SystemBuzy As Boolean
Dim FromCell As Range, ToCell As Range
Dim BuzyHere As Boolean, LargeTarget As Boolean, FromIsEmpty As Boolean, ToIsEmpty As Boolean
Dim FromRow As Long, FromCol As Long, ToRow As Long, ToCol As Long
Private Sub Worksheet_Change(ByVal Target As Range) ' Value Change
Call SetPrevious(Target)
Debug.Print " Value Changed"
BuzyHere = BuzyHere Or SystemBuzy
LargeTarget = IsRangeLarge(FromCell)
If BuzyHere Or LargeTarget Then
Exit Sub
End If
If ToRow > 2 And ToCol = 4 And Not ToIsEmpty Then
BuzyHere = True
ToCol = 5
Sheet1.Cells(ToRow, ToCol).Select
ActiveWindow.Zoom = 100
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End If
If ToRow > 2 And ToCol = 5 And MathVal(ToCell) > 0.01 Then
BuzyHere = True
'Call DoSomething Not related
End If
BuzyHere = False
End Sub
'_______________________________________________________________________
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call SetPrevious(Target)
Debug.Print " Selection Changed"
BuzyHere = BuzyHere Or SystemBuzy
LargeTarget = IsRangeLarge(FromCell)
If BuzyHere Or LargeTarget Then
Exit Sub
End If
If ToRow > 2 And ToCol = 4 Then
ActiveWindow.Zoom = 120
Application.Goto ActiveCell, Scroll:=True
Else
ActiveWindow.Zoom = 100
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End If
BuzyHere = False
End Sub
Private Sub SetPrevious(TheTarget As Range)
Set ToCell = TheTarget.Cells(1, 1)
If FromCell Is Nothing Then Set FromCell = ToCell
FromCol = FromCell.Column: FromRow = FromCell.Row
ToRow = ToCell.Row: ToCol = ToCell.Column
FromIsEmpty = ToIsEmpty
ToIsEmpty = IsEmpty(ToCell)
Set FromCell = ToCell
Debug.Print "FromRow:" & FromRow, "FromCol:" & FromCol, "ToRow:" & ToRow, "ToCol:" & ToCol;
End Sub
' ===== Here these public functions are into a different module =====
Option Explicit
Option Base 1
Public Function TrimMil(Brut As Double) As Double
TrimMil = Int(Brut * 100 + 0.5) / 100
End Function
Public Function MathVal(TheCell As Variant) As Double
If IsNumeric(TheCell.Value) Then
MathVal = TrimMil(Val(TheCell.Value))
Else
MathVal = 0
End If
End Function
Public Function IsRangeLarge(TheRange As Range) As Boolean
If TheRange Is Nothing Then
IsRangeLarge = False
Exit Function
End If
If TheRange.Rows.Count > 1 Or TheRange.Columns.Count > 1 Then
IsRangeLarge = True
Exit Function
End If
IsRangeLarge = False
End Function
Sheet1.Cells(ToRow, ToCol).Select? If you want to make sure that doesn't trigger an event you need to useApplication.EnableEvents = False(don't forget to re-enable after). FYI so many Globals make your code really difficult to follow and can lead to subtle bugs.