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!

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!

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!

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!

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!