Making sure scrollable Textbox is positioned at the top

[Ludwig]

On one worksheet I have a scrollable textbox. I am finding it stays 'scrolled' where the last person left it positioned, be it the middle or bottom or wherever. I want each time the worksheet is activated to ensure the shown text starts from the top.

I can make the textbox visible or hidden using
<CODE>
ActiveSheet.Shapes("Instructions").Visible = False
ActiveSheet.Shapes("Instructions").Visible = True
<CODE END>

I've tried various options found from a search, but I do not understand the syntax. They all refer to a form -
<CODE>
TextBox1.SetFocus
TextBox1.SelStart = 0
<CODE END>

but I do not understand what "TextBox1." is on the front. At the same time, I found "Textbox1.Visible" was the way to show/hide the texbox, and my coding method for that works. But it doesn't work for '.SetFocus', '.Curline', 'SelStart' or any other set of (1 or more) commands to achieve what I want.

Can someone advise what the syntax should be? Thanks!

 

[MARK858]

If it is actually a shape then try the code below. Code goes in the Worksheet module of the sheet with the shape.

Private Sub Worksheet_Activate()
    With ActiveSheet.Shapes("Instructions")
        .Left = 3
        .Top = 2
    End With
End Sub

 

[Coding4Fun]

Maybe?

Sub Test()

'For Active X Control
ActiveSheet.ListBox1.Selected(0) = True

'For Form Control
ActiveSheet.ListBoxes("List Box 1").Selected = 1
End Sub

TextBox1.etc is referring to a textbox either in a VBA Form or within a Sheet. "TextBox1" is the name of the textbox. In my example I am working with Listboxes, I named the Active X Control "ListBox1" and the Form Control is "List Box 1"

 

[Jaafar Tribak]

Have you tried this :

Code in the worksheet module :

Private Sub Worksheet_Activate()
    Me.Instructions.Activate
    Me.Instructions.SelStart = 0
End Sub

 

[Ludwig]

If it is actually a shape then try the code below. Code goes in the Worksheet module of the sheet with the shape.

Private Sub Worksheet_Activate()
    With ActiveSheet.Shapes("Instructions")
        .Left = 3
        .Top = 2
    End With
End Sub

It is actually defined as a textbox (i.e. Insert / Text Box), not a shape, so I could add scrollbars to it.

The code you suggests runs without error - but all it does is move the textbox to that position on the screen. I had located the top left corner by $H$3, and the code moved it into A1.

 

[Ludwig]

Maybe?

Sub Test()

'For Active X Control
ActiveSheet.ListBox1.Selected(0) = True

'For Form Control
ActiveSheet.ListBoxes("List Box 1").Selected = 1
End Sub

TextBox1.etc is referring to a textbox either in a VBA Form or within a Sheet. "TextBox1" is the name of the textbox. In my example I am working with Listboxes, I named the Active X Control "ListBox1" and the Form Control is "List Box 1"

I don't understand the code. I tried the Active X Control one as is" but it failed - "Run-time error '438': Object does not support this property or method.". I changed "Listbox1" to the name of my textbox. I would have expected some form like textbox("textboxname") at that point.

The Form Control one also failed - "Run-time error '1004': Unable to get the ListBoxes property of the Worksheet class".

 

[Ludwig]

Have you tried this :

Code in the worksheet module :

Private Sub Worksheet_Activate()
    Me.Instructions.Activate
    Me.Instructions.SelStart = 0
End Sub

Yay!!! That worked!! Thanks so much!

 

[Coding4Fun]

I don't understand the code. I tried the Active X Control one as is" but it failed - "Run-time error '438': Object does not support this property or method.". I changed "Listbox1" to the name of my textbox. I would have expected some form like textbox("textboxname") at that point.

The Form Control one also failed - "Run-time error '1004': Unable to get the ListBoxes property of the Worksheet class".

I think I may have misunderstood your request and assumed when you said a scrollable textbox you meant a listbox. Glad you found a solution.

 

[MARK858]

It is actually defined as a textbox (i.e. Insert / Text Box), not a shape, so I could add scrollbars to it.

The code you suggests runs without error - but all it does is move the textbox to that position on the screen. I had located the top left corner by $H$3, and the code moved it into A1.

Just to point out you adjust the code I posted by amending the 2 numbers to the position you want the shape in when the sheet activates, it went to A1 only because you didn't state where you wanted it..(and I used Shapes because the code you used in post number 1 uses shapes for the visible false/true so it obviously finds it because a textbox is a shape).

Happy though that the code Jaafar Tribak posted works for you