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]
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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

Forum statistics

Threads
1,141,011
Messages
5,703,724
Members
421,311
Latest member
tanujath

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
Top