Floating Text Box

[thereuare]

Don't know if i'm using the term correctly, but i'd like a text box to appear when the cursor is over a certain cell, and disappear when it's no longer over that cell.(similar to putting the cursor over a line in a graph and it shows you the data points of that position on the graph)

Is this possible in VBA? Is this called a floating text box?

 

[RogerC]

Hi thereuare. Have you tried using the 'Comments' feature of Excel? It sounds like that is what you want. Go to the Insert menu, select 'Comment'. Enter the text you would like to have appear. At this point you can also right click on the Comment to change it's appearance. Next go to the View menu, to de-select 'Comments' so that your Comments only appear when you have the cursor over the cell they are attached to. Hope that helps.
This message was edited by RogerC on 2003-01-03 10:00

 

[thereuare]

THANKS, that's exactly what i need, but 2 questions remain:

1) Each cell that has a comment box has a red flag in the corner, showing that it has a comment box attached. Is it possible to NOT have this flag (or not show it). If not, how about changing the properties of the flag so that it's white, and therefore won't be seen as it will blend in with the background of the cell itself?

2) In the comment box i want to show a date, which comes from a variable in a CSV file. Therefore the date is in 'regular' number form (ie- 37466 instead of 07/29/02). What is the code to have this appear in date form within the text box? I tried something like variablename.NumberFormate=mm/dd/yy but it didn't work. Is it possible to change the format within the text box, or is it necessary to change the format on the CSV file before i start using it within the text box.

Thanks again for your answers. I was going down the wrong path, trying to learn about mouse rollover effects (which require Java which i know NOTHING about) and this is MUCH easier, assuming i can fix the above two situations.

 

[Richie(UK)]

Hi thereuare

To get rid of the Comment indicator go to the Tools menu and select Options. On the View tab you will see a Comment section, tick the None box to remove the indicator.

HTH

 

[thereuare]

But clicking on "None" not only removes the indicator, but also takes away the comments when the cursor is over that cell.

I need the comments to appear when the cursor is placed over the cell, but i don't want any indicator on the cell itself (or at least hide the indicator OR have the indicator be the same color as the cell so that it's not visible).

Your input and suggestions are appreciated.

 

[RogerC]

1) According to this thread (look under the subject 'Changing the Color of your Excel Cell Comment') the red comment triangle can't be changed. http://www.mvps.org/dmcritchie/excel/colors.htm#triangles"

I know Jack in the UK was trying to do this at one time... not sure if he was able to find a way. Maybe he'll see this.

Here is some handy code that will automatically resize your comments to fit the text in them... (not what you wanted, but I thought it usefull). http://www.mrexcel.com/board/viewtopic.php?topic=4582&forum=2

2) Sorry, I know nothing about CSV files. Hopefully someone will help you with this one.

 

[Andrew Poulsom]

For your date problem try something like this:

Sub Test()
    Const MyVar As Long = 37466
    Range("A1").Comment.Text Text:=Format(MyVar, "mm/dd/yy")
End Sub

 

[thereuare]

Thanks for the info on the date... but as i have the following in the text box, DateVariable, Value1Variable, Value2Varialbe, how do i format the first variable but not the second/third?

Using the code above DOES give me the date in the correct format, but doesn't show the other variables.

Still working on other resources to try and get the triangles gone <grrr>

Thanks to all for help thus far!

 

[Andrew Poulsom]

Just use the concatenation operator to combine all your variables:

Range("A1").Comment.Text Text:=Format(MyVar, "mm/dd/yy") & " " & Value1Variable & " " & Value2Variable

 

[thereuare]

Actually found a much easier way:

FormatDateTime(Variable)

The above gives me the date as i need it and doesn't effect any other text in the same box.

Thanks for everyone's help.