Excel 2013 VBA SelectionChange事件

我正在使用Excel 2013，并且遇到SelectionChange Event的问题，这使我发疯。我想知道是否有人可以帮助我。我已经呆了这么久（一个星期！），我可能会错过一些容易被人看见的东西。下面的代码在Excel 2007和2010中可以正常工作。

最初，当您激活页面时，代码将立即将您返回到调用页面（在此菜单主菜单中）。该代码的第二次正常工作。在某个阶段，只需选择新的单元格，它将使您返回到另一页。以我为例，它带我回到“主菜单”页面。

我的问题被调用了三个例程（1）选择更改事件，（2）SetHighlightRows1（ByVal目标为范围），（3）MinRowsHeight_ActiveCell

感谢您的协助/ sglxl

Option Explicit

Private Declare Function LockWindowUpdate Lib "USER32" _
                                          (ByVal hwndLock As Long) As Long
----------------------------------------------------------------------------------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    Dim Msg, Style, Title, Response

    ' Similar to ScreenUpdating but this locks the Shapes from continuous Flickering
    LockWindowUpdate Application.hWnd

    ' Initialise
    ActiveSheet.Unprotect Password:=pw
    Application.ScreenUpdating = False

    ' Highlight selected rows
    Call SetHighlightRows1(ActiveCell)

    ' Reset ScreenUpdating to False
    Application.ScreenUpdating = False

    ' Headings in all sheets set to Max 53
    ' Build Message
    Msg = "You cannot access this area!"
    Style = vbOKOnly + vbInformation
    Title = "Company Secretary"

    On Error Resume Next

    ' Limit access area so that row heights remain constant
    If Not (Intersect(Target, Range("A1:O8")) Is Nothing) Or Not (Intersect(Target, Range("A1011:O1011")) Is Nothing) Then
        Response = MsgBox(Msg, Style, Title)
        Range("ptrCursor").Select
        GoTo CleanUp:
    Else
        Target.Select
    End If

    ' Set Row Height
    ' EnableEvents set to TRUE
    Call MinRowsHeight_ActiveCell

    ' Unprotect AkSht as MinRowsHeight_ActiveCell set Protect = True
    ActiveSheet.Unprotect Password:=pw

    Rows(3).EntireRow.RowHeight = 53

CleanUp:

    ' CleanUp
    ActiveSheet.Protect Password:=pw, AllowFiltering:=True
    Application.ScreenUpdating = True

    ' Unlock the window updating in the end by passing a null to the LockWindowUpdate API function.
    LockWindowUpdate 0

End Sub

' ---------------------------------------------------------------------------------

'----------------------------------------------------------------
Public Sub SetHighlightRows1(ByVal Target As Range)
'----------------------------------------------------------------

    Dim MyRng As Range
    Dim TargetCol
    Dim TargetRow
    Dim BeginColumn As Long
    Dim EndColumn As Long
    Dim BeginRow As Long

    ' Initialise
    ' Disable Events before SelectionChange occurs. There may be other events that
    ' may Trigger the SelectionChange
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    On Error Resume Next

    ' Define Row and Column ranges to make routine dynamic
    TargetCol = Target.Column
    TargetRow = Target.Row
    BeginColumn = ActiveSheet.Range("ptrColumnBegin").Column
    EndColumn = ActiveSheet.Range("ptrColumnEnd").Column - 1
    BeginRow = ActiveSheet.Range("ptrBeginCell").Row

    ' ***** Set Range parameters *****
    Set MyRng = Range(Cells(TargetRow, BeginColumn), Cells(TargetRow, EndColumn))

    ' Initialise
    ' Disable Events before SelectionChange occurs. There may be other events that
    ' may Trigger the SelectionChange
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    On Error GoTo CleanUp

    If TargetCol > EndColumn Then GoTo CleanUp

    ' ***** Set range limits *****
    ' ActiveSheet.Range("ptrEndCell").Row - 1 - This will ensure that if the user inserts additionalRows
    ' The highlighter bar will follow to include the additional Rows
    If TargetRow < BeginRow Or TargetRow > ActiveSheet.Range("ptrEndCell").Row - 1 Then GoTo CleanUp
    ' ***** End Range Limits *****

    Application.Cells.FormatConditions.Delete

    ' Highlight Columns
    With MyRng
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
        With .FormatConditions(1).Font
            .Bold = True
            .Italic = False
            ' .Color = RGB(192, 0, 0) ' Seaxl Red
            .Color = RGB(83, 141, 213)    ' Dark Blue
            .Color = RGB(0, 51, 204)    ' Dark Blue
        End With
        '                .FormatConditions(1).Interior.Color = RGB(225, 234, 204)    ' Green
        '                .FormatConditions(1).Interior.Color = RGB(220, 230, 241)    ' Light Blue
        .FormatConditions(1).Interior.Color = RGB(248, 248, 248)    ' Light Grey
    End With

CleanUp:

    ' CleanUp
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

' ---------------------------------------------------------------------------------

Sub MinRowsHeight_ActiveCell()

    'Initialise
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:=pw
    Application.EnableEvents = False

    ' Only Visible Cells are set to min height
    ActiveSheet.Range("tblDatabaseSort").SpecialCells(xlCellTypeVisible).RowHeight = 22.5

    ' Adjust only the ActiveCell Row height to AutoFit
    ActiveCell.EntireRow.AutoFit
    If ActiveCell.EntireRow.RowHeight < 22.5 Then
        ActiveCell.EntireRow.RowHeight = 22.5
    End If

    ' CleanUp
    Application.ScreenUpdating = True
    ActiveSheet.Protect Password:=pw
    Application.EnableEvents = True

End Sub