Limit Scrolling Area

I don’t use VBA coding very much at all, well, hardly ever in fact. However, this evening I came across a couple of features that I thought were useful to know and one of them involves using VBA.

Imagine you want to set up a worksheet with a model or some information or something but it only contains data in, say the range A1:E100 and you don’t want anyone to be able to move their cursor outside that range. Here is how to stop the cursor from moving below row 100 and to the right of column E.

• Open a workbook and right click your mouse on any sheet tab
• Select View Code
• In the coding area copy and paste the following:
• Private Sub Worksheet_Activate()
  Me.ScrollArea = "A1:E100"
  ‘Set back to normal
  ‘Me.ScrollArea = ""
  End Sub
• Save your worksheet

You then need to click on any other sheet tab in the workbook and then come back to the sheet that you have saved the macro in and that macro will now be active.

It doesn’t take much to change the area you want to control: just change A1:E100 to F5:N23 or whatever you like. Try it!

Duncan Williamson

Thanks to the people here for this tip: http://www.mrexcel.com/forum/showthread.php?t=3233