Adding dates within a comment

[Jock]

This first part I can get to work fine:
If a date is entered in A1, a comment will appear in C1 showing the date
(from A1) plus 10 days.
If a date is then entered in B1, the comment in C1 will be amended to the
date from B1 plus 10 days.

What I am struggling with is:
If a date is entered in A1, a comment will appear in C1 showing the date
(from A1) plus 10 days. (same as above)
However, if a date is then entered in B1, the comment in C1 will be amended
to the date from A1 plus 10 days.
partial code below:

Error Resume Next
If Not Intersect(Target, Me.Range("B4:B30")) Is Nothing Then
With Target
If .Value <> "" Then
Application.EnableEvents = False
strTemp = Target.Offset(0, 1).Comment.Text
Target.Offset(0, 1).ClearComments
Target.Offset(0, 1).AddComment.Text Text:=strTemp ' & " " & "AoS
filed. Defence now due by: "
On Error GoTo 0
Application.EnableEvents = True
End If

How do I get the code to look at column A rather than the Target column B
and use the date in A?

 

[Mike H]

Jock,

Your code snippet has missing end with & end if but I assume you know that.
Try this line to populate you comment with column A

Target.Offset(0, 1).AddComment.Text Text:=strTemp & Format(Target.Offset(,
-1), "dd/mm/yyyy")

Mike

 

[NickH]

Hi Jock

I assume your code is in the Worksheet_Change subroutine?

The following does what I understand you need, but this is fixed on cells A1
and B1 for the dates and C1 for the comment. If you need to work with other
cells, this code will have to be modified accordingly

I'm using shorthand notation for the cell addresses - .[a1] is equivalent to
..Range("A1") etc.

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False)
Case "A1"
Rem check that Target contains a recognisable date and that C1 has no
comment. If both true, then add the date (from A1) + 10 days into C1 comment
If (IsDate(Target) And ActiveSheet.[c1].Comment Is Nothing) Then
ActiveSheet.[c1].AddComment.Text Text:=Format(Target + 10, "d mmm yyyy")
Case "B1"
Rem check that Target contains a recognisable date and that C1 does have
a comment. If both true, then add the date (from B1) + 10 days, plus
additional detail into C1 comment
If IsDate(Target) And Not (ActiveSheet.[c1].Comment Is Nothing) Then
ActiveSheet.[c1].Comment.Text Text:=Format(Target + 10, "d mmm yyyy") & "
extra detail"
Case Else
End Select
End Sub

 

[Billy Liddel]

Jock; Here is another version:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DueDate As Date, strTemp As String
Dim cmt As String

cmt = "AoS filed. Defence now due by: "
On Error Resume Next
If Intersect(Target, Me.Range("B4:B30")) Is Nothing Then
Exit Sub
Else
With Target
If .Value <> "" Then
Application.EnableEvents = False
DueDate = Target.Offset(0, -1)
DueDate = DueDate + 10
strTemp = Target.Offset(0, 1).Comment.Text
Target.Offset(0, 1).ClearComments
Target.Offset(0, 1).AddComment.Text Text:=vbLf & strTemp & cmt &
DueDate
On Error GoTo 0
Application.EnableEvents = True
End If
End With
End If

End Sub

HTH
Peter

 

[Jock]

Hi Nick and thanks.
I tweaked the code to suit however, I get a Compile error (Case Else outside
Select Case).
Also, If there's a date in "B", then the comment (in "C") should state the
date in "A" plus 28 days (not A + 10 as I stated before!)


--
Traa Dy Liooar

Jock

Hi Jock

I assume your code is in the Worksheet_Change subroutine?

The following does what I understand you need, but this is fixed on cells A1
and B1 for the dates and C1 for the comment. If you need to work with other
cells, this code will have to be modified accordingly

I'm using shorthand notation for the cell addresses - .[a1] is equivalent to
.Range("A1") etc.

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False)
Case "A1"
Rem check that Target contains a recognisable date and that C1 has no
comment. If both true, then add the date (from A1) + 10 days into C1 comment
If (IsDate(Target) And ActiveSheet.[c1].Comment Is Nothing) Then
ActiveSheet.[c1].AddComment.Text Text:=Format(Target + 10, "d mmm yyyy")
Case "B1"
Rem check that Target contains a recognisable date and that C1 does have
a comment. If both true, then add the date (from B1) + 10 days, plus
additional detail into C1 comment
If IsDate(Target) And Not (ActiveSheet.[c1].Comment Is Nothing) Then
ActiveSheet.[c1].Comment.Text Text:=Format(Target + 10, "d mmm yyyy") & "
extra detail"
Case Else
End Select
End Sub


--
Nick

This first part I can get to work fine:
If a date is entered in A1, a comment will appear in C1 showing the date
(from A1) plus 10 days.
If a date is then entered in B1, the comment in C1 will be amended to the
date from B1 plus 10 days.

What I am struggling with is:
If a date is entered in A1, a comment will appear in C1 showing the date
(from A1) plus 10 days. (same as above)
However, if a date is then entered in B1, the comment in C1 will be amended
to the date from A1 plus 10 days.
partial code below:

Error Resume Next
If Not Intersect(Target, Me.Range("B4:B30")) Is Nothing Then
With Target
If .Value <> "" Then
Application.EnableEvents = False
strTemp = Target.Offset(0, 1).Comment.Text
Target.Offset(0, 1).ClearComments
Target.Offset(0, 1).AddComment.Text Text:=strTemp ' & " " & "AoS
filed. Defence now due by: "
On Error GoTo 0
Application.EnableEvents = True
End If

How do I get the code to look at column A rather than the Target column B
and use the date in A?

 

[NickH]

Hi Jock

The code worked fine when it left me - perhaps it is the line wrapping
in this web page which is causing the problem?

For the date+28, you can either refer to cell A1 directly (ActiveSheet.[a1])
or as an offset to the Target. I would not generally advocate the latter
because you can't be sure what Target is pointing to, which is why I use the
Case staement on its address. In my sample, because I know it is "B1", it is
safe to use Target.Offset(0,-1).

The modified statement for Case "B1" would be like this:

If IsDate(Target) And Not (ActiveSheet.[c1].Comment Is Nothing) Then
ActiveSheet.[c1].Comment.Text
Text:=Format(ActiveSheet.[a1] + 28, "d mmm yyyy") & " extra detail"

all of the above should be on a single line in the VBA editor following the
Case "B1" statement. There is no End If in this situation as the test and
the action are on a single line.

--
Nick

Hi Nick and thanks.
I tweaked the code to suit however, I get a Compile error (Case Else outside
Select Case).
Also, If there's a date in "B", then the comment (in "C") should state the
date in "A" plus 28 days (not A + 10 as I stated before!)


--
Traa Dy Liooar

Jock

Hi Jock

I assume your code is in the Worksheet_Change subroutine?

The following does what I understand you need, but this is fixed on cells A1
and B1 for the dates and C1 for the comment. If you need to work with other
cells, this code will have to be modified accordingly

I'm using shorthand notation for the cell addresses - .[a1] is equivalent to
.Range("A1") etc.

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False)
Case "A1"
Rem check that Target contains a recognisable date and that C1 has no
comment. If both true, then add the date (from A1) + 10 days into C1 comment
If (IsDate(Target) And ActiveSheet.[c1].Comment Is Nothing) Then
ActiveSheet.[c1].AddComment.Text Text:=Format(Target + 10, "d mmm yyyy")
Case "B1"
Rem check that Target contains a recognisable date and that C1 does have
a comment. If both true, then add the date (from B1) + 10 days, plus
additional detail into C1 comment
If IsDate(Target) And Not (ActiveSheet.[c1].Comment Is Nothing) Then
ActiveSheet.[c1].Comment.Text Text:=Format(Target + 10, "d mmm yyyy") & "
extra detail"
Case Else
End Select
End Sub


--
Nick

This first part I can get to work fine:
If a date is entered in A1, a comment will appear in C1 showing the date
(from A1) plus 10 days.
If a date is then entered in B1, the comment in C1 will be amended to the
date from B1 plus 10 days.

What I am struggling with is:
If a date is entered in A1, a comment will appear in C1 showing the date
(from A1) plus 10 days. (same as above)
However, if a date is then entered in B1, the comment in C1 will be amended
to the date from A1 plus 10 days.
partial code below:

Error Resume Next
If Not Intersect(Target, Me.Range("B4:B30")) Is Nothing Then
With Target
If .Value <> "" Then
Application.EnableEvents = False
strTemp = Target.Offset(0, 1).Comment.Text
Target.Offset(0, 1).ClearComments
Target.Offset(0, 1).AddComment.Text Text:=strTemp ' & " " & "AoS
filed. Defence now due by: "
On Error GoTo 0
Application.EnableEvents = True
End If

How do I get the code to look at column A rather than the Target column B
and use the date in A?

 

[Jock]

Thanks for the info Nick - got there in the end!

Cheers,
--
Traa Dy Liooar

Jock

Hi Jock

The code worked fine when it left me - perhaps it is the line wrapping
in this web page which is causing the problem?

For the date+28, you can either refer to cell A1 directly (ActiveSheet.[a1])
or as an offset to the Target. I would not generally advocate the latter
because you can't be sure what Target is pointing to, which is why I use the
Case staement on its address. In my sample, because I know it is "B1", it is
safe to use Target.Offset(0,-1).

The modified statement for Case "B1" would be like this:

If IsDate(Target) And Not (ActiveSheet.[c1].Comment Is Nothing) Then
ActiveSheet.[c1].Comment.Text
Text:=Format(ActiveSheet.[a1] + 28, "d mmm yyyy") & " extra detail"

all of the above should be on a single line in the VBA editor following the
Case "B1" statement. There is no End If in this situation as the test and
the action are on a single line.

--
Nick

Hi Nick and thanks.
I tweaked the code to suit however, I get a Compile error (Case Else outside
Select Case).
Also, If there's a date in "B", then the comment (in "C") should state the
date in "A" plus 28 days (not A + 10 as I stated before!)


--
Traa Dy Liooar

Jock

Hi Jock

I assume your code is in the Worksheet_Change subroutine?

The following does what I understand you need, but this is fixed on cells A1
and B1 for the dates and C1 for the comment. If you need to work with other
cells, this code will have to be modified accordingly

I'm using shorthand notation for the cell addresses - .[a1] is equivalent to
.Range("A1") etc.

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False)
Case "A1"
Rem check that Target contains a recognisable date and that C1 has no
comment. If both true, then add the date (from A1) + 10 days into C1 comment
If (IsDate(Target) And ActiveSheet.[c1].Comment Is Nothing) Then
ActiveSheet.[c1].AddComment.Text Text:=Format(Target + 10, "d mmm yyyy")
Case "B1"
Rem check that Target contains a recognisable date and that C1 does have
a comment. If both true, then add the date (from B1) + 10 days, plus
additional detail into C1 comment
If IsDate(Target) And Not (ActiveSheet.[c1].Comment Is Nothing) Then
ActiveSheet.[c1].Comment.Text Text:=Format(Target + 10, "d mmm yyyy") & "
extra detail"
Case Else
End Select
End Sub


--
Nick


:

This first part I can get to work fine:
If a date is entered in A1, a comment will appear in C1 showing the date
(from A1) plus 10 days.
If a date is then entered in B1, the comment in C1 will be amended to the
date from B1 plus 10 days.

What I am struggling with is:
If a date is entered in A1, a comment will appear in C1 showing the date
(from A1) plus 10 days. (same as above)
However, if a date is then entered in B1, the comment in C1 will be amended
to the date from A1 plus 10 days.
partial code below:

Error Resume Next
If Not Intersect(Target, Me.Range("B4:B30")) Is Nothing Then
With Target
If .Value <> "" Then
Application.EnableEvents = False
strTemp = Target.Offset(0, 1).Comment.Text
Target.Offset(0, 1).ClearComments
Target.Offset(0, 1).AddComment.Text Text:=strTemp ' & " " & "AoS
filed. Defence now due by: "
On Error GoTo 0
Application.EnableEvents = True
End If

How do I get the code to look at column A rather than the Target column B
and use the date in A?