Floating Text Box in Excel 2K3/2K7

[Raymond W.]

I am wanting to create a floating text box in Excel 2003/2007 but am
currently stuck. The idea is that I will be able lock a text box with a few
bullet points in the top right of the screen and that it will remain visible
in that position regardless of where I scroll in the worksheet. Locking cells
instead, or using Headers/Footers is not an option here.

For this purpose, the text box has a name of "Box". I found this code online
(along with several other examples) and replaced the name of the text box
with my own, but could not get it (or any other example) to work. The
following example gives the error "Method or Data member not found" and
highlights ".Box"...

Any help would be appreciated.


Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With ActiveWindow.VisibleRange
Me.Box.Top = .Top
Me.Box.Right = .Right
End With
End Sub

 

[ozgrid.com]

Place your TextBox on row 1 and freeze panes.

 

[p45cal]

It's likely that
box
was assigned to be the textbox elsewhere in the code. In your case yo
might do it within the same sub:


VBA Code:
--------------------


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim box
Set box = Shapes("box")
Set xxx = ActiveWindow.VisibleRange
With xxx.Resize(, xxx.Columns.Count - 1)
box.Top = .Top
box.Left = .Left + .Width - box.Width
End With
End Sub

--------------------




I am wanting to create a floating text box in Excel 2003/2007 but am

 

[Raymond W.]

Thanks for the responses, both of you. Freezing panes/cells is not an option
here for aesthetic reasons (not my spreadsheet).

As for the name 'box', I had actually used another name originally that was
far more original "MyAwesomeBox", but got tired of typing it in while trying
various snippets of code from the web to make this work. That is when I
changed it to 'box'. I will however try your suggestion when I get home in
about 16 hours. The name of the box does not matter to me honestly.

Thanks for your input, we'll see how it goes.

It's likely that
box
was assigned to be the textbox elsewhere in the code. In your case you
might do it within the same sub:

 

[Raymond W.]

HA! I haven't had a chance to work on this until today. The code provided
works for the most part, the only feature lacking is to have it scroll when
the user scrolls the work sheet, so it always remains visible, not just
bouncing to the top on selection change. I don't know that this is possible
and the box works as is. If anyone has a solution, thanks, if not - I am
still happy. Great job mate! I changed the name of the box, and the code
accordingly, so here is what I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim box
Set box = Shapes("boxing")
Set xxx = ActiveWindow.VisibleRange
With xxx.Resize(, xxx.Columns.Count - 1)
box.Top = .Top
box.Left = .Left + .Width - box.Width
End With
End Sub

It's likely that
box
was assigned to be the textbox elsewhere in the code. In your case you
might do it within the same sub: