cell overflow problems

  • Thread starter Thread starter jasonsweeney
  • Start date Start date
J

jasonsweeney

I have four columns in a spreadhseet. In each cell in these columns,
there is a sentence. Some of the sentences are longer than others.
The width of the columns has to stay the same.

For reasons I won't get into, I cannot "wrap text" to make the full
sentences visable. I cannot shrink font size anymore than I have. I
cannot elongate the cells (vertically) to make the full sentences
visable.

Is there any way in VBA to make the full sentences "float" if the user
places their mouse pointer over the cell?

Any other ideas?

(I also considered the possibility of using comments....I have linked
comments to cell contents before, but I would prefer not to do this)
 
If the sentences don't have to change then go to
Inser
Commen
delete anything that Excel automatically puts in the text box that appears and retyp
the sentence that is in the cell. Reshape the text box to be long and short
The user would then see the sentence that is in the cell
If the sentences change then VBA can add the comment later to the cell.
 
Yes. This is certainly a way to do it. But I would prefer not to us
the comments.....all the little comment indicators will make th
spreadhseet very "busy".

I was wondering if there was a way to do it using VBA to actually mak
the cell contents float if the user rests their mouse over the cel
 
What I am looking for is a "floating sentence" kind of like the floating
messages one gets when in the Visual Basic Editor when you place your
mouse pointer over a Range("xx").text or other argument and the little
yellow drop down comes down telling you what text is in range "xx".

I just want that little yellow drop down in the spreadsheet and the
content of the drop down is the full sentence in the cell under your
mouse pointer........
 
Now that I think about it, another solution would be to use comment
if:

(1) There was a way to make the color of the comment indicator whit
(or clear, or otherwise invisble to the user)

(2) you can use VBA to populate the comment for a given cell with tex
from that cell (or a different cell)

If anybody knows how to do these things, that would accomplish m
goal.

Thanks
 
I played with some code and if you need to modify the sentences, this works pretty well.
Put this macro on the ThisWorkbook sheet. To activate you just double click on the cell that needs the comment added
you could add IF statements if you need to limit the cells that can be changed either by individual cells or specific columns or rows ( IF LEFT(TEMP,2) = $A THEN .... would limit the macro to column A only)

dim TEMP, COM as string
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
TEMP = Target.Address
' OPTIONAL IF STATEMENT HERE
COM = Range(TEMP)
Range(TEMP).Select
On Error GoTo 10
Range(TEMP).AddComment
Range(TEMP).Comment.Visible = False
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range(TEMP).Comment.Shape.ScaleHeight 0.3, msoFalse, msoScaleFromTopLeft
Range(TEMP).Comment.Shape.ScaleWidth 2.78, msoFalse, msoScaleFromTopLeft
10 Range(TEMP).Comment.Text Text:= COM
ActiveCell.Next.Select
End Sub
 
Thanks!


That works well, except I still have to deal with annoying red
triangles.....perhaps I will overlay white triangle graphicobjects as a
workaround....

I think what I really want is called a "Screen tip" where the content
of the screen tip is the cell value.
 
you can't do (1)

Sub CreateComment()

For Each Cell In Range("B9:B13")
Cell.AddComment Text:=Cell.Value
Next
End Sub
 
Try adding the following code between those lines. It will place the comment in the cell for as long as you set the time and then delete the comment. You will be able to see the comment for 5 or 10 seconds and it will clear. If you need to see it again just double click again

10 Range(TEMP).Comment.Text Text:=COM < --- existing code

Start = Timer
Do While Timer < Start + 5
DoEvents ' Yield to other processes.
Loop

Selection.ClearComments


ActiveCell.Next.Select <--- existing code
End Sub
 
Alright....I have dumped this code into Sheet Code..... is Optio
Explicit.....

I jest keep getting errors..."variable not defined"....


Comment A -- Also, I get errors when there are already comments in th
cell, so I had to include this delete comment command.....Problem is
this doesn't work if there arn't already comments in the cell...I trie
to IF THEN the condition, but couldn't get the code right....

____________________________________
Sub CreateComment()
For Each Cell In Range("B28:B52")
With Cell
.Comment.Delete < ---------------------- Please See Comment A
.AddComment
.Comment.Visible = True
.Comment.Text Text:=Cell.Value
.Comment.Visible = False
.Comment.Shape.ScaleHeight 0.2, msoFalse, msoScaleFromTopLeft
.Comment.Shape.ScaleWidth 4, msoFalse, msoScaleFromTopLeft
End With
Next

End Su
 
Back
Top