Prev: Exclude one folder in a filesearch
Next: From ListBox selection >labels on userform to show sheet cell valu
From: Atishoo on 22 Apr 2010 11:58
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
"Dave Peterson" wrote:

> 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 wrote:
> >
> > 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
> >
> > "Dave Peterson" wrote:
> >
> > > 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 wrote:
> > > >
> > > > 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
> > > .
> > >
>
> --
>
> Dave Peterson
> .
>
From: EricG on 22 Apr 2010 13:26
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


"Atishoo" wrote:

> 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??
From: Dave Peterson on 22 Apr 2010 13:33
That seems like a very reasonable solution to me -- well, until you scroll to
the right <vbg>.

Atishoo wrote:
>
> 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
> "Dave Peterson" wrote:
>
> > 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 wrote:
> > >
> > > 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
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > 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 wrote:
> > > > >
> > > > > 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
> > > > .
> > > >
> >
> > --
> >
> > Dave Peterson
> > .
> >

--

Dave Peterson
From: Dave Peterson on 22 Apr 2010 13:40
Another option may be to use a userform. The user could put it where it was the
least distracting.

Dave Peterson wrote:
>
> That seems like a very reasonable solution to me -- well, until you scroll to
> the right <vbg>.
>
> Atishoo wrote:
> >
> > 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
> > "Dave Peterson" wrote:
> >
> > > 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 wrote:
> > > >
> > > > 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
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > 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 wrote:
> > > > > >
> > > > > > 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
> > > > > .
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > > .
> > >
>
> --
>
> Dave Peterson

--

Dave Peterson
First  |  Prev  | 
Pages: 1 2
Prev: Exclude one folder in a filesearch
Next: From ListBox selection >labels on userform to show sheet cell valu