screen tips and macros or automation

[nocompetitionblog]

In Microsoft Excel there are these things called screentips.

I know how to copy and paste a screentip as text to a desired
location.

right-click> edit> screentip> copy> paste in new, corresponding range
What I don't know is how to automate this process, as with macros,
because i have a large range in which almost every cell has it's own
screen tip.


That is, i want to isolate just the screen tips from a range of cells,
so that
I will be left with a range of cells that is the pure text version of
what
the original range of cells contained as screen tips.

how do i do this, or is it even possible? I'm open to alternative
methods.

 

[merjet]

I'm aware of "screen tips" associated with icons on toolbars, but not
with cells. A similar thing for a cell is a "comment".

The following VBA code worked for me, although the sizing not very
well.

'add comment to cell
Range("C9").Comment.Text Text:="insert text"
'change size of comment
Range("C9").Comment.Shape.ScaleWidth 0.9, msoFalse,
msoScaleFromTopLeft
Range("C9").Comment.Shape.ScaleHeight 1.1, msoFalse,
msoScaleFromTopLeft
'copy comment from one cell to another
Range("C12").Comment.Text Text:=Range("C9").Comment.Text
'clear a cell's comment
Range("C10").ClearComments

Hth,
Merjet

 

[nocompetitionblog]

thanks everyone, it's solved.
i solved it on my own with a little mousework and experimentation.

credit goes to myself and tim ogilvy, whose code i modified to get the
screentip
here is the original post with ogilvy's advice.

http://groups.google.com/group/microsoft.public.excel.programming/bro...

here is the modified version, by me, but based on mr. ogilvy's code.

Sub get_hlink_screentip()
Dim hlnk As Hyperlink
For Each cell In Range("a1:y25")
For Each hlnk In cell.Hyperlinks
   cell.Offset(100, 0).Value = hlnk.ScreenTip
Next
Next

End Sub- Hide quoted text -

- Show quoted text -

Tom Ogilvy., not tim. sorry.