Software

Prevent Excel users from scrolling outside data area

In Excel, you can create a macro to prevent users from moving outside the used range on a worksheet. Here's how.

Do you find that users frequently scroll outside the data range and onto blank areas of their worksheet? You can create a macro that will automatically prevent them from moving outside the used range on a worksheet. Follow these steps:

  1. Activate the worksheet.
  2. Right-click the sheet name tab on which you want to limit scrolling and select View Code.
  3. Enter the following code:


  4. Private Sub Worksheet_Activate()
            Me.ScrollArea = Range(Me.UsedRange, Me.UsedRange(2, 2)).Address
         End Sub

  5. Press [Alt][Q] and save the workbook.

Each time you activate the worksheet, this macro will run automatically to prevent scrolling outside the used range.

When you need to enter data outside the used range, you will need to reset the scroll area. You can create a shortcut key that activates a macro to do just that. Follow these steps:

  1. Press [Alt] [F11]
  2. Select Insert | Module and enter the following code at the prompt:


  3. Sub ResetScrollArea()
               ActiveSheet.ScrollArea =''
           End Sub

  4. Press [ALT][Q]
  5. Press [ALT][F8] and Select ResetScrollArea.
  6. Click Options and assign a shortcut key.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

0 comments