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?

You could upload a workbook to our forum where we would be pleased to
look at it for you! :)


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=7641

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?

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?

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?

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?

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?