Can Excel do this with coding?

[Guest]

I have a question that I don't know if Excel can handle or not? The final
data entry Column on one of my spreadsheet is a "Comments" section. I use
that Column to enter a variety of client information such as (but not limited
to):

communications between clients and our agency
calls from counselors about our clients
comments regarding disruptive client interactions
reminders regarding action that needs to be taken on a particular client

There is no formula in this column and it is formatted for "general" data
entry. I've seen an Access program which has an "executable" CLICK ON
comments pop up field that can be directly linked to a clients name, and the
user can open a pop up window for reviewing, editing, or inputting additional
comments made. The comments can also be merged with MS Word for printing, as
many comments need to be put on paper for the purpose of a "hard copy" record.

My question is whether or not "coding" can create something similar to what
I described above (a macro button to click on which would open a window for
comments/notes) so I can remove the comments sections (from the two
spreadsheets within my workbook).

Any suggestions on this issue would be greatly appreciated.

Dan

 

[Pete_UK]

Hi Dan,

you could envisage having another file which would have at least two
columns - one would contain the customer ID to provide a link back to
your existing file and the other would be your current comments
column.

As you might want to add other comments, you might also consider a
third column which records the date of that comment, and to enable you
to retrieve all comments relating to a particular customer then you
could make use of a fourth column which would have a linked list
structure - initially set to -1, when you click Add new Comment the
new comment would go into the next available row, say row 10, so its
pointer would be set to -1 (meaning no more for that client) and the
first comment's pointer would be set to 10 by your routine. You might
have a separate file that acts as an index to the start record for
each customer, comprising customer ID and the row of the first comment
for that customer - this will enable you to track the customer
comments more easily.

You would need to be able to Add a new Comment, Delete a comment
(which would mean tracking the before and after pointers, as it might
be te second of 3 comments), as well as Edit a comment, and presumably
you would have to incorporate some rudimentary text processing
capability to handle word-wrap and such - sounds like a database
application to me!! <bg>

Hope this helps.

Pete

 

[Guest]

Thanks Pete. I was hoping that it could be done with code, easy pop ups, and
that would be that. You are correct that this sounds like a dbase.
Unfortunately I know about as much in "Access" as I know about speaking a
foreign language........."Nothing".

Hopefully the "code" gods will read this and come up with a magic answer,
lol. I appreciate your input.

Dan

 

[Pete_UK]

Dan,

you might consider posting in the programming group if you want to get
some examples of code.

Incidentally, if you do want to implement a linked list structure you
will also need a series of free-space pointers. This helps you to
cater for deleted records. Imagine that the comment which is on row 5
(out of 20 rows used) is deleted - you would not want to delete the
row as all the other pointers would need to be amended, so you just
delete the contents of that row. But that row is now available to be
re-used, so the free-space pointer should now point to 5 (as the next
available row when you want to add a comment), and row 5 would have a
pointer to row 21. You can also have forwards and backwards pointers
in case you can only display a limited number of comments and you want
to have arrows to allow the User to display earlier or later comments.

Fascinating stuff !! It's got my memory cells ticking over <bg> (I
used to teach this stuff years ago).

Pete

 

[Guest]

Pete you got my mind thinking too..........Ha! Ha! Pointers, pointers and
more pointers.

Dan

 

[Harlan Grove]

Pete you got my mind thinking too..........Ha! Ha! Pointers, pointers and
more pointers. ....
"Pete_UK" wrote: ....

....

Linked lists can be implemented with arrays. One array holds indices into
the other array. Iterating through the first array in order provides the
indices to the list-ordered contents of the second array, but the second
array need not be stored in the same order.

Or create a node type which includes an successor index member, then an
array of that type could be used to create a linked list. Doubly linked
lists are just as easy to implement with arrays. FWIW, binary trees could be
implemented with arrays.

When the array lists run out of space, resize 'em.

Pointers are unnecessary. A good thing since VBA doesn't provide them.

 

[Guest]

I appreciated all the feedback. I have used array formulas in the past but
the down side is how much they slow down my workbook and overall
calculations. I was hoping for a simple process with "coding" of being able
to "click" a executable button, open a comments window, and use this new
window to document relevant information. I invisioned being able to print
from this window, add and delete comments as necessary, etc. Right now I have
client last name in Column A, first name in Column B, and my "comments"
column is in AS (I have several other data/formula columns in between). If
imagined the pop up window launch button to live in Column AS, and associate
with the appropriate client based upon the row where that button was (to
maintain the pop up windows connection to a specific client).

I don't have much of a coding background, but I've seen coding to some
incredible things. Thanks for all the feedback. Hopefully the coding gods
will drop an answer at my feet, lol.

Dan