Joinersfolly
04-13-2016, 06:43 AM
The following change event should do the following:
in the "B" column insert todays date less the number of days input in the G1 cell which is a named range called ADateBack.
(Note: The G1 cell is selected by the worksheet activate event given below
The problem is that all the dates already input in column "B" are also changed.
In fact the other dates change before the message box in the procedure below shows and before the date in put in to the next cell in column "B"
Of course the msgbox is not need and I will remove it when it works properly
What am I doing wrong?
PS Is this the correct place to ask this question?
Thanks in advance.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("ADateBack").Address Then
Dim LastRow As Long
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
LastRow = LastRow + 1
Dim DataRange As Range
Set DataRange = Range("B" & LastRow)
DataRange.Activate
MsgBox DataRange.Address
DataRange.Formula = "=Today()-ADateBack"
DataRange.Offset(0, 1).Select
End If
End Sub
Private Sub Worksheet_Activate()
Range("B1") = "Date"
Range("C1") = "Time Started"
Range("D1") = "Time Finished"
Range("E1") = "Session Total"
Range("F1") = "Decimal Playing Hours"
Range("ADateBack").Select
End Sub
in the "B" column insert todays date less the number of days input in the G1 cell which is a named range called ADateBack.
(Note: The G1 cell is selected by the worksheet activate event given below
The problem is that all the dates already input in column "B" are also changed.
In fact the other dates change before the message box in the procedure below shows and before the date in put in to the next cell in column "B"
Of course the msgbox is not need and I will remove it when it works properly
What am I doing wrong?
PS Is this the correct place to ask this question?
Thanks in advance.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("ADateBack").Address Then
Dim LastRow As Long
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
LastRow = LastRow + 1
Dim DataRange As Range
Set DataRange = Range("B" & LastRow)
DataRange.Activate
MsgBox DataRange.Address
DataRange.Formula = "=Today()-ADateBack"
DataRange.Offset(0, 1).Select
End If
End Sub
Private Sub Worksheet_Activate()
Range("B1") = "Date"
Range("C1") = "Time Started"
Range("D1") = "Time Finished"
Range("E1") = "Session Total"
Range("F1") = "Decimal Playing Hours"
Range("ADateBack").Select
End Sub