Prevent User from Switching Sheets

[gallen]

I have code in the Worksheet_Change event to select a cell after another one has been changed.

I have very basic error handling code set up.

A user has just filled in a cell, then without losing focus from the cell has changed to a different worksheet.

An error is produced in the Worksheet_Change event yet the 'On Error goto errHandle' hasn't done it's job as the code stops on the line of execution not in the error handling error.

The error (Select Method of Range Class Failed) is raised every time a user changes sheet with out losing cell focus within the calling sheet, first.

My Code is below:

Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Cells.Count > 1 Then Exit Sub
    
    On erorr GoTo errHandle
    Enable False
        
    Select Case Target.Column
        Case Info.Code 'Supplier Ref
            Target = UCase(Target)
            GetDetails Target
            SetValidation Target
            SetBorder Range("A" & Target.Row & ":M" & Target.Row)
            Target.Offset(0, 2).Select
            
        Case Info.OrderType
            Target.Offset(0, Info.DelTo - Info.OrderType).Select
            Target = UCase(Target)
        Case Info.DelTo
            Target.Offset(0, Info.Sup1 - Info.DelTo).Select
            Target = UCase(Target)
        Case Info.Sup1
            Target.Offset(0, Info.Buyer - Info.Sup1).Select
            Target = UCase(Target)
        Case Info.Buyer
            Target.Offset(1, Info.Code - Info.Buyer).Select
        Case Else
            Target = UCase(Target)
        
    End Select



        
Enable True
Exit Sub
errHandle:
    MsgBox Err.Description
    Enable True
End Sub

Is it possible to prevent the user from switching sheets?

Thank you

 

[gallen]

I've fixed it. I added code to check if the active sheet name was the same as the sheet name for the 'WorkSheet_Change' sub.

If it isn't, I reactivated, ran the code then activated the sheet the user was trying to reach in the first place:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub


Dim sActiveSheet As String


    sActiveSheet = ActiveSheet.Name
    If sActiveSheet <> Sheet1.Name Then Sheet1.Activate 'If user activated a different sheet then re-activate the original sheet
    
    Enable False
    
    Select Case Target.Column
        Case Info.Code 'Supplier Ref
            Target = UCase(Target)
            GetDetails Target
            SetValidation Target
            SetBorder Range("A" & Target.Row & ":M" & Target.Row)
            Target.Offset(0, 2).Select
            
        Case Info.OrderType
            Target.Offset(0, Info.DelTo - Info.OrderType).Select
            Target = UCase(Target)
        Case Info.DelTo
            Target.Offset(0, Info.Sup1 - Info.DelTo).Select
            Target = UCase(Target)
        Case Info.Sup1
            Target.Offset(0, Info.Buyer - Info.Sup1).Select
            Target = UCase(Target)
        Case Info.Buyer
            Target.Offset(1, Info.Code - Info.Buyer).Select
        Case Else
            Target = UCase(Target)
        
    End Select
    If sActiveSheet <> Sheet1.Name Then Sheets(sActiveSheet).Activate 'Now 'reactivate the sheet the user wanted in the first place
Enable True
Exit Sub
errHandle:
    MsgBox Err.Description
    Enable True
End Sub