When a date is placed in any cell D, I would like either:
1) a comment to automatically be accociated to cell F on the same row with a
date displayed which will be the date from D + 14, OR
2) When the cell in F has focus, the date (D + 14) appears in a pre
determined cell at the top of the page, F2 for instance.

Any ideas?
--
Traa Dy Liooar

Jock

Unfortunately I do not have hard code to give you, but if you look into
events that can occur on a worksheet, then what you would want to do is
capture a cell change event.

If the change occurs in a cell that is in column D, then check to see if it
is a date or contains a date (Depending on what data you expect in the cell.
I.e., if you expect someone to type "This will be performed on 01/01/10" then
all you want from the text is the date.

If that is true, then assign the appropriate "object" (You mentioned a
comment, though not sure if you are referring to a little popup comment, or
just a string of text like "This is due on 01/15/10") the date of column D
incremented by 14 days. I don't recall the function name, but there is a
date option where you input the month, day, and year separately and it
provides a date code. I recommend this feature rather than just incrementing
the calendar day and displaying that as text, because if the date in Col D is
the 15th or later and it happens to be in February of a non-leap year then
the result would be the 29th of February or later which wouldn't exist in
that year. By using the format Year("year of col d") Month("Month of col D")
and day("Day of Col D" + 14), Excel will fix the month and year to correspond
to this new date.

Otherwise if you have a date data type, you simply add 14 to it and store it
in the data object necessary for Column F of the same row.

Don't know if that helps, there are others that can provide you the straight
code, I tend to try to provide the thought process to construct the code,
because there are a number of ways to get to the same result.

"Jock" wrote:

When a date is placed in any cell D, I would like either:
1) a comment to automatically be accociated to cell F on the same row with a
date displayed which will be the date from D + 14, OR
2) When the cell in F has focus, the date (D + 14) appears in a pre
determined cell at the top of the page, F2 for instance.

Any ideas?
--
Traa Dy Liooar

Jock

Jock,

Try the worksheet change code below. This is very basic and you would need
to consider what you would do if the cell already had a comment. As a minimum
you should check to see if there is to prevent the code giving an error with

Set cmt = ActiveCell.Comment
If cmt Is Nothing Then
'insert your comment




Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsDate(Target) Or Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("D:D")) Is Nothing Then
DateFormat = "dd-mmm-yy"
Newdate = DateAdd("D", 14, Target.Value)
Target.Offset(, 2).AddComment.Text Text:=Format(Newdate, DateFormat)
End If
End Sub

Mike

"Jock" wrote:

When a date is placed in any cell D, I would like either:
1) a comment to automatically be accociated to cell F on the same row with a
date displayed which will be the date from D + 14, OR
2) When the cell in F has focus, the date (D + 14) appears in a pre
determined cell at the top of the page, F2 for instance.

Any ideas?
--
Traa Dy Liooar

Jock

Hi,

And for option 2 try this. Once again consider some error trapping by
checking if there's a date in column D for example

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("F:F")) Is Nothing Then
Range("F2") = DateAdd("D", 14, Target.Offset(, -2).Value)
End If
End Sub

Mike

"Jock" wrote:

When a date is placed in any cell D, I would like either:
1) a comment to automatically be accociated to cell F on the same row with a
date displayed which will be the date from D + 14, OR
2) When the cell in F has focus, the date (D + 14) appears in a pre
determined cell at the top of the page, F2 for instance.

Any ideas?
--
Traa Dy Liooar

Jock

I get the message "Invalid procedure call or argument" with this one,
referring to the line beginning 'Range("F2")=......
??
Traa Dy Liooar

Jock


"Mike H" wrote:

Hi,

And for option 2 try this. Once again consider some error trapping by
checking if there's a date in column D for example

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("F:F")) Is Nothing Then
Range("F2") = DateAdd("D", 14, Target.Offset(, -2).Value)
End If
End Sub

Mike

"Jock" wrote:

When a date is placed in any cell D, I would like either:
1) a comment to automatically be accociated to cell F on the same row with a
date displayed which will be the date from D + 14, OR
2) When the cell in F has focus, the date (D + 14) appears in a pre
determined cell at the top of the page, F2 for instance.

Any ideas?
--
Traa Dy Liooar

Jock

Hi,

The only way I can make this error is with text in column D, did you put it
in as worksheet code and copy it exactly?

Mike

"Jock" wrote:

I get the message "Invalid procedure call or argument" with this one,
referring to the line beginning 'Range("F2")=......
??
Traa Dy Liooar

Jock


"Mike H" wrote:

Hi,

And for option 2 try this. Once again consider some error trapping by
checking if there's a date in column D for example

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("F:F")) Is Nothing Then
Range("F2") = DateAdd("D", 14, Target.Offset(, -2).Value)
End If
End Sub

Mike

"Jock" wrote:

When a date is placed in any cell D, I would like either:
1) a comment to automatically be accociated to cell F on the same row with a
date displayed which will be the date from D + 14, OR
2) When the cell in F has focus, the date (D + 14) appears in a pre
determined cell at the top of the page, F2 for instance.

Any ideas?
--
Traa Dy Liooar

Jock

Last one for the day!
I'm keen to get this one to work so that when a date is entered in 'D', a
comment box appears in 'F' with 'D's date plus 14 in it.
This would save a lot of grief!
However, I can't get any comment box to appear with you code. I have copied
it into a Worksheet_Change event that is already so I'll copy the whole thing
below to see if you can help me out on this one.
Thanks Mike.

Private Sub Worksheet_Change(ByVal Target As Range)

'Enters todays date in A when case number entered in B
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B5:B100")) Is Nothing Then
With Target
If .Value < "" Then
.Offset(0, -1).Value = Format(Date, "dd/mmm")
End If
End With
End If
'Application.EnableEvents = True


'Application.EnableEvents = False
If Not IsDate(Target) Or Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("I:I")) Is Nothing Then
DateFormat = "dd-mmm"
Newdate = DateAdd("I", 14, Target.Value)
Target.Offset(, 1).AddComment.Text Text:=Format(Newdate, DateFormat)
Application.EnableEvents = True
End If
End Sub


--
Traa Dy Liooar

Jock


"Mike H" wrote:

Hi,

The only way I can make this error is with text in column D, did you put it
in as worksheet code and copy it exactly?

Mike

"Jock" wrote:

I get the message "Invalid procedure call or argument" with this one,
referring to the line beginning 'Range("F2")=......
??
Traa Dy Liooar

Jock


"Mike H" wrote:

Hi,

And for option 2 try this. Once again consider some error trapping by
checking if there's a date in column D for example

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("F:F")) Is Nothing Then
Range("F2") = DateAdd("D", 14, Target.Offset(, -2).Value)
End If
End Sub

Mike

"Jock" wrote:

When a date is placed in any cell D, I would like either:
1) a comment to automatically be accociated to cell F on the same row with a
date displayed which will be the date from D + 14, OR
2) When the cell in F has focus, the date (D + 14) appears in a pre
determined cell at the top of the page, F2 for instance.

Any ideas?
--
Traa Dy Liooar

Jock

I have changed some of the columns btw :)
--
Traa Dy Liooar

Jock


"Jock" wrote:

Last one for the day!
I'm keen to get this one to work so that when a date is entered in 'D', a
comment box appears in 'F' with 'D's date plus 14 in it.
This would save a lot of grief!
However, I can't get any comment box to appear with you code. I have copied
it into a Worksheet_Change event that is already so I'll copy the whole thing
below to see if you can help me out on this one.
Thanks Mike.

Private Sub Worksheet_Change(ByVal Target As Range)

'Enters todays date in A when case number entered in B
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B5:B100")) Is Nothing Then
With Target
If .Value < "" Then
.Offset(0, -1).Value = Format(Date, "dd/mmm")
End If
End With
End If
'Application.EnableEvents = True


'Application.EnableEvents = False
If Not IsDate(Target) Or Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("I:I")) Is Nothing Then
DateFormat = "dd-mmm"
Newdate = DateAdd("I", 14, Target.Value)
Target.Offset(, 1).AddComment.Text Text:=Format(Newdate, DateFormat)
Application.EnableEvents = True
End If
End Sub


--
Traa Dy Liooar

Jock


"Mike H" wrote:

Hi,

The only way I can make this error is with text in column D, did you put it
in as worksheet code and copy it exactly?

Mike

"Jock" wrote:

I get the message "Invalid procedure call or argument" with this one,
referring to the line beginning 'Range("F2")=......
??
Traa Dy Liooar

Jock


"Mike H" wrote:

Hi,

And for option 2 try this. Once again consider some error trapping by
checking if there's a date in column D for example

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("F:F")) Is Nothing Then
Range("F2") = DateAdd("D", 14, Target.Offset(, -2).Value)
End If
End Sub

Mike

"Jock" wrote:

When a date is placed in any cell D, I would like either:
1) a comment to automatically be accociated to cell F on the same row with a
date displayed which will be the date from D + 14, OR
2) When the cell in F has focus, the date (D + 14) appears in a pre
determined cell at the top of the page, F2 for instance.

Any ideas?
--
Traa Dy Liooar

Jock