Multiple statements within 'SelectionChange'

[Foxtrots]

Hi Forum experts,

Hoping someone could help me here, I am trying to add three events within a SelectionChange(ByVal Target As Range), I can get two to work but when I add the third, the whole code fails.

Individually the events are:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Myrange As Range, KeepOut As Range
    Dim ws As Worksheet
    
    Set KeepOut = ActiveSheet.Range("A1:E2")
    Set Myrange = Intersect(Target, KeepOut)
    If Myrange Is Nothing Then Exit Sub

    Application.EnableEvents = False
    If KeepOut.Rows.Count + KeepOut.Cells(1).Row - 1 = 65536 Then
        Cells(KeepOut.Cells(1).Row - 1, 1).Select
    MsgBox "You cannot select the first two rows!", vbCritical
    Else
    MsgBox "You cannot select the first two rows!", vbCritical
        Cells(KeepOut.Rows.Count + KeepOut.Cells(1).Row, 1).Select
    End If
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        
    If ActiveSheet.Name <> "Sheet X" Then
    ActiveSheet.Name = "Sheet X"
    End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim Myrange1 As Range, Clear As Range
    
    Set Clear = ActiveSheet.Range("Table1")
    Set Myrange1 = Intersect(Target, Clear)
    
    If Myrange1 Is Nothing Then Exit Sub
    Application.CutCopyMode = False
    If Not Intersect(Target, Range("Table1")) Then
    Call ClipboardClear
    Exit Sub
    
    End If
    
End Sub

I have attached the file, with the code in Sheet 1 and the code I wish to add at the end of the sub

Any help would be much appreciated.

 

[Jon Peltier MVP]

You have to put all your code into a single SelectionChange event procedure.

 

[Foxtrots]

Hi Jon,

Thanks for the reply, when I put all the code together the code doesn't work at all.

In this file, I combined the code and when the first two rows are selected the code runs but after that it no longer runs also the last part of the code does not work at all (the ClipboardClear part).

Thanks

 

[p45cal]

try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Myrange As Range, KeepOut As Range
Dim ws As Worksheet
    
If Me.Name <> "Sheet X" Then ActiveSheet.Name = "Sheet X"
    
Set KeepOut = Range("A1:E2")
Set Myrange = Intersect(Target, KeepOut)
If Not Myrange Is Nothing Then
  Application.EnableEvents = False
  If KeepOut.Rows.Count + KeepOut.Cells(1).Row - 1 = 65536 Then
    Cells(KeepOut.Cells(1).Row - 1, 1).Select
    MsgBox "You cannot select the first two rows!", vbCritical
  Else
    MsgBox "You cannot select the first two rows!", vbCritical
    Cells(KeepOut.Rows.Count + KeepOut.Cells(1).Row, 1).Select
  End If
End If
    
Set Myrange = Intersect(Target, Range("Table1"))
    
If Not Myrange Is Nothing Then
  Application.CutCopyMode = False
  ClipboardClear
End If
Application.EnableEvents = True
End Sub

 

[Foxtrots]

try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Myrange As Range, KeepOut As Range
Dim ws As Worksheet
   
If Me.Name <> "Sheet X" Then ActiveSheet.Name = "Sheet X"
   
Set KeepOut = Range("A1:E2")
Set Myrange = Intersect(Target, KeepOut)
If Not Myrange Is Nothing Then
  Application.EnableEvents = False
  If KeepOut.Rows.Count + KeepOut.Cells(1).Row - 1 = 65536 Then
    Cells(KeepOut.Cells(1).Row - 1, 1).Select
    MsgBox "You cannot select the first two rows!", vbCritical
  Else
    MsgBox "You cannot select the first two rows!", vbCritical
    Cells(KeepOut.Rows.Count + KeepOut.Cells(1).Row, 1).Select
  End If
End If
   
Set Myrange = Intersect(Target, Range("Table1"))
   
If Not Myrange Is Nothing Then
  Application.CutCopyMode = False
  ClipboardClear
End If
Application.EnableEvents = True
End Sub

Hi p45cal,

Thank you very much, works like a charm, much appreciate your help.