Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding dates within a comment
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? -- Traa Dy Liooar Jock |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding dates within a comment
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 "Jock" wrote: 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? -- Traa Dy Liooar Jock |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding dates within a comment
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 "Jock" wrote: 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? -- Traa Dy Liooar Jock |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding dates within a comment
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" wrote: 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? -- Traa Dy Liooar Jock |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding dates within a comment
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 "NickH" wrote: 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 "Jock" wrote: 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? -- Traa Dy Liooar Jock |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding dates within a comment
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 "Jock" wrote: 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 "NickH" wrote: 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 "Jock" wrote: 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? -- Traa Dy Liooar Jock |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding dates within a comment
Thanks for the info Nick - got there in the end!
Cheers, -- Traa Dy Liooar Jock "NickH" wrote: 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 "Jock" wrote: 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 "NickH" wrote: 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 "Jock" wrote: 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? -- Traa Dy Liooar Jock |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a variable value to a comment? | Excel Programming | |||
Adding a new comment with a function? | Excel Discussion (Misc queries) | |||
Adding Comment and AutoSizing | Excel Programming | |||
Adding Comment to cell | Excel Programming | |||
Adding comment programmatically | Excel Programming |