Help with combining/merging code
 

I have one sortting code that is attached to a worksheet change event and
another in a button click event and I want to merge the worksheet change code
into the button click event code or what ever else can be suggested.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
Const WS_RANGE As String = "B2:B5001"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = WorksheetFunction.Max(Range("A1:A5001")) + 1
Me.Range("A:G").Sort key1:=Me.Range("B3"), header:=xlYes
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub


Private Sub BtnDelete_Click()
Dim fRow As Long

On Error GoTo ender
fRow = Columns(2).Find(What:=TxtMan.Value, _
After:=Cells(5000, 2), LookIn:=xlFormulas, _
LookAT:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Row
Rows(fRow).Delete
Exit Sub

ws_exit:
Application.EnableEvents = True
Exit Sub
ender:
MsgBox "Value not found"
End Sub

Help with combining/merging code
 

I am not sure if this applies to you, but in general:

1. The button code should disable events until it is done
2. If the button code and worksheet code need to call a common sub, then the
common sub should be declared Public in a standard module.
--
Gary''s Student - gsnu200749


"Mekinnik" wrote:

I have one sortting code that is attached to a worksheet change event and
another in a button click event and I want to merge the worksheet change code
into the button click event code or what ever else can be suggested.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
Const WS_RANGE As String = "B2:B5001"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = WorksheetFunction.Max(Range("A1:A5001")) + 1
Me.Range("A:G").Sort key1:=Me.Range("B3"), header:=xlYes
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub


Private Sub BtnDelete_Click()
Dim fRow As Long

On Error GoTo ender
fRow = Columns(2).Find(What:=TxtMan.Value, _
After:=Cells(5000, 2), LookIn:=xlFormulas, _
LookAT:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Row
Rows(fRow).Delete
Exit Sub

ws_exit:
Application.EnableEvents = True
Exit Sub
ender:
MsgBox "Value not found"
End Sub