Stop Scrolling

We are trying to stop scrolling in range by using View->Tool Bars-> Control Bar -> Properties. We insert a range to lock the scroll area. This works fine. However, when we save the file - and then reopen the file, the scroll lock is gone. Please advise as to how to save the scroll lock range successfully so that other users will stay in the presribed range only. Thanks.

pike

Thanks, but the macro will not record. I have 13 modules in my workbook - so I am not sure which module should have the code if I finally find a way to record the macro. I have also tried the "Developer" tab followed by "Properties" and then setting the scroll area. This works but the scroll area settings are lost after saving, closing and opening again. I cannot record these steps either. I am simply trying to limit scrolling to a defined range. It seem that there must be an easy way to accomplish this. Please advise. Thanks
,

Thanks for your help.

I opened my workbook and selected "ThisWorkbook" in the Visual Basic Editor list and chose View Code.

I inserted the following: Worksheets(1).ScrollArea = "a1:f10" ..... Result - no luck.
So I tried: Worksheets("Selection").ScrollArea = "a1:f10" .... where "Selection" is the tab name of the sheet - Again no Luck
So I tried: Worksheets("30").ScrollArea = "a1:f10" .... where "30" is the number of the sheet in the VBA Editor List - Still no Luck
I also tried: Sheets(30).ScrollArea = "a1:f10" .... because "sheets" works with other macros to define sheets - but still no joy.

Sorry to be so thick - and persistant. Would appreciate any other suggestions you may have.

Pike

Thanks for all your help. I copied your code directly into VBA code editor section for the Sheet on which I want to limit scrolling - and changed the sheet number in the code as appropriate. I also tried copying your code with the proper sheet number into the code section of ThisWorkbook. Neither worked. I give up.

I am a bit surprised that the Developer -> Properties -> Scroll Area ... approach does not work. It works fine while the workbook is open but the scroll area range is cleared if the workbook is saved and then opened again. This seems to be a very straight forward solution but it cannot be saved. I don't really understand the purpose of allowing for scroll area limits to be set but not saved.

In any case, I am stopped and will resort to hiding columns and rows to keep users from straying too far from my range. Unless you have another idea. I hope macros will work - even when the rows from which the macro looks for data are hidden - have not tested that yet.

Thanks again

Attached is a copy of the file. I have removed about 70% of the sheets to get under the size limit allowed for attachments - the workbook is confidential and still under development.
The tab marked "Selection" is the sheet on which I want to limit scrolling to a range of A1:W72.
I really appreciate any comments or suggestions you may have - on scrolling or any other errors or problems you may see.

Thanks
Victor

Thanks for your response.

I copied the code and entered it exactly as you instructed. I still does not work. I also tried a couple variations such as using the number of the sheet "30" instead of "Selection" but - no luck. I also entered the code in Module 1 as a test but that did nothing.

I assume the code worked for you - with the file I sent. Therefore, I am beginning to wonder if my Excel 2010 is not installed properly - or if there is a toggle switch or setting that is not set properly to allow for event code. I am now chasing down the subject of event code to see if there is something amiss. If the code worked for you in my workbook, perhaps you could send attach the file with code inserted - so that I can compare it to mine. This seems very strange.

In any case, thanks again for your efforts.

Best Regards,
Victor

Pike

Yes, it works.... but it now locks all the pages in the range of a1:a10 as per your code. When I change the code to a1:w27, however, the sheets remain locked in the a1:a10 range. Also, I really don't understand why the code you entered would show up as the same code on every sheet - and limits the range on every sheet. I don't understand why it works in the a1:a10 range now - but the same exact code would not work for me. Of course, it will not help if I cannot alter the code to allow a wider range and limit the effect of the range only to the page call "Selection" .... again, as of now, it is locked in the a1:a10 range. There seems to be a basic issue or problem with how I am saving the file - or the functionality of my version of Excel 2010.

Any thoughts you have will be appreciated.

Have a nice Christmas

Victor

Happy New Year.

This code you suggested works until I save the file and re-open it. Even the file you sent lost its functionality after I saved it and re-opened. Over the holidays, I re-installed Excel 2010 but that did not help. I have been saving the file in xls format since most of my associates are still using the old version of Excel. I also tried saving as xlsx but that did not help. I have moved on - hiding cells rather than restricting scroll area because I am running out of time. However, if you have any other ideas, it would be greatly appreciated. Seems like this should be a simple thing but no luck so far.

Hello..

Please Login or Register  to view this content.

I put the above in the ThisWorkbook module. It's all working as it's supposed to here.
I hopefully have attached the file. Boy they made that attachment thingy complicated
Have Fun