unite macros

[Carlos M.]

Hello friends



1*since I make to unite these two macro function of this 1er macro to place
the date in column 4 when something writes in column 1

2*el second macro to place the hour in column 5 when something writes in
column 1

3*el third macro to place the hour in column 7 when something writes in
column 6







1---------------------------------------------------------------------------
-------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A:A"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 3)
.NumberFormat = "dd mmm yyyy"
.Value = Date
End With
Application.EnableEvents = True
End If
End With
End Sub
2---------------------------------------------------------------------------
--------


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A:A"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 4)
.NumberFormat = "hh:mm AM/PM"
.Value = Time
End With
Application.EnableEvents = True
End If
End With
End Sub
3---------------------------------------------------------------------------
-----
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("F:F"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 6)
.NumberFormat = "hh:mm AM/PM"
.Value = Time
End With
Application.EnableEvents = True
End If
End With
End Sub

 

[Frank Kabel]

Hi Varlos
not tested but try


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
on error goto errhandler
select case .column
case 1
Application.EnableEvents = False
With .Offset(0, 3)
.NumberFormat = "dd mmm yyyy"
.Value = Date
End With
With .Offset(0, 4)
.NumberFormat = "hh:mm AM/PM"
.Value = Time
End With

case 6
With .Offset(0, 6)
.NumberFormat = "hh:mm AM/PM"
.Value = Time
End With

end select
End With

errhandler:
Application.EnableEvents = True
End Sub

 

[Dave Peterson]

One way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error GoTo errHandler:

With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(Me.Range("A:A,F:F"), .Cells) Is Nothing Then Exit Sub

Application.EnableEvents = False
Select Case .Column
Case Is = Me.Range("a:a").Column
With .Offset(0, 3)
.NumberFormat = "dd mmm yyyy"
.Value = Date
End With

With .Offset(0, 4)
.NumberFormat = "hh:mm AM/PM"
.Value = Time
End With

Case Is = Me.Range("f:F").Column
With .Offset(0, 6) 'or .offset(0,1) if you meant G?
.NumberFormat = "hh:mm AM/PM"
.Value = Time
End With

End Select
End With

errHandler:
Application.EnableEvents = True
End Sub

 

[Carlos M.]

thanks pot everything, works perfect

One way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error GoTo errHandler:

With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(Me.Range("A:A,F:F"), .Cells) Is Nothing Then Exit Sub

Application.EnableEvents = False
Select Case .Column
Case Is = Me.Range("a:a").Column
With .Offset(0, 3)
.NumberFormat = "dd mmm yyyy"
.Value = Date
End With

With .Offset(0, 4)
.NumberFormat = "hh:mm AM/PM"
.Value = Time
End With

Case Is = Me.Range("f:F").Column
With .Offset(0, 6) 'or .offset(0,1) if you meant G?
.NumberFormat = "hh:mm AM/PM"
.Value = Time
End With

End Select
End With

errHandler:
Application.EnableEvents = True
End Sub

 

[Carlos M.]

thanks pot everything, works perfect

Hi Varlos
not tested but try


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
on error goto errhandler
select case .column
case 1
Application.EnableEvents = False
With .Offset(0, 3)
.NumberFormat = "dd mmm yyyy"
.Value = Date
End With
With .Offset(0, 4)
.NumberFormat = "hh:mm AM/PM"
.Value = Time
End With

case 6
With .Offset(0, 6)
.NumberFormat = "hh:mm AM/PM"
.Value = Time
End With

end select
End With

errhandler:
Application.EnableEvents = True
End Sub