Window scroll issue

[irresistible007]

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]

 

[Bartek]

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:

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:

ActiveWindow.Zoom=True

 

[irresistible007]

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...