| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macros in Personal.xls that would create two toolbars and buttonswith assigned macros | Excel Programming | |||
| choose default macros Not Enabled / Macros Enable Setting | Excel Programming | |||
| weird saving of a document with macros resulting with macros being transfered to the copy | Excel Programming | |||
| convert lotus 123w macros to excel macros | Excel Programming | |||
| Macros not appearing in the Tools Macro Macros list | Excel Programming | |||
![Thomas Price[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
