multiple worksheet changes

[andysh]

Hi

I have a worksheet change in a sheet but need to a second one. I know you can't have two changes in a sheet so is there a way I could combine the two changes below?

Private Sub worksheet_change(ByVal target As Range)
If target.Column <> 2 Ortarget.Cells.Count > 1 Then Exit Sub
Iftarget.Offset(0, -1) = "" Then
target.Offset(0,-1) = Now
target.Offset(0,8) = "USERNAME"
End If
End Sub


Private Sub worksheet_change(ByVal target As Range)

If Not Intersect(target,Range("E:E")) Is Nothing Then
Select Casetarget
Case"REJECT"
MsgBox"Please enter a reason for rejection"
End Select
End If
End Sub

 

[Peter_SSs]

Try this

Private Sub worksheet_change(ByVal target As Range)
  Application.EnableEvents = False
  If target.Column = 2 And target.Cells.Count = 1 Then
    If target.Offset(0, -1) = "" Then
      target.Offset(0, -1) = Now
      target.Offset(0, 8) = "USERNAME"
    End If
  End If
  
  If Not Intersect(target, Range("E:E")) Is Nothing Then
    Select Case target
      Case "REJECT"
        MsgBox "Please enter a reason for rejection"
      End Select
  End If
  Application.EnableEvents = True
End Sub

BTW, when posting code please indent your code and use code tags to preserve that indentation - see my signature block below.

 

[andysh]

That doesn't seem to work, neither of the subs are working

Apologies for the code, I'm having to mail the code to a laptop not on company network and it reformats

 

[Peter_SSs]

That doesn't seem to work, neither of the subs are working

Perhaps your 'Events' have become disabled. Close right out of Excel then open it again & try again (with this sightly modified code replacing the previous suggestion).

Private Sub worksheet_change(ByVal Target As Range)
  Application.EnableEvents = False
  If Target.Cells.Count = 1 Then
    If Target.Column = 2 Then
      If Target.Offset(0, -1) = "" Then
        Target.Offset(0, -1) = Now
        Target.Offset(0, 8) = "USERNAME"
      End If
    End If
    
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
      Select Case Target
        Case "REJECT"
          MsgBox "Please enter a reason for rejection"
        End Select
    End If
  End If
  Application.EnableEvents = True
End Sub

 

[andysh]

Perfect

Thanks for your help, Peter

 

[Peter_SSs]

Perfect

Thanks for your help, Peter

You're welcome.