How to run Multiple Macro's in Worksheet?

[Ben Dummar]

Hi,

When I use the following macro's one at a time in the worksheet they work
great. What do I need to do to be able to run both of them in the same
worksheet at the same time?

Thanks,
Ben

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'to install -- right-click on the sheettab of the corresponding
' sheet and choose 'view code'. Paste the following procedure
' in the module.
If Target.Column <> 2 Then Exit Sub
If Target.Row = 1 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Dim R As Long
R = Target.Row
Target.Offset(0, 2).Value = Date

ErrHandler:
Application.EnableEvents = True
End Sub

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("l2:l15"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("l2:m15").Sort Key1:=Range("l2")
Application.EnableEvents = True
End Sub

 

[Norman Jones]

Hi Ben,

When I use the following macro's one at a time in the worksheet they work
great. What do I need to do to be able to run both of them in the same
worksheet at the same time?

It is only possible to have one Worksheet_Change procedure in a given sheet
module. Therefore, you need to combine your two procedures.

Try replacing the existing code with:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim R As Long
R = Target.Row

If Target.Column <> 2 Then Exit Sub
If Target.Row = 1 Then Exit Sub

On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Offset(0, 2).Value = Date

If Not Intersect(Range("l2:l15"), Target) Is Nothing Then
Range("l2:m15").Sort Key1:=Range("l2")
End If

ErrHandler:
Application.EnableEvents = True
End Sub
'<<=============

 

[Ben Dummar]

Norman,

Thanks for the quick response. Thanks for the info on combining. The first
half of the change_event works but the second part(the autosort) doesn't work
when combined. It is like it doesn't check to see if the second event
occured.

 

[Norman Jones]

Hi Ben,

Try the following version:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim R As Long
R = Target.Row

If Not Intersect(Range("l2:l15"), Target) Is Nothing Then
Range("l2:m15").Sort Key1:=Range("l2")
End If

If Target.Column <> 2 Then Exit Sub
If Target.Row = 1 Then Exit Sub

On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Offset(0, 2).Value = Date

If Not Intersect(Range("l2:l15"), Target) Is Nothing Then
Range("l2:m15").Sort Key1:=Range("l2")
End If

ErrHandler:
Application.EnableEvents = True
End Sub
'<<=============

 

[Norman Jones]

Hi Ben,

Try the following version:

Should read:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim R As Long
R = Target.Row

On Error GoTo ErrHandler
Application.EnableEvents = False

If Not Intersect(Range("I2:I15"), Target) Is Nothing Then
Range("I2:M15").Sort Key1:=Range("I2") '<<== CHECK RANGE
End If

If Target.Column <> 2 Then Exit Sub
If Target.Row = 1 Then Exit Sub

Target.Offset(0, 2).Value = Date

ErrHandler:
Application.EnableEvents = True
End Sub
'<<=============

 

[Ben Dummar]

Normon,

Thanks!

I got it to work by changing "Application.EnableEvents = False " to
"Application.EnableEvents = True".

Will or is that causing some negative side affect that I am currently not
seeing?

 

[Norman Jones]

Hi Ben,

I got it to work by changing "Application.EnableEvents = False " to
"Application.EnableEvents = True".

The code worked for me with Application.EnableEvents set to False and
restored to True at the end of the procedure.

This turns of events and , in your case, prevents changes in cells I2_I15
(caused by the macro's sort code) from re-triggering the Worksheet_Change
event procedure.