| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
combine two macros
Hi, i need this two macros in the same sheet.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 6 Then Cells(Target.Row + 1, "B").Select ElseIf Target.Column 1 And Target.Column < 6 Then Cells(Target.Row, Target.Column + 1).Select End If End Sub --------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Address(False, False) = "C5" Then Range("C1").Value = IIf(IsEmpty(Target), "", "" & UCase(Format(Date, "dddd dd.mm.yyyy"))) ElseIf Target.Cells.Column = 2 And Target.Cells.Row = 5 Then Application.EnableEvents = False N = Target.Row If Me.Range("A" & N).Value < "" Then With Me.Range("G" & N) If .Value = "" Then ..Value = Now End If End With End If Application.EnableEvents = True End If enditall: End Sub How can i make this two macros work together? How can i combine this macros? Can this be done? Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
combine two macros
Please note that I havent changed your code; but just rearranged...
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Address(False, False) = "C5" Then Range("C1").Value = IIf(IsEmpty(Target), "", "" & _ UCase(Format(Date, "dddd dd.mm.yyyy"))) ElseIf Target.Column = 2 And Target.Cells.Row = 5 Then Application.EnableEvents = False N = Target.Row If Me.Range("A" & N).Value < "" Then With Me.Range("G" & N) If .Value = "" Then .Value = Now End If End With End If Application.EnableEvents = True ElseIf Target.Column 1 And Target.Column < 6 Then Cells(Target.Row, Target.Column + 1).Select ElseIf Target.Column = 6 Then Cells(Target.Row + 1, "B").Select End If enditall: End Sub -- If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need this two macros in the same sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 6 Then Cells(Target.Row + 1, "B").Select ElseIf Target.Column 1 And Target.Column < 6 Then Cells(Target.Row, Target.Column + 1).Select End If End Sub --------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Address(False, False) = "C5" Then Range("C1").Value = IIf(IsEmpty(Target), "", "" & UCase(Format(Date, "dddd dd.mm.yyyy"))) ElseIf Target.Cells.Column = 2 And Target.Cells.Row = 5 Then Application.EnableEvents = False N = Target.Row If Me.Range("A" & N).Value < "" Then With Me.Range("G" & N) If .Value = "" Then .Value = Now End If End With End If Application.EnableEvents = True End If enditall: End Sub How can i make this two macros work together? How can i combine this macros? Can this be done? Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
combine two macros
I realize you did not change the OP's code, but I might suggest one change
right at the end of it: enditall: Application.EnableEvents = True End Sub For the OP: with this change, if an error takes place within the portion of code at the first ElseIf where .EnableEvents has been turned off, that condition will be reversed in the event of an error exit, otherwise if an error takes place in that section of code, your workbook will no longer respond to any events which can cause some head scratching moments. "Jacob Skaria" wrote: Please note that I havent changed your code; but just rearranged... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Address(False, False) = "C5" Then Range("C1").Value = IIf(IsEmpty(Target), "", "" & _ UCase(Format(Date, "dddd dd.mm.yyyy"))) ElseIf Target.Column = 2 And Target.Cells.Row = 5 Then Application.EnableEvents = False N = Target.Row If Me.Range("A" & N).Value < "" Then With Me.Range("G" & N) If .Value = "" Then .Value = Now End If End With End If Application.EnableEvents = True ElseIf Target.Column 1 And Target.Column < 6 Then Cells(Target.Row, Target.Column + 1).Select ElseIf Target.Column = 6 Then Cells(Target.Row + 1, "B").Select End If enditall: End Sub -- If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need this two macros in the same sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 6 Then Cells(Target.Row + 1, "B").Select ElseIf Target.Column 1 And Target.Column < 6 Then Cells(Target.Row, Target.Column + 1).Select End If End Sub --------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Address(False, False) = "C5" Then Range("C1").Value = IIf(IsEmpty(Target), "", "" & UCase(Format(Date, "dddd dd.mm.yyyy"))) ElseIf Target.Cells.Column = 2 And Target.Cells.Row = 5 Then Application.EnableEvents = False N = Target.Row If Me.Range("A" & N).Value < "" Then With Me.Range("G" & N) If .Value = "" Then .Value = Now End If End With End If Application.EnableEvents = True End If enditall: End Sub How can i make this two macros work together? How can i combine this macros? Can this be done? Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
combine two macros
Thanks 'JLatham'..
If this post helps click Yes --------------- Jacob Skaria "JLatham" wrote: I realize you did not change the OP's code, but I might suggest one change right at the end of it: enditall: Application.EnableEvents = True End Sub For the OP: with this change, if an error takes place within the portion of code at the first ElseIf where .EnableEvents has been turned off, that condition will be reversed in the event of an error exit, otherwise if an error takes place in that section of code, your workbook will no longer respond to any events which can cause some head scratching moments. "Jacob Skaria" wrote: Please note that I havent changed your code; but just rearranged... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Address(False, False) = "C5" Then Range("C1").Value = IIf(IsEmpty(Target), "", "" & _ UCase(Format(Date, "dddd dd.mm.yyyy"))) ElseIf Target.Column = 2 And Target.Cells.Row = 5 Then Application.EnableEvents = False N = Target.Row If Me.Range("A" & N).Value < "" Then With Me.Range("G" & N) If .Value = "" Then .Value = Now End If End With End If Application.EnableEvents = True ElseIf Target.Column 1 And Target.Column < 6 Then Cells(Target.Row, Target.Column + 1).Select ElseIf Target.Column = 6 Then Cells(Target.Row + 1, "B").Select End If enditall: End Sub -- If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need this two macros in the same sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 6 Then Cells(Target.Row + 1, "B").Select ElseIf Target.Column 1 And Target.Column < 6 Then Cells(Target.Row, Target.Column + 1).Select End If End Sub --------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Address(False, False) = "C5" Then Range("C1").Value = IIf(IsEmpty(Target), "", "" & UCase(Format(Date, "dddd dd.mm.yyyy"))) ElseIf Target.Cells.Column = 2 And Target.Cells.Row = 5 Then Application.EnableEvents = False N = Target.Row If Me.Range("A" & N).Value < "" Then With Me.Range("G" & N) If .Value = "" Then .Value = Now End If End With End If Application.EnableEvents = True End If enditall: End Sub How can i make this two macros work together? How can i combine this macros? Can this be done? Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
combine two macros
Hi, it's not working. The second macro it's working ok, but the first is not.
When i write something in "B5" and then hit enter, the selected cell is not "C5". The selected cell is "B6". I need the selected cell to be cell "B5". If i write something in "C5" and then hit enter, the selected cell will be "D1" but i need the selected cell to be "D5". From "D" to "F", the code is running good and at the end, the selected cell is second cell in next row ("B6"), so it's ok. I think the first code and the second, has a problem because the second code it's using "B5" and "C5". Maybe this two macros can be merged togheter in another way? Thanks! "Jacob Skaria" a scris: Thanks 'JLatham'.. If this post helps click Yes --------------- Jacob Skaria "JLatham" wrote: I realize you did not change the OP's code, but I might suggest one change right at the end of it: enditall: Application.EnableEvents = True End Sub For the OP: with this change, if an error takes place within the portion of code at the first ElseIf where .EnableEvents has been turned off, that condition will be reversed in the event of an error exit, otherwise if an error takes place in that section of code, your workbook will no longer respond to any events which can cause some head scratching moments. "Jacob Skaria" wrote: Please note that I havent changed your code; but just rearranged... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Address(False, False) = "C5" Then Range("C1").Value = IIf(IsEmpty(Target), "", "" & _ UCase(Format(Date, "dddd dd.mm.yyyy"))) ElseIf Target.Column = 2 And Target.Cells.Row = 5 Then Application.EnableEvents = False N = Target.Row If Me.Range("A" & N).Value < "" Then With Me.Range("G" & N) If .Value = "" Then .Value = Now End If End With End If Application.EnableEvents = True ElseIf Target.Column 1 And Target.Column < 6 Then Cells(Target.Row, Target.Column + 1).Select ElseIf Target.Column = 6 Then Cells(Target.Row + 1, "B").Select End If enditall: End Sub -- If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i need this two macros in the same sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 6 Then Cells(Target.Row + 1, "B").Select ElseIf Target.Column 1 And Target.Column < 6 Then Cells(Target.Row, Target.Column + 1).Select End If End Sub --------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Address(False, False) = "C5" Then Range("C1").Value = IIf(IsEmpty(Target), "", "" & UCase(Format(Date, "dddd dd.mm.yyyy"))) ElseIf Target.Cells.Column = 2 And Target.Cells.Row = 5 Then Application.EnableEvents = False N = Target.Row If Me.Range("A" & N).Value < "" Then With Me.Range("G" & N) If .Value = "" Then .Value = Now End If End With End If Application.EnableEvents = True End If enditall: End Sub How can i make this two macros work together? How can i combine this macros? Can this be done? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combine cells with the same reference and combine quantities | Excel Discussion (Misc queries) | |||
| How do I combine MACROS and functions? | New Users to Excel | |||
| Macros warning always shows up, even if all macros removed | Excel Discussion (Misc queries) | |||
| combine two macros | Excel Worksheet Functions | |||
| Training: More on how to use macros in Excel: Recording Macros | Excel Worksheet Functions | |||
Linear Mode
