Accommodate two functions in Sub Workbook_SheetBeforeRightClick

[Vincent88]

Hi, What should be modified to run the two functions in workbook module.

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

  Select Case Sh.Name
    Case "Agents"
      Exit Sub
    Case Else
  End Select
 
  'Insert Screenshot and send Email in Column E
  If Target.Column <> 5 Or Application.CountA(Cells(Target.Row, 1).Resize(, 2)) < 2 Then Exit Sub
  Cancel = True
  Call Module3.SelectOLE3
 
 
  'Copy REQ reference and Open Remedy in Column D
  If Target.Column <> 4 Or Application.CountA(Cells(Target.Row, 1).Resize(, 2)) < 2 Then Exit Sub
  Cancel = True
  Call Module6.CopyREQ6
    
 
End Sub

 

[Peter_SSs]

Give this a try with a copy of your workbook.

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

  If Sh.Name <> "Agents" Then
 
    'Insert Screenshot and send Email in Column E
    If Target.Column = 5 And Application.CountA(Cells(Target.Row, 1).Resize(, 2)) = 2 Then
      Cancel = True
      Call Module3.SelectOLE3
    
    'Copy REQ reference and Open Remedy in Column D
    ElseIf Target.Column = 4 And Application.CountA(Cells(Target.Row, 1).Resize(, 2)) = 2 Then
      Cancel = True
      Call Module6.CopyREQ6
    End If
  
  End If
    
End Sub

 

[Vincent88]

Hi Peter_SSs, I modified yours and it works. Is there any way to make it more neatly.

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

  Select Case Sh.Name
    Case "Agents"
      Exit Sub
    Case Else
  End Select
 
  If Target.Column <> 4 And Target.Column <> 5 Or Application.CountA(Cells(Target.Row, 1).Resize(, 2)) < 2 Then Exit Sub
 
  'Insert Screenshot and send Email in Column E
  If Target.Column = 5 Or Application.CountA(Cells(Target.Row, 1).Resize(, 2)) < 2 Then
  Cancel = True
  Call Module3.SelectOLE3
 
  End If
 
  'Copy REQ reference and Open Remedy in Column D
  If Target.Column = 4 Or Application.CountA(Cells(Target.Row, 1).Resize(, 2)) < 2 Then
  Cancel = True
  Call Module6.CopyREQ6
    
  End If
 
 
End Sub

 

[Marc L]

Hi,​

​

remove Select Case like Peter did and remove the CountA part within each Target.Colum codeline …​

​

Using Select Case for the Target.Column should be smart …​

 

[Marc L]

Correction for previous post : Using Select Case for the Target.Column should be smart …​

​

As after the first If you know the column is 4 or 5 so you can apply the Cancel before the first If Target.Column …​

 

[Marc L]

According to the last 3 posts :​

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    If Sh.Name <> "Agents" And Target.Column > 3 And Target.Column < 6 Then _
        If Application.CountA(Cells(Target.Row, 1).Resize(, 2)) = 2 Then _
            Cancel = True: If Target.Column = 4 Then Module6.CopyREQ6 Else Module3.SelectOLE3
End Sub