# Floating Text Boxes in Excel



## Poptart7912 (Aug 13, 2003)

I am trying to create floating text boxes within a spreadsheet but have run into a wall...I would like for the boxes (oh, they have hyperlinks to other sheets within the workbook) to move as the user scrolls through the worksheet. Any ideas?

I do not have a lot of vb knowledge so you might have to break it down Barney-style for me.

Thanks for your help.


----------



## mrwendal (Jul 25, 2001)

I can't do what you're trying to, but perhaps some other users have some ideas for you.

The closest i could get is this:

Hit Alt+F11 in the spreadsheet to go to VB.
Hit F7 to get code window

Paste following code..

Edited: *Code error - ignore this code & see updated post below this one* 
*
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveWindow
ActiveWindow.ScrollRow
TextBox1.Top = Target.Top
End With
End Sub*

Close VB editor.

now, when you scroll down the page, the textbox ("textbox1") doesn't move. However, if you click in a cell further up/down the page, the text box jumps to that row but remains in the same column

It's a start if nothing else!


----------



## mrwendal (Jul 25, 2001)

Apologies,

just use this code:

*
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
TextBox1.Top = Target.Top
End Sub
*


----------



## Poptart7912 (Aug 13, 2003)

No apologies!! I am thankful there is someone who can visualize what I'm trying to do. Everyone in the office looks at me cross - eyed when I start talking about cool Excel stuff. 

I get an error (even on the corrected code) -- Ambiguious Name Detected: Worksheet_SelectionChange.

Would the code be different for a shape (retangle) than for a text box -- other than the name TextBox1? I'm wondering if an autoshape can be more easily manipulated....


----------



## mrwendal (Jul 25, 2001)

Sorry, I thought you meant the text box option on the Control Toolbox as opposed the 'drawn' textbox.

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Shapes("Text Box 1").Top = Target.Top
End Sub


This presumes your drawn text box is called Text Box 1.

Make sure you enter the code in the correct part of the vb window.

hit Alt+F11
Click Ctrl + R 

in the project window, double click the name of your worksheet and then paste this code in the window that appears.

Does this help?


----------

