I have a user that needs to be able to hover over a cell and see the entire
contents of the cell. We thought it might be beneficial to create a macro
that would copy the entire contents of the cell and create a comment (since
the cells only show 250 characters and comments can hold more). We would
need this for each and every cell. Any ideas? I need this ASAP. Thanks.

Lots of options for you; too many to list here.
Check out this link:
http://www.contextures.com/tiptech.html

Look for 'Comments'. You will find more than a dozen useful resources.

Regards,
Ryan---

--
RyGuy


"shell" wrote:

I have a user that needs to be able to hover over a cell and see the entire
contents of the cell. We thought it might be beneficial to create a macro
that would copy the entire contents of the cell and create a comment (since
the cells only show 250 characters and comments can hold more). We would
need this for each and every cell. Any ideas? I need this ASAP. Thanks.

If your user just clicks on the cell, the entire contents will be visible in
the formula bar. That is by far the easiest solution. However, if you must
display the contents in a cell comment when the mouse hovers over the cell,
here is a solution. I have adapted it from a reply by Mike H to a similar
question. Mike wrote:

"There is no mouse over event in Excel but you can do it with this code in a
standard module. To activate it run the sub 'Hook mouse'. To deactivate it
run the sub 'unhook mouse'. Change the range in the hook mouse sub to
whatever you want.

I can't remember where I copied this code from and the source isn't credited
in the comments so apologies and thanks to the original author."

Here is the adapted code:

Option Explicit

Declare Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA"
(ByVal idHook As Long, ByVal lpfn As Long, _
ByVal hmod As Long, ByVal dwThreadId As Long) As Long
Declare Function CallNextHookEx Lib "user32" (ByVal hHook As Long, ByVal
nCode As Long, ByVal wParam As Long, lParam As Any) As Long
Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As Long) As
Long
Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long

Private Type POINTAPI
x As Long
Y As Long
End Type

Const HC_ACTION = 0
Const WH_MOUSE_LL = 14
Const WM_MOUSEMOVE = &H200

Dim hhkLowLevelMouse As Long
Dim blnHookEnabled As Boolean
Dim udtCursorPos As POINTAPI
Dim objCell As Variant
Dim objTargetRange As Range

Public Function LowLevelMouseProc _
(ByVal nCode As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
'\\ Prevent error if objCell is Nothing
On Error Resume Next
If (nCode = HC_ACTION) Then
'\\ when Mouse is moved
If wParam = WM_MOUSEMOVE Then
'\\ Process WM_MOUSEMOVE message first
LowLevelMouseProc = False
'\\ Get Mouse Pointer location in Screen Pixels
GetCursorPos udtCursorPos
'\\ Get Cell under Cursor
Set objCell = ActiveWindow.RangeFromPoint(udtCursorPos.x,
udtCursorPos.Y)
If Len(objCell.Formula) 0 Then
objCell.AddComment
objCell.Comment.Visible = False
objCell.Comment.Text Text:=objCell.Formula
objCell.Select
End If
End If
Exit Function
End If
'\\ Call next hook if any
LowLevelMouseProc = CallNextHookEx(0, nCode, wParam, ByVal lParam)
End Function

Sub Hook_Mouse()
'\\ Prevent Hooking more than once
If blnHookEnabled = False Then
'\\ Change this Target range address as required
'Set objTargetRange = Sheets("Sheet1").Range("A1:A20")
hhkLowLevelMouse = SetWindowsHookEx _
(WH_MOUSE_LL, AddressOf LowLevelMouseProc, Application.Hinstance, 0)
blnHookEnabled = True
End If
End Sub

Sub UnHook_Mouse()
If hhkLowLevelMouse < 0 Then UnhookWindowsHookEx hhkLowLevelMouse
'\\ reset Flag
blnHookEnabled = False
End Sub

Sub DelAllComments()
'Deletes all comments on the active sheet
Cells.Select
Selection.ClearComments
End Sub

I have commented out the 'Set objTargetRange' line. I have also added a
macro, DelAllComments, to delete all the comments on the active sheet if
desired.

Put all this code in a VBA module in your workbook. To run any of the
macros, fro your worksheet select Tools Macro Macros. Click on the
macro you want to run, then click Run (pre-Excel 2007 directions).

If you are new to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/200...e-elses-macro/

Hope this helps,

Hutch

"shell" wrote:

I have a user that needs to be able to hover over a cell and see the entire
contents of the cell. We thought it might be beneficial to create a macro
that would copy the entire contents of the cell and create a comment (since
the cells only show 250 characters and comments can hold more). We would
need this for each and every cell. Any ideas? I need this ASAP. Thanks.

A cell can hold 32767 characters but only 1024 of those will be visible in
the cell so your 250 is incorrect.

To add a Comment to a cell with the text from that cell.

Sub Comment_Add()
Dim cmt As Comment
Dim r As Range
For Each r In Range("D3:D100") 'adjust to suit
Set cmt = r.Comment
If cmt Is Nothing Then
Set cmt = r.AddComment
cmt.Text Text:=r.Text
End If
Next r
End Sub


Gord Dibben MS Excel MVP

On Thu, 11 Dec 2008 09:29:01 -0800, shell
wrote:

I have a user that needs to be able to hover over a cell and see the entire
contents of the cell. We thought it might be beneficial to create a macro
that would copy the entire contents of the cell and create a comment (since
the cells only show 250 characters and comments can hold more). We would
need this for each and every cell. Any ideas? I need this ASAP. Thanks.