ActiveX Textbox on Sheet - Scroll Using VBA

[Jeffrey Mahoney]

I have several ActiveX Textboxes that are linked to several cells with formulas to change the text based on another cell. They are locked and my sheet is protected. This allows the user to click the text box and scroll if the text lines are longer than the control.

Issues:
1. Users don't know how to click away from the control to scroll down through the rest of the Sheet form
2. Sometimes the textboxes resize themselves (I've added code to resize them to their default upon focus)

I would rather use a different control to scroll through the text.

How do I control the scroll bar for an ActiveX Textbox using VBA?

 

[Jaafar Tribak]

Setting the CurLine Property will move the scroll bar.

Sub Demo()
    With Sheet1.TextBox1
        ' Set focus to the TextBox
        .Activate
        ' Jump to the first line
        .CurLine = 0
        ' Wait 3 secs
        Application.Wait Now + TimeSerial(0, 0, 3)
        ' Jump to the last line
        .CurLine = .LineCount - 1
    End With
End Sub

 

[Jeffrey Mahoney]

Jaafar,

That does work. So, there's no way to do that without activating the textbox?

 

[Jaafar Tribak]

That does work. So, there's no way to do that without activating the textbox?

No as far as I know ... What is the issue with activating the textbox?

 

[Jeffrey Mahoney]

No as far as I know ... What is the issue with activating the textbox?

Well, this may seem unimportant.

When I give focus to the textboxes, they shift in size and location. I created a macro to put them back when they get focus. That kind of solves that.

But, users (with less skill on how to navigate a worksheet) struggle to click away from the control so they can scroll further down the form.

I thought it would be great to have a scrollbar that controls the textbox without giving focus to it.

 

[Edgar_]

You could store the current focus before scrolling, then set the focus on the textbox, scroll and get back to the previously stored focus. Do you have a screenshot, video or file as sample of what you want to do?

 

[Jaafar Tribak]

Yes. As pointed out by Edgar_, cache the selected range before activating the textbox and get back to the cached range afterwards. Disabling ScreenUpdating would be very useful here to prevent worksheet jumping.

An yes, a screenshot or video sample would be useful.