Setting Scroll Area in Excel Worksheet

[Pramila]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

Hi

Please let me know how to restrict the scroll area in an Excel worksheet so that the user cannot scroll beyond the set area. I tried setting it using the ScrollArea property in VBA. But the settings are lost upon closing the workbook.

Thanks

Pramila

 

[mrglsmrc1]

using vba to control spreadsheet behavior on a mac is probably not a good idea because although VBA will work on a powerPC based Mac running office 2004 as you have, VBA is no longer supported on the Mac by microsoft on intel based Macs.
VBA will be supported in a future version of office, we are told.
So even if your script works on your machine, the xls file will behave differently on other machines so it cannot parametize the client's experience using the document.

 

[CyberTaz]

It isn't clear whether you *want* a VBA solution or are simply trying to
resort to one. If you need something in VBA I can't help, but it isn't
necessary for what you briefly describe wanting to do.

One option is to simply Hide the columns & rows beyond the used area of the
sheet. Impose Protection if you need to. Users won' even see the hidden
cells, let alone be able to scroll to them.

If that isn't a satisfactory solution you'll need to provide a much more
detailed description of what you want to accomplish.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[CyberTaz]

Just for clarification...

Intel v. PPC processors has nothing to do with support for VBA

It's Mac Office 2008, itself, which does not support VBA regardless of which
processor is involved, but that version will run on Macs with either
processor with either Tiger or Leopard. Also, VBA has no issue with Intel
based Macs if using Office 2004 or v. X -- both of which run on Intels.

As to the return of VBA in Mac Office -- it has been officially announced by
MS that it will occur with the *next* release which is tentatively scheduled
for sometime in 2010.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Shane Devenshire]

Hi,

I don't have my Mac open, but the code should be

Private Sub Workbook_Open()
Sheets("Sheet1").ScrollArea = "A1:C100"
End Sub

This code is added to the thisWorkbook object in the VBA editior. Activate
the VBA area and under your file name in the top left side double-click the
thisWorkbook item. Then add the code above, modifying the sheet name and
range to suit.

 

[Pramila]

@Shane

Writing a code in VBA Editor would put up the Enable/Disable Macros dialog every time the workbook is opened, which some users would not be familiar with and hence they would normally disable the macros. The hide rows/columns method is simpler, I guess..

Thanks for your help, anyways...