2 Macros in VBA???
 

I have these two macros in VBA and they will not work. If I put them in
sepratly they will work but when I put them in together they will not. Can
you help me fix this so I can get them both to work??? Thanks in advance for
your time!

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Range("j:k", "y:y"), Target) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("W5"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value = 0 Then
Exit Sub
End If
If Target.Value = 50000 Then
Range("S5").Value = "HEAVY TRUCK"
End If
If Target.Value < 50000 Then
Range("S5").Value = "LT DUTY TRUCK"
End If
Application.EnableEvents = True
End Sub

2 Macros in VBA???
 

Hi

You can only have one "Worksheet_Change" event macro in a worksheet.
Here's how you can do it; put the event code in the codesheet for the
worksheet, and Macro1 & Macro2 in a normal Module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Call Macro1(Target)
Call Macro2(Target)
End Sub

Sub Macro1(Target)
If Not Application.Intersect(Range("j:k", "y:y"), Target) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Sub Macro2(Target)
If Intersect(Range("W5"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value = 0 Then
Exit Sub
End If
If Target.Value = 50000 Then
Range("S5").Value = "HEAVY TRUCK"
End If
If Target.Value < 50000 Then
Range("S5").Value = "LT DUTY TRUCK"
End If
Application.EnableEvents = True
End Sub

Best regards,
Per

"Thomas Price" skrev i meddelelsen
...
I have these two macros in VBA and they will not work. If I put them in
sepratly they will work but when I put them in together they will not.
Can
you help me fix this so I can get them both to work??? Thanks in advance
for
your time!

Private Sub ByVal Target As Excel.Range)
If Not Application.Intersect(Range("j:k", "y:y"), Target) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("W5"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value = 0 Then
Exit Sub
End If
If Target.Value = 50000 Then
Range("S5").Value = "HEAVY TRUCK"
End If
If Target.Value < 50000 Then
Range("S5").Value = "LT DUTY TRUCK"
End If
Application.EnableEvents = True
End Sub

2 Macros in VBA???
 

Per,
Thank you. It worked one time and then stopped working. Do you know
why that is? Thanks!

"Per Jessen" wrote:

Hi

You can only have one "Worksheet_Change" event macro in a worksheet.
Here's how you can do it; put the event code in the codesheet for the
worksheet, and Macro1 & Macro2 in a normal Module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Call Macro1(Target)
Call Macro2(Target)
End Sub

Sub Macro1(Target)
If Not Application.Intersect(Range("j:k", "y:y"), Target) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Sub Macro2(Target)
If Intersect(Range("W5"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value = 0 Then
Exit Sub
End If
If Target.Value = 50000 Then
Range("S5").Value = "HEAVY TRUCK"
End If
If Target.Value < 50000 Then
Range("S5").Value = "LT DUTY TRUCK"
End If
Application.EnableEvents = True
End Sub

Best regards,
Per

"Thomas Price" skrev i meddelelsen
...
I have these two macros in VBA and they will not work. If I put them in
sepratly they will work but when I put them in together they will not.
Can
you help me fix this so I can get them both to work??? Thanks in advance
for
your time!

Private Sub ByVal Target As Excel.Range)
If Not Application.Intersect(Range("j:k", "y:y"), Target) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("W5"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value = 0 Then
Exit Sub
End If
If Target.Value = 50000 Then
Range("S5").Value = "HEAVY TRUCK"
End If
If Target.Value < 50000 Then
Range("S5").Value = "LT DUTY TRUCK"
End If
Application.EnableEvents = True
End Sub

2 Macros in VBA???
 

The VBA editor must be in break mode for some reason.

Close the workbook an reopen it. Now it should work. I don't know why it
happens.

Regards,
Per

"Thomas Price" skrev i meddelelsen
...
Per,
Thank you. It worked one time and then stopped working. Do you know
why that is? Thanks!

"Per Jessen" wrote:

Hi

You can only have one "Worksheet_Change" event macro in a worksheet.
Here's how you can do it; put the event code in the codesheet for the
worksheet, and Macro1 & Macro2 in a normal Module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Call Macro1(Target)
Call Macro2(Target)
End Sub

Sub Macro1(Target)
If Not Application.Intersect(Range("j:k", "y:y"), Target) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Sub Macro2(Target)
If Intersect(Range("W5"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value = 0 Then
Exit Sub
End If
If Target.Value = 50000 Then
Range("S5").Value = "HEAVY TRUCK"
End If
If Target.Value < 50000 Then
Range("S5").Value = "LT DUTY TRUCK"
End If
Application.EnableEvents = True
End Sub

Best regards,
Per

"Thomas Price" skrev i
meddelelsen
...
I have these two macros in VBA and they will not work. If I put them in
sepratly they will work but when I put them in together they will not.
Can
you help me fix this so I can get them both to work??? Thanks in
advance
for
your time!

Private Sub ByVal Target As Excel.Range)
If Not Application.Intersect(Range("j:k", "y:y"), Target) Is Nothing
Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("W5"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value = 0 Then
Exit Sub
End If
If Target.Value = 50000 Then
Range("S5").Value = "HEAVY TRUCK"
End If
If Target.Value < 50000 Then
Range("S5").Value = "LT DUTY TRUCK"
End If
Application.EnableEvents = True
End Sub