Keep text box visible

[Atishoo]

Hi all
Am wanting to keep a text box visible while scrolling, I cant use freeze
panes as I need the whole page to scroll behind a position locked text box.

I am thinking can i create a formula to trigger when scrolling that will
dynamically recalculate the "top" property of the text box to hold it in
position??

 

[Dave Peterson]

Excel doesn't have any event for scrolling.

But you could reposition the textbox after you've changed the selection.

If that's ok,

What's the name of the textbox?
Was it from the Drawing toolbar or from the Control Toolbox toolbar?

===
Someone did post an API/MSIE object (I think) that did do this, but it was too
complex for me.

 

[Atishoo]

Hi Dave

Its a forms menu text box not an active x.

Was wondering if trigering it as mouse over might work while scrolling given
that cels move past the cursor while scrolling (though that of course would
necessitate the cursor being over the set worksheet area at the time).
Text box is originally named "Text Box 1"

thanks

 

[Atishoo]

Aaach! that wont work either will it because mose move only applys to objects
and not forms objects!! blast

 

[Dave Peterson]

Do you mean a textbox from the Drawing toolbar?

There isn't a textbox on the Forms toolbar (unless you've modified that
toolbar).

And did you need help with the worksheet_selectionchange code?

 

[Atishoo]

Do you know something your not bloomin wrong. It is a text box that i
originally created in a word doc and copied across some time ago, as its
clearly not an active x object I always assumed it was a form object.
Its never simple!!

Am ok setting a selection change but i really need the text box to be
permanantly fixed.
Am thinking now im just going to have to bite the bullet re format my page
and use split or freeze pane.

thanks

 

[EricG]

Here's a quirky way you might do it, using the OnTime method.

I created a blank workbook, and then on "Sheet1" I added a textbox, with
default name "TextBox 1". The example checks the left edge of the text box
against the left edge of the upper left cell of the visible region, and if
they're not the same, the text box is moved. I set the timer to repeat every
second, so it's not exactly real-time. Note the single quotes inside the
double quotes for the OnTime call. This is the way to pass a parameter to
your procedure. I just included it as an example.

HTH,

Eric

In your Workbook_Open event:

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:01"), "'Move_TextBox1 0'"
End Sub

In a general module:

Sub Move_TextBox1(iParam As Integer)
Dim theCells As Range
'
If (ActiveWorkbook.ActiveSheet.Name = "Sheet1") Then
Set theCells = ActiveWindow.ActivePane.VisibleRange
If (Abs(ActiveWorkbook.ActiveSheet.Shapes("TextBox 1").Left -
theCells.Left) > 0.1) Then
ActiveWorkbook.ActiveSheet.Shapes("TextBox 1").Left =
theCells.Left
End If
End If
'
' Reset the timer
'
Application.OnTime Now + TimeValue("00:00:01"), "'Move_TextBox1 0'"
End Sub

 

[Dave Peterson]

That seems like a very reasonable solution to me -- well, until you scroll to
the right <vbg>.

 

[Dave Peterson]

Another option may be to use a userform. The user could put it where it was the
least distracting.