Code to stop scroll

[stevesunfold]

hi guys
is there anyway to stop screen scroll?
so that you can only see the visible cells at opening of workbook?
regards

[solnajeff]

HI

The following code in a sheet module will restrict scrolling on that sheet

Please Login or Register  to view this content.

or it could be in the "This Workbook" module and set on opening for all relevant sheets

The property is cleared on saving the workbook and must be reset each time

Regards

Jeff

[stevesunfold]

hi jeff
thanks
but its still scrolling

[solnajeff]

HI

Attached is an example sheet that limits scrolling on sheet 1 to the range A1 to M50.

Regards

Jeff

[stevesunfold]

terrific
work a treat
thanks jeff
how sad are we on xmas day ? lol

[stevesunfold]

hi solna
im trying this in a different workbook and doing as i did before but it lets me scroll to where i like

is this other code affecting it?

Please Login or Register  to view this content.

[VBA Noob]

Try selecting another sheet then go back to the sheet with the code. Code only works when you activate the sheet.

VBA Noob

[stevesunfold]

Ah i see now
hmmmmm is there anyway this could be permanent please?

[shg]

In the ThisWorkbook module:

Please Login or Register  to view this content.

[stevesunfold]

hi shg
nice to see you again
im getting compile error in hidden module: this workbook message
i have changed Sheet1 to working sheet which is what its callled

[stevesunfold]

ignore shg
i thinks its because i had two Private Sub Workbook_Open()
one with nothing in it
its ok now thank you once again

i take it
its possible to add any other sheets i would like to

[shg]

Try it, Steve.

[stevesunfold]

i actually thought that as soon as i posted it shg
trouble is im scared i will undo all the work ive previously done
but im only adding another line

[VBA Noob]

You don't need code to set the scroll area. Press Alt + F11 > Select sheet and under properties add the range to scroll

http://www.excel-vba.com/zz-vba-4-4.gif

VBA Noob

[stevesunfold]

vba
would i use this reference?
a1:s20

its ok noticed
$A$1:$S$20

[shg]

I'd use Noob's approach.

[stevesunfold]

cool thanks guys
you guys are blooming geniuses

[stevesunfold]

perhaps not as perfect as i thought
if i save it then reopen it
it doesnt stop the scroll where as shg's way does

[shg]

Hmm.

I'm surprised that doesn't persist.

[stevesunfold]

yours does to be fair

[stevesunfold]

its weird because when i save it its got the refences there
but when i reopen the workbook the references are empty

[shg]

Mine does because it runs every time the workbook opens.

[stevesunfold]

i will keep to yours now if i know that works
is 4.4mb large for excel file?

[shg]

It's large compared to the size of the files I work with. I have fairly complex workbooks I use for pricing, and they're typically 500K. The most complex workbook I ever built was about 5MB.

The only ones I have that are larger are just gobs of data.

[zobi316]

Thankyou for the attachment of the scrolling limit. But how do I get the scrolling working with other information in it such as charts and tables. I tried the same procedure but just doesn't seem to be working

[shg]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then start your own thread.

Thanks.