CODEWITHSUNDEEP
excelvba
adiwhy
adiwhy

Reputation: 67

Excel Macro, Combining two Private Sub worksheet_change

I search before in this site, but not really found same case with my code. Hope someone here can help me on this. How to combine two Private sub below?

1st code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("P:P")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Dim NR As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Select Case Target.Value
    Case "CLOSED"
      NR = Worksheets("Closed").Range("D1500").End(xlUp).Offset(1).Row
      Range("B" & Target.Row & ":P" & Target.Row).Copy Worksheets("Closed").Range("B" & NR)
      Rows(Target.Row).Delete
    Case "Re-handover"
      NR = Worksheets("Handover").Range("D1500").End(xlUp).Offset(1).Row
      Range("E" & Target.Row & ":O" & Target.Row).Copy Worksheets("Handover").Range("E" & NR)
      ' Rows(Target.Row).Delete
      End Select
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

2nd code

Option Explicit
Public preValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 15 Then Exit Sub
    Target.ClearComments
Target.AddComment.Text Text:="Updated " & Format(Date, "dd mmm yyyy") & " " & Format(Time, "hh:mm") & Chr(10) & "By " & Environ("UserName")
End Sub

thank you very much before

Upvotes: 0

Views: 1040

Answers (1)

Myles
Myles

Reputation: 176

Would this work?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

*If target.column=15 then
    Target.ClearComments
    Target.AddComment.Text Text:="Updated " & Format(Date, "dd mmm yyyy") & " " & Format(Time, "hh:mm") & Chr(10) & "By " & Environ("UserName")
else
endif *


If Intersect(Target, Range("P:P")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim NR As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Select Case Target.Value
    Case "CLOSED"
      NR = Worksheets("Closed").Range("D1500").End(xlUp).Offset(1).Row
      Range("B" & Target.Row & ":P" & Target.Row).Copy Worksheets("Closed").Range("B" & NR)
      Rows(Target.Row).Delete
    Case "Re-handover"
      NR = Worksheets("Handover").Range("D1500").End(xlUp).Offset(1).Row
      Range("E" & Target.Row & ":O" & Target.Row).Copy Worksheets("Handover").Range("E" & NR)
      ' Rows(Target.Row).Delete
      End Select
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

Upvotes: 1

Related Questions