"Floating" text box?

Originally Posted by The1nk

I'm back with another brain-teaser!

One of my customers wants to have a floating text box (to serve as a legend for her data). The purpose is so that she(or any other user) can scroll through the data and still see the legend without any issues. I showed her how to freeze the panes, but that didn't suffice, for some reason. She wants a "Floating" text box.

So, if any of you genius' out there have any idea on how to do this? Whether the text box is truly floating or if it's anchored in a specific X, Y coord - I don't think it matters. Just any idea on how to do this would be great.

Thanks

Just use the ActivexControls from the VBE. Set up a form, place a TextBox control on the form and set the Modal property of the form to False.

HTH

Hi all,

Sad to say, I want exactly what The1nk's customer wants.

Sadder to say, I'm a total Excel novice. Well, not total, but "VBE" is not something I am familiar with. Neither is the "Controls ToolBox".

Starting with my regular Excel 2007 document, could someone show me step-by-step how to get this "floating text box", impervious to the vertical and horizontal scroll?

Many thanks!


Stanbridge

Nevermind, I've got it working now.

I had to make it so the Developer tab was showing (only in Excel 2007). Duh.

Works a treat, thanks guys!

Please provide more detailed instructions. Got first method is thread to work, but not 2nd with code for VBE. I am doing something wrong, but don't know where.
Thank you.

Hi all,

New to the forum, but not to Excel.

Will the above methods work for form buttons as well?

Cheers,
Francois

Originally Posted by royUK

Add a TextBox from the Controls ToolBox, in the Worksheet_SelectionChange event add this code

Please Login or Register  to view this content.

The textbox does not allow me to paste all of that text and does not allow me to press the enter button to skip to the next line. Do I type it all in one line.

I tried what Richard Buttrey recommend but now I am stuck trying to figure out how to make it appear on the sheet? How to I add it once I have the form created?

Sorry about that, I just did a Google Search and it brought be to this thread, so I logged in and replied. I just found the answer:

Quote by Rick Rothstein:

"I think you might do better with a UserForm containing a TextBox. Let me show you how to do this and you decide if you like the idea or not. Press Alt+F11 to go into the VB editor. Once there, click Insert/UserForm from its menu bar. When the UserForm window comes up, a small ToolBox window will open up also. Locate the TextBox icon in it (looks like the letters ab followed by a vertical bar) and click it, then click drag inside the UserForm to place the TextBox inside it. You can now resize the TextBox to fit from edge to edge within the UserForm. Next, click View/PropertiesWindow from the VB editor's menu bar, then click inside the TextBox you placed on the UserForm... now the Properties Window will be displaying the properties for the TextBox. There are lots of properties you can set here, but the one you probably will be most interested in is the MultiLine property (it allows text to be displayed on more than one line)... find it in the list and double click the word MultiLine to change its value from the default of False to the value True. Next, we need a macro in order to be able to show the UserForm. Click Insert/Module from the VB editor's menu bar and copy/paste the following into the code window that opened up...
Sub ShowTextBox()
UserForm1.TextBox1.text = "Line one" & vbLf & "Line two"
UserForm1.Show vbModeless
End Sub
Note the first line... you can assign any text here that you want to show up inside the TextBox when it is first displayed. My example shows how to set up multiple lines of text... just concatenate the built-in constant character for a Line Feed, which is vbLf, between the lines of text. You can do this for as many lines of text as you need. Or you can leave off the first ampersand and everything after it in order to show just a single line of text. Okay, we're done. Go back to the worksheet and press Alt+F8 to bring up the Macro dialog box, select ShowTextBox from the list and click the Run button. The TextBox will remain stationary on the screen, but you can move it click/dragging the title bar for the window that houses it. The TextBox is not "tied" to the worksheet, so you can select different worksheets and the TextBox will remain visible... you can even drag it off the worksheet if that is more convenient for you. To close the UserForm, just click its window's X icon in the upper right corner. Also, since ShowTextBox is a macro, you can assign it to a button if you want to make it more convenient to call up."

Yes, thank you. Sorry about that Richard.