Contents to Comments, Cumulative

[Guest]

Hi All,

I have a table with several rows and columns. Within the table, are several
versions of the following three columns:
Tick "Yes, I have done something";
Date it was done;
Commentary, "But it's not all finished..."; Some kind of text entirely at
user's discretion.

Thanks to Bob Philips, I can now, on a double-click, get a tick, and the
date, and I trust my users can type...

Because Bob clearly found that too easy, I am being a little bit more
adventurous on his/anyone else's behalf:

Can I rig it so that, say the user comes along a week later, to update those
three entries, he/she can double-click, to blank the three cells, the former
contents of the last two cells being stored in a comment tied to one of the
cells?

Then come along a week later, update, the latest stuff being stored on the
next line in the same comment box, etc., etc.?

I'm pretty sure this can be done but what are the practical limitations -
just how much info. can one comment store?

Here's Bob's original solution for ticks / dates:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel
As Boolean)
Const WS_RANGE As String = "O:O,S:S,X:X,AC:AC"

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value <> "a" Then
.Font.Name = "Marlett"
.Value = "a"
.Offset(0, 1).Value = Date
.Offset(0, 1).NumberFormat = "dd-mmm-yy"
Else
.Value = ""
.Offset(0, 1).Value = ""
End If
End With
End If

End Sub

Just so's you know what kind of an Excel halfwit you're dealing with, I
realise that I could input a further line to blank the second cell along as
well -
..Offset(0, 1).Value = ""

but how do I get one line to kill both adjacent cells?

Finally, is there any way I can draw up (presumably) a second macro which
has some 'on x event' instructions, so that users who bypass the
double-click/tick approach, and just input a new date, suffer the same
consequence?

I suspect that there are many of you who will have dealt with this already;
if you have tutorials, etc., covering this then I'll be more than happy to
have a butcher's.

Many, many thanks in advance.

If you can get it to make the tea as well, I'd be most grateful...

Regards,

 

[Bernie Deitrick]

Try the version below.

HTH,
Bernie
MS Excel MVP


Const WS_RANGE As String = "O:O,S:S,X:X,AC:AC"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)

Dim myText As String
Dim mmCell As Range

If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False

If Not Intersect(Target, Range(WS_RANGE)) Is Nothing Then
With Target
If .Value <> "a" Then
.Font.Name = "Marlett"
.Value = "a"
.Offset(0, 1).Value = Date
.Offset(0, 1).NumberFormat = "dd-mmm-yy"
Else
On Error GoTo noComment
myText = ""
Set mycell = .SpecialCells(xlCellTypeComments)
myText = .Comment.Text & Chr(10)
.ClearComments
noComment:
.AddComment myText & .Offset(0, 1).Text & Chr(10) & .Offset(0, 2).Text
.Resize(1, 3).ClearContents
End If
End With
End If
Application.EnableEvents = True
Cancel = True
End Sub

 

[Bernie Deitrick]

ooops....

Dim mmCell As Range

should have been

Dim myCell As Range


That's what I get for adding dim statements in a text editor...

Sorry about that,
Bernie
MS Excel MVP

 

[Guest]

Bernie,

Thanks very much. I am amazed it's such a short piece of code. If you have
the time, perhaps you could educate me further:

(As an aside, I did note the mm / my thing, but it didn't make any
difference - presumably VB allows one to declare variables 'on the fly?')

The first bit is basically saying that if the selected cell does NOT fall
OUTSIDE the selected ranges - this is easier than saying if it DOES fall
SOMEWHERE in...?

then slap a tick and then a formatted date next cell along..

otherwise:

On error Goto - when is it an error? When the code messes up, or simply when
the first bit is false - i.e. when there's already a tick?

It seems that the last couple of bits are

i) temporarily dropping the contents of the comment into myText,
iii) erasing what's in the comment
iii) dropping myText plus the next couple of entries back into the comment

I am a bit confused by this. Perhaps you've designed it to ignore rubbish if
it messes up and basically start from scratch?

I realise that you've probably got better things to do than explain it once
you've solved it, but any pointers would be most welcome.

Thanks once again

 

[Bernie Deitrick]

Lambs,

Comments in-line....

(As an aside, I did note the mm / my thing, but it didn't make any
difference - presumably VB allows one to declare variables 'on the fly?')

If you odnt' use Option Explicit at the top of your code module, VBA allows
you to use undeclared variables, which is usually a bad idea, if only that
your typing doesn't get checked.

The first bit is basically saying that if the selected cell does NOT fall
OUTSIDE the selected ranges - this is easier than saying if it DOES fall
SOMEWHERE in...?

Actually, intersect return a range if there is an intersect, and nothing if
there isn't, so you could say:

IF Intersect(.....) Is Nothing Then....
what to do if there isn't an intersect
else
what to do if there is an intersect
end if

but that requires an else clause, since you really only want to do something
when there is an intersect.

On error Goto - when is it an error?

The error is a result of the .SpecialCells (Set mycell =
..SpecialCells(xlCellTypeComments))
when the current cell doesn't have a comment. Yes, it bass-ackwards, but
that is the way it is done.

When the code messes up, or simply when
the first bit is false - i.e. when there's already a tick?

It seems that the last couple of bits are

i) temporarily dropping the contents of the comment into myText,
iii) erasing what's in the comment
iii) dropping myText plus the next couple of entries back into the comment

I am a bit confused by this. Perhaps you've designed it to ignore rubbish
if
it messes up and basically start from scratch?

Nope, it isn't designed to ignore rubbish, just to not try to read a comment
that doesn't exist.

I realise that you've probably got better things to do than explain it
once
you've solved it, but any pointers would be most welcome.

So, any other questions? ;-)

Bernie
MS Excel MVP