Can this be done with coding?

[Guest]

I have a question that I don't know if Excel can handle or not? Also please
note that I am a "newbie" to Excel, and definitly to coding. Most of the
coding in my workbook was done the simple way (assistance from friends who
wrote the code, and gave me the necessary buttons to launch the codes
itself-e.g. a sort button to automatically sort my spreadsheet by last name).
I merely copied and pasted the code into the VB editor along with the
executable buttons to make the program function. So be gentle with me on
explanations, lol.

I have a spreadsheet where column A carries "last name", column B carries
"first name", and column AS carries "comments" (with several columns of other
requirements in between).

I use column AS (comments) 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).

Column A and B also use Dynamic Ranges fyi:

=OFFSET('New Rule Clients'!$A$4,,,COUNTA('New Rule Clients'!$B:$B)-2,1)
=OFFSET('New Rule Clients'!$B$4,,,COUNTA('New Rule Clients'!$B:$B)-2,1)

Any suggestions on this issue would be greatly appreciated.

Dan

 

[Keith74]

Hi

I may have read something wrong but wouldn't, right click - insert
comment, do the job of the AS column.

I would be fairly straightforward to then print off names and comments
either on another worksheet, or a little more complex from word using
VBA

Keith

 

[Guest]

You can pop up a userform and have the user type information in a textbox,
then store it somewhere with information to associate it to the client - I
only add the latter because I am not sure what you mean by

so I can remove the comments sections (from the two
spreadsheets within my workbook).

Also, you can right click on any cell in Excel and in the popup window, you
can choose insert comment. Then when you hover over this cell, the comment
will fly out for review. You can also right click and edit the comment.
You can have all comments visible so when you print the sheet, the comments
are visible. There has also been code posted in the past to list comments in
Word.

 

[Guest]

Hi Tom:

Thanks for your reply (as well as Keith's). I am familiar with the insert
comments option in Excel, but was just looking for something a bit fancier
(hence what you described where a userform can be pop up and the user can
type inforamtion into a texbox, and store it with the information to the
client (that is the key as I want to make sure that a specific comments stays
connected to a particular client name (identifed in columns A and B) of the
various rows in my spreadsheet.

My comment that you asked for clarity on:
spreadsheets within my workbook)

was meant to say that I'd like a more sophisticated way of including
comments than merely typing them into the various rows in Column AS, thus
making some of my rows quite large (as I do wrap the text). The idea of a pop
up also makes it easier on the readers eye, so that no one has to read 5-6
lines of comments in this column. My idea would be that the comments would
only be accessible when you "clicked" on a button which said comments (it
would open up the userform for viewing, editing or printing).

If you do know where I could access the code you referenced (as I didn't see
it in the forum when I looked), I would be very appreciative. Hopefully it's
easy to read and edit because I've never written code.

Thanks again,

Dan

 

[Guest]

Well, the comments typed in are not held in a hidden location or anything.
The userform acts as an interface and you program it to store the information
collected somewhere and for looking at existing information, you write the
code for it to pick that up and display it so it all looks like it is stored
in a secret place. You actually just store it on the worksheet. (or on a
hidden sheet - with your code).

These will give you some insights.

http://j-walk.com/ss/excel/tips/tip84.htm

http://www.contextures.on.ca/xlUserForm01.html


See this tutorial here
http://www.dicks-blog.com/excel/2004/09/linking_userfor.html

http://support.microsoft.com/default.aspx?scid=kb;en-us;829070
How to use Visual Basic for Applications examples to control UserForms in
Microsoft Excel


XL97: How to Use a UserForm for Entering Data (Q161514)
http://support.microsoft.com/?id=161514

XL2000: How to Use a UserForm for Entering Data (Q213749)
http://support.microsoft.com/?id=213749


Here are some other sources of information:

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.


http://support.microsoft.com/?id=168067
File Title: Microsoft(R) Visual Basic(R) for Applications Examples for
Controlling UserForms in Microsoft Excel 97
File Name: WE1163.EXE
File Size: 161742 bytes
File Date: 05/08/97
Keywords: kbfile
Description: This Application Note is an introduction to manipulating
UserForms in Microsoft Excel 97. It includes examples and Microsoft Visual
Basic for Applications macros that show you how to take advantage of the
capabilities of UserForms and use each of the ActiveX controls that are
available for UserForms

Peter Aiken Articles:
Part I
http://msdn.microsoft.com/library/en-us/dnoffpro01/html/IntroductiontoUserFormsPartI.asp
Part II
http://msdn.microsoft.com/library/en-us/dnoffsol02/html/IntroductiontoUserFormsPartII.asp

if you haven't written code and don't know how, I am not sure how doable it
would be in a short amount of time.