0

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
3
  • 3
    70 lines can be copy/pasted into your question. It's unlikely many people will download your workbook to see the code. Commented Apr 6, 2023 at 23:57
  • 1
    "The Question: Why is the SelectionChange subroutine re-instantiated in the middle of its code after the Change event subroutine has terminated?" Because you call Sheet1.Cells(ToRow, ToCol).Select ? If you want to make sure that doesn't trigger an event you need to use Application.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. Commented Apr 7, 2023 at 15:42
  • Thanks for the suggestion. Funny, I just found EnableEvents 5 minutes ago. While it will certainly be useful the my application, it does not explain why, when running the code in a Step-By-Step manner (F8), when the Worksheet_Change event sub terminates, the code execution jumps strait in the middle of the SelectionChange sub. That is a real puzzle for me. Any comments ? Commented Apr 7, 2023 at 16:56

1 Answer 1

1

Here's a simple example:

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Offset(1).Select
    Debug.Print "still in change event"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Debug.Print "selectionchange event"
End Sub

The output from editing a cell is:

still in change event
selectionchange event

so - triggering the Worksheet_SelectionChange doesn't immediately run the associated event code - it's "queued" until the Worksheet_Change event code exits.

Sign up to request clarification or add additional context in comments.

3 Comments

I just ran this code Dim NewLocation As Range Private Sub Worksheet_Change(ByVal Target As Range) Set NewLocation = Target.Offset(3, 3) NewLocation.Select Debug.Print "still in change event", NewLocation.Row, Target.Value End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Debug.Print "selectionchange event", Target.Row, Target.Value End Sub It reveal that VBA will process two SelectionChange events.
There are 2 selectionchange event triggers - when the user hits Enter after making the edit, and when you call NewLocation.Select
Thanks. I just edited my other related queston: stackoverflow.com/questions/75960596/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.