Force Scroll Bar Down on TextBox Control

[Excel Monkey]

I have a text box which I am appending text to via code. I want the ability
to force the scroll bar down to the bottom each time the text is appended. I
put the following code into the AfterUpdate Event. However this event does
not fire when adding text via code. The only way I can get it to work is if
I change the Userform .Enabled property to FALSE. However this disables the
buttons on the userform. So then I tried to simply set the Enabled property
of the Textbox to FALSE. Howevever when I do this, the textbox does not
scroll nor are the scroll bars visible. How do I allow the user to scroll,
force the scroll bar to the bottom and not disable any button on the form?

Private Sub TextBox1_AfterUpdate()
On Error Resume Next
lLine = TextBox1.CurLine
TextBox1.CurLine = lLine
On Error GoTo 0
End Sub

Thanks

EM

 

[OssieMac]

Untested but can't you simply place the code to move the scroll bar
immediately after the code you are using to append and not rely on an event
to run the code.

 

[Excel Monkey]

Yes I can do this. I have moved the code from the event procdure behind the
form to a Class Module. However the scroll bar does not seem to position
itself at the bottom. When I put the cursor over the lLine variable it shows
the correct value. However the scroll bar continues to stay at the top.
When my spreadsheet opens I show the form in modeless (i..e UserForm1.Show
vbModeless).


'********************************************
Private Sub SetScrollBar()
Dim lLine As Long
'UserForm1.TextBox1.SetFocus
lLine = UserForm1.TextBox1.CurLine
UserForm1.TextBox1.CurLine = lLine
End Sub

Thanks

EM

 

[Excel Monkey]

So here is what is happening. The sub below does not work in some instances
but does in others. Effectively I display a text box on the the open event
of a workbook. The textbox stays displayed and is appended with log info.
When I click onto cells, the textbox updates (via Class module Application
level events) but the SetScrollBar executes without forcing the scrollbar
down as it should.

Now I also have a "Hide" button on the userform that the Textbox is in.
This is to allow the user to reduce the size of the userform so that it does
not get in the way of the active window. When the user clicks on this button
(CommandButton2) the form's size is reduced an a button appears to allow the
user to expand the form again later (CommandButton3). When the user expands
the form and then once again clicks onto a cell in the spreadsheet it once
again fires the SetScrollBar sub - BUT IT WORKS.

It only works immediately on the first cell click, AFTER the user expands
the form using the CommandButton3_Click event. On the second and all
subsequent cell clicks, it reverts back to not working.


Private Sub SetScrollBar()
UserForm1.TextBox1.SetFocus
lLine = UserForm1.TextBox1.CurLine 'Public Variable
UserForm1.TextBox1.CurLine = lLine
UserForm1.TextBox1.SetFocus
End Sub
'*******************************************
Private Sub CommandButton2_Click()
Dim btn As Control
Me.Width = 10
Me.Height = 60
Me.TextBox1.Visible = False
With CommandButton3
.Height = 24
.Left = 12
.Top = 10
.Width = 60
End With
UserForm1.CommandButton3.Visible = True
End Sub
'*****************************************
Private Sub CommandButton3_Click()

Me.Width = 640
Me.Height = 180

With Me.TextBox1
.Height = 100
.Left = 12
.Top = 12
.Width = 600
End With

Me.TextBox1.Visible = True

With CommandButton3
.Height = 24
.Left = 12
.Top = 126
.Width = 60
End With

SetFormPosition

End Sub

Thanks

EM

 

[Excel Monkey]

I made the following changes an it seems to work.

Public Sub SetScrollBar()

'On Error Resume Next
UserForm1.TextBox1.Visible = True 'New
UserForm1.TextBox1.Enabled = True
UserForm1.TextBox1.SetFocus
lLine = UserForm1.TextBox1.CurLine
UserForm1.TextBox1.CurLine = lLine
UserForm1.TextBox1.Enabled = False

End Sub

 

[OssieMac]

The following sub does not make sense to me. lLine is saved to the current
CurLine value and then you are setting Curline to the same value so the
cursor should not move.

CurLine is the current line where the cursor is positioned.

Private Sub SetScrollBar()
UserForm1.TextBox1.SetFocus
lLine = UserForm1.TextBox1.CurLine 'Public Variable
UserForm1.TextBox1.CurLine = lLine
UserForm1.TextBox1.SetFocus
End Sub

You need to save CurLine to lLine variable immediately after entering the
last of the data in the TextBox1 while the cursor is still on the last line.
I have not been able to do that reliably because the user might move up
through the textbox to edit an error and then move to another control from
there.

From my testing it appears that you can only set the cursor to the specified
CurLine if the TextBox has focus. Therefore must SetFocus to the TextBox
first. Having said that, it also appears that it only works if done in the
last line of code executed before the End Sub like the following example. Any
code after the line of code to set the cursor to Curline will nullify the
CurLine setting:-

Sub test()
UserForm1.Show vbModeless
UserForm1.TextBox1.SetFocus
UserForm1.TextBox1.CurLine = 6 'Can use a variable in lieu of 6
End Sub

Next I found that using the above code to set the cursor to the last line
then it would only work if I used a value 1 less then should be required.
Example if 8 lines then should be 7 because of Zero to 7 line count. However,
I had to set it to 6 or I got an error. Also 6 actually set it to the last
line which should be 7.

Now if I run code from a Command button after the form is opened then I had
to set it 7 to get the last line like the following:-

Private Sub CommandButton1_Click()
Me.TextBox1.SetFocus
Me.TextBox1.CurLine = 7
End Sub

The 6 or 7 just does not make sense to me.

My next test was to use SendKeys to send a Ctrl/End when the control gets
focus. As much as I dislike the use of SendKeys it appears to be more
reliable and I don’t think that sending Ctrl/End after the TextBox has focus
will ever cause a problem. (Note: Must still SetFocus to the TextBox first.)

Sub test()
UserForm1.Show vbModeless
UserForm1.TextBox1.SetFocus
Application.SendKeys ("^{END}")
End Sub

The above code also worked well from a CommandButton as per the following:-

Private Sub CommandButton1_Click()
Me.TextBox1.SetFocus
Application.SendKeys ("^{END}")
End Sub

Just a little added extra. I also tried to use both the CurLine method and
Sendkeys method with the TextBox Enter Event. It does not work when you use
the mouse to click on the TextBox. In Help it says that Enter Event code
actually takes place before the real enter takes place and I am sure that
this is the reason for this. It does not actually have focus until the Enter
Event code is finished running.

Hope this all helps. It was an interesting experiment for myself but in the
end I didn’t progress all that far.