PDA

View Full Version : [SOLVED] Change event problem



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

Paul_Hossler
04-13-2016, 07:23 AM
1. This is the right place

2. Please use CODE tags -- that's the icon [#] that enters the [ CODE ] and [/ CODE ] markers. Paste between them

3. I think (bold, italic, underlined) that you need to turnoff events while you're manipulating the worksheet to avoid your change cause the event to be called again





Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
If Target.Address = Range("ADateBack").Address Then
Application.EnableEvents = False
LastRow = Cells(Rows.Count, "C").End(xlUp).Row + 1
Range("B" & LastRow).Formula = "=Today()-ADateBack"
Range("C" & LastRow).Select
Application.EnableEvents = True
End If
End Sub

Joinersfolly
04-13-2016, 08:18 AM
Thanks Paul,
Certainly your code is neater and I have changed mine to the following, however the problem persists!, i.e.all dates in column B continue to change.
Confused.



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






Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
If Target.Address = Range("ADateBack").Address Then
Application.EnableEvents = False


LastRow = Cells(Rows.Count, "C").End(xlUp).Row + 1
Range("B" & LastRow).Formula = "=Today()-ADateBack"
Range("C" & LastRow).Select
Application.EnableEvents = True






End If


End Sub

Paul_Hossler
04-13-2016, 08:36 AM
Well, this IS a formula, so if ADateBack changes or you run it on different 'Today()'s the previous entries will change



Range("B" & LastRow).Formula = "=Today()-ADateBack"



Maybe you want this. Not tested since there's no workbook, and I assume that ADateBack is a named range containing a date
This will fix the entry as of the time the macro runs



Range("B" & LastRow).Value = Date - [ADateBack]



Maybe posting a small WB showing the problem will help

Joinersfolly
04-13-2016, 10:07 AM
The following works when I put it in the sheet activate event, so I now need to
A: Get the value in the cell G1 to subtract from today, I have the number 9 input below
B Get it to work in the change event using Target

Ideas please


Dim LastRow As Long

Application.EnableEvents = False


LastRow = Cells(Rows.Count, "C").End(xlUp).Row + 1
Range("B" & LastRow).Formula = "=Today()-9"
Range("C" & LastRow).Select
Application.EnableEvents = True







Dim LastRow As Long

Application.EnableEvents = False


LastRow = Cells(Rows.Count, "C").End(xlUp).Row + 1
Range("B" & LastRow).Formula = "=Today()-9"
Range("C" & LastRow).Select
Application.EnableEvents = True