Textbox scrollbar settings

[Chris Watts]

I have a userform whose main component is a TextBox. It is locked as the
user is only permitted to read not edit the data there - that is done in the
main spreadsheet and the userform is modeless. The TextBox has a vertical
scroll bar to permit the user to navigate around its contents. The user
will not normally place the focus on the Textbox contents itself but just
use the scrollbar..

The data in the textbox is updated programattically (using vba). When this
happens the box contents scroll back to the top - I do not want this to
happen. I want the scroll position to remain as the user has set it. How
can I achive this? I have tried SelStart and SelLength but these don't
achive this - the before and after update values are always 0. I have tried
reading and resetting CurLine (with SetFocus) but no luck there - I actually
get an error if the user has never placed the cursor in the textbox.
So how can I read the current setting of the scrollbar and then reinstate
it?

Using Excel 2007

TIA
Chris

 

[isabelle]

hi Chris,


you have to give focus to the TextBox before use CurLine
Me.TextBox1.SetFocus

to read the current cursor position
x = Me.TextBox1.CurLine

for placing the scroll bar in the middle
Me.TextBox1.CurLine = Me.TextBox1.LineCount / 2

 

[Chris Watts]

hi Chris,


you have to give focus to the TextBox before use CurLine
Me.TextBox1.SetFocus

to read the current cursor position
x = Me.TextBox1.CurLine

for placing the scroll bar in the middle
Me.TextBox1.CurLine = Me.TextBox1.LineCount / 2

Hi Isabelle,
Thanks for that. I have tried just what you suggest and it only seems to
work if the user has actually placed the cursor within the textbox at some
point - SetFocus is necessary but not enough If the user has not done so
but has only repositioned the text view by using the scrollbar then it does
not work. In the latter case an error is genereated telling me that there
was a problem accessing the CurLine property. My presumption is that in the
latter case CurLine is not actually set until the cursor is placed within
the textbox by the user. The problem that I face is that the user will only
be viewing the text and not changing it within the textbox and so it cannot
be guaranteed that the cursor will have ever been placed within the textbox.

I will, of course, try your suggestion again just in case I have missed
something.

Thanks again
Chris

 

[Chris Watts]

Hi Isabelle,
Thanks for that. I have tried just what you suggest and it only seems to
work if the user has actually placed the cursor within the textbox at some
point - SetFocus is necessary but not enough If the user has not done so
but has only repositioned the text view by using the scrollbar then it
does not work. In the latter case an error is genereated telling me that
there was a problem accessing the CurLine property. My presumption is that
in the latter case CurLine is not actually set until the cursor is placed
within the textbox by the user. The problem that I face is that the user
will only be viewing the text and not changing it within the textbox and
so it cannot be guaranteed that the cursor will have ever been placed
within the textbox.

I will, of course, try your suggestion again just in case I have missed
something.

Thanks again
Chris

Hi Isabelle,
I have now found a way to achieve almost what I need. There are some issues
to bear in mind though!
Firstly, as you say, reading and writing the CurLine property requires the
textbox to have the focus.
Secondly, if the user has never placed the cursor within the textbox then
CurLine will be Null - and this causes a error which needs trapping and
setting of CurLine to 1. (But if you check the value of CurLine using a
MsgBox statement it will tell you that it is 1.).
Thirdly, setting the value of Curline does cause the textbox to scroll so
that CurLine is visible and at the bottom of the box. That is not quite the
same as restoring the scrollbar setting but I can live with that.
Lastly, if the user has never placed the cursor in the textbox then this
technique won't wotk

I will explore, in slow time, as to whether I can use WinAPIs to read/set
the VerticalScrollPercentage property for the textbox window.

Thanks for you suggestion.
cheers
Chris