Hi Phil
I remember you well. With just the H of your surname as your ID, I hadn't
picked up that it was you.
I trust all is well in Italy.
Take a look at the code Debra Dalgleish has about handling comments, on her
site.
http://www.contextures.com/xlcomments03.html
As Debra says, there is no way of switching off the indicator, but you could
use her trick of drawing a triangle shape of matching size and placing over
the indicator. If you set the colour to NoFill, then that may help.
Debra shows code for applying and removing the shapes.
If you would like, feel free to send me a sample of what you are trying to
do, with some further detail, and I would be more than happy to take a look
and see if I can come up with any other ideas.
In case you have lost my email address it is
roger at technology4u dot co dot uk
--
Regards
Roger Govier
"Phil H." wrote in message
...
Hi Again Roger - we met in London 2006 at the Excel conference, I'm the
fly
fisherman €“ gave thought to staying at your place in spring 07.
Will try the comment idea €“ seems totally feasible. Is there a way to
eliminate the red comment symbol in the upper right corner? Might
require
some sort of search macro to identify cells with comments and, say,
temporarily apply a background color. These symbols could make the
worksheet
very €œbusy,€ adding confusion to beginning users.
Basically, my purpose is to give users an on-screen definition of a code
(there are many columns with similar situations), without having to search
elsewhere for the information. Again, I'm dealing with 89 columns and
8000+
rows (admittedly way too much), so my current task is to make the
worksheet a
user-friendly environment - which currently it is not. The user's task is
to
go through the 722,000+ cells and clean out errors - where incompatible
codes
exist (on a row). Im putting together a code matrix to set the rules
for
the exercise. Will probably be looking for help next week after I receive
the latest spreadsheet. So, for now users are filtering and with a very
short lead time for scrub completion, on-screen helps seem my only
contribution. If you have any other ideas, let me know. Meanwhile, Ill
try
the comments macro. This is a neat idea for other worksheets €“ will put
it
in my library. Thanks for the help, Roger.
"Roger Govier" wrote:
Hi Phil
Following on from Peter's idea, I have played about with adding comments
to
the cell.
If you have a List (in my case on Sheet2 column A) for your DV items,
with a
list of descriptions for those items along side in column B (Sheet2),
then
the following event code on your sheet with the DV (Sheet1), will insert
the
comment to the cell depending upon what has been selected.
Hovering over the cell will cause the Comment to be displayed.
Private Sub Worksheet_Change(By Val Target As Range)
Dim messg As String
If Not Intersect(Target, Range("A:A")) Is Nothing Then
messg = WorksheetFunction.Index(Sheets("Sheet2").Range("B: B"), _
WorksheetFunction.Match(Target.Value,
Sheets("Sheet2"). _
Range("A:A"), 0))
On Error Resume Next
Target.Cells.AddComment
Target.Cells.Comment.Visible = False
Target.Cells.Comment.Text Text:="" & Chr(10) & messg
On Error GoTo 0
End If
End Sub
Obviously, change the ranges to suit your situation.
--
Regards
Roger Govier
"Phil H." wrote in message
...
Peter, I think "List" may be the idea. I will play with the
validation
idea. In the meantime, I was wondering if we can go behind the scenes
and
add a second column to the filter dropdown list. This would be
perfect,
if
doable, because of the many many columns with codes. The further I get
into
this, the more I think I will press for converting this to Access -
after
the
current situation passes.
"Peter T" wrote:
Have you considered using Data Validation / List in all your input
cells,
where the list refers to your codes (could be a ref or a named range).
In the next column you could have a lookup formula that returns the
description based on the code in the input cell (or blank if code cell
is
empty).
Regards,
Peter T
"Phil H." wrote in message
...
Thanks, Pete - not encouraging at this point. "Normal" users will
not
be
able to remember 365 codes. Can you think of another way to
approach
to
the
problem? Could a lookup be used somehow? Or maybe a dropdown in
the
header
row?
"Peter T" wrote:
Tricky, not impossible but effectively not viable.
There are no directly trappable mouse over or hover events in
cells.
Using a
timer could periodically check mouse coordinates every (say) 0.2
sec'
and
if
same conclude the mouse is stationary (could sub class windows
mouse
events
but not stable in VBA).
Having determined the mouse is not moving next task is to relate
mouse
coordinates to cell, it is doable with one of three distinct
approaches
(not
sure about xl2007) but none are straightforward.
Then the final task is to display something like a tooltip, rather
than a
form or some other object. I'm almost sure there is no way to do
that
purely
with VBA.
Of course, could do almost anything in a selection change event
(except
the
tool tip, temporary dynamic comment perhaps).
Regards,
Peter T
"Phil H." wrote in message
...
MS Excel 2003, Workbook name "2008 Results."
In worksheet named "Inventory," Column M has alpha-numeric
codes -
the
worksheet contains 8000+ rows.
In worksheet named "Function Codes," 365 a-n codes are held in
column
B,
with their corresponding text descriptions in column C.
What is needed: when the user hovers the cursor over a cell in
"Inventory"
column M, Excel finds the a-n code in worksheet "Function Codes"
column
B,
and displays the corresponding text from column C the same way as
in
a
hyperlink "Screen Tip."
Finally, the user must be able to click on a column M cell, make
an
a-n
code
change, and the new text description appear when hovering over
the
changed
cell (after the correction has been entered).
What would the macro code be and where would it be placed?