Making sure scrollable Textbox is positioned at the top

Ludwig

Board Regular
Joined
Apr 7, 2003
Messages
80
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
If it is actually a shape then try the code below. Code goes in the Worksheet module of the sheet with the shape.

Code:
Private Sub Worksheet_Activate()
    With ActiveSheet.Shapes("Instructions")
        .Left = 3
        .Top = 2
    End With
End Sub
 
Upvote 0
Maybe?

Code:
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"
 
Upvote 0
Have you tried this :

Code in the worksheet module :
Code:
Private Sub Worksheet_Activate()
    Me.Instructions.Activate
    Me.Instructions.SelStart = 0
End Sub
 
Upvote 0
If it is actually a shape then try the code below. Code goes in the Worksheet module of the sheet with the shape.

Code:
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.
 
Upvote 0
Maybe?

Code:
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".
 
Last edited:
Upvote 0
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. :)
 
Last edited:
Upvote 0
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 :biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top