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
|
Similar Threads
