Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Myrange As Range, KeepOut As Range
Dim ws As Worksheet
Set KeepOut = ActiveSheet.Range("A2:C5")
Set Myrange = Intersect(Target, KeepOut)
If Myrange Is Nothing Then Exit Sub
Application.EnableEvents = False
If KeepOut.Rows.Count = 65536 And KeepOut.Columns.Count = 256 Then
On Error Resume Next
Set ws = ThisWorkbook.Sheets("KickMeTo")
On Error GoTo 0
If ws Is Nothing Then
Set ws = ThisWorkbook.Sheets.Add
ws.Name = "KickMeTo"
End If
MsgBox "Houston we have a problem" & vbNewLine & _
"You cannot select any cell in " & vbNewLine & "'" & KeepOut.Parent.Name & "'" & vbNewLine & _
"So you have been directed to a different sheet"
ws.Activate
ElseIf KeepOut.Rows.Count = 65536 Then
If KeepOut.Cells(1).Column > 1 Then
Cells(KeepOut.Cells(1).Row, KeepOut.Cells(1).Column - 1).Select
Else
Cells(KeepOut.Cells(1).Row, KeepOut.Cells(1).Column + 1).Select
End If
MsgBox "You cannot select " & KeepOut.Address(False, False) & vbNewLine & _
"You have been directed to the first free column in the protected range", vbCritical
ElseIf KeepOut.Rows.Count + KeepOut.Cells(1).Row - 1 = 65536 Then
Cells(KeepOut.Cells(1).Row - 1, 1).Select
MsgBox "You cannot select " & KeepOut.Address(False, False) & vbNewLine & _
"You have been directed to the first free cell in Column A above the protected range", vbCritical
Else
MsgBox "You cannot select " & KeepOut.Address(False, False) & vbNewLine & _
"You have been directed to the first free cell in Column A below the protected range", vbCritical
Cells(KeepOut.Rows.Count + KeepOut.Cells(1).Row, 1).Select
End If
Application.EnableEvents = True
End Sub
|