Window scroll issue

irresistible007

Board Regular
Joined
Nov 24, 2005
Messages
173
I have a Text box and a command button on my sheet (sheet has a freez panes so as the controls should always be visible), and following code to make them alive:

Private Sub CommandButton1_Click()
Dim rng As Range

Set rng = ActiveWorkbook.Sheets("Sheet1").Range("G65536").End(xlUp)

rng.Offset(1, 0).Value = TextBox1.Text
TextBox1.Text=""
End Sub


The thing is that on my monitor 28 rows of excel are shown at a time and so when i make the 29th entry from above controls though the entry sits perfectly on its place but i have manually to scroll down to see it. What i want to know is it possible to get this task automagically... i mean when i make the 29th entry the excel must show it by automatically scrolling down so as i can see the most recent entries without manually scrolling[/b]
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Bartek

Board Regular
Joined
Jul 29, 2006
Messages
54
Hi,

The thing is that on my monitor 28 rows of excel are shown at a time and so when i make the 29th entry from above controls though the entry sits perfectly on its place but i have manually to scroll down to see it. What i want to know is it possible to get this task automagically... i mean when i make the 29th entry the excel must show it by automatically scrolling down so as i can see the most recent entries without manually scrolling[/b]

Generally, if you want Excel to automatically scroll down, use ActiveWindow.SmallScroll. For example:

Code:
ActiveWindow.SmallScroll Down:=1

scrolls the Window down by a single row. ActiveWindow.LargeScroll will scroll down by pages. Additionally, if you want a specific number of rows to be displayed, select the desired number of rows, like "a1:a30" for 30 rows and use:

Code:
ActiveWindow.Zoom=True
 

irresistible007

Board Regular
Joined
Nov 24, 2005
Messages
173
Many thanks Dude .. the 1st option made my brain clicks towards another tricky adjustment in your code.. i am posting here for the benefit of all

Private Sub CommandButton1_Click()
Dim rng As Range

Set rng = ActiveWorkbook.Sheets("Sheet1").Range("G65536").End(xlUp)

rng.Offset(1, 0).Value = TextBox1.Text
TextBox1.Text = ""
If Range("G28").Value <> "" Then
ActiveWindow.SmallScroll Down:=1
End If
End Sub


The little If command gave me exactly what i was searching for... thanks dude... it was impossible without you cuz i didn't knew that command which you have kindly quoted...
 

Watch MrExcel Video

Forum statistics

Threads
1,113,848
Messages
5,544,640
Members
410,627
Latest member
georgealice
Top