Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging and combining two reports | Excel Discussion (Misc queries) | |||
Merging/Combining workbook data | Excel Discussion (Misc queries) | |||
Combining and merging lines | Excel Discussion (Misc queries) | |||
Combining/Merging Rows | Excel Discussion (Misc queries) | |||
Combining Excel files into 1 (no merging) | New Users to Excel |