VBA Express : Excel - Prevent Cell Selection / Scrolling

Excel

Prevent Cell Selection / Scrolling

Ease of Use

Easy

Version tested with

2000, 2002

Submitted by:

Justinlabenne

Description:

Prevent cell selection and sheet scrolling

Discussion:

If you do a lot of applications that require a backdrop "Dashboard" sheet, this snippet of code helps to place some more limits on user options. It disables the ability to select cells on the sheet, plus limits the scroll area to just one cell so the user cannot scroll vertically or horizontally. It also protects the sheet setting, but sets the User Interface to True so code can still run on the sheet if needed. Another method to customize the Excel environment and limit some user options.

Code:

instructions for use

Option Explicit Sub StopSheet() ' Kill Cell Selection on Dashboard Sheet With Sheet1 'Code Name ' Disable scrolling by setting the scroll area to one cell .ScrollArea = Range("A1").Address ' Disable cell selection .EnableSelection = xlNoSelection ' Protect the sheet with user-interface set .Protect , , , , True End With End Sub Sub StartSheet() ' Reset the sheet With Sheet1 ' Unprotect the sheet .Unprotect .Range("A1").Select ' Re-open the scroll area .ScrollArea = Empty ' Allow for cell selection .EnableSelection = xlNoRestrictions End With End Sub

How to use:

Open an Excel Workbook
Copy the code
Press Alt + F11 to open the Visual Basic Editor (VBE)
Select INSERT > MODULE from the menubar
Paste code into the right pane
Press Alt+Q to return to Excel
Save workbook before any other changes

Test the code:

Go to TOOLS > MACRO > MACROS
When the dialog appears, select {StopSheet}
Press Run to disable the options
Go to TOOLS > MACRO > MACROS
When the dialog appears, select {StartSheet}
Press Run to re-enable the options

Sample File:

DisableCellSelection.zip 8.92KB

Approved by mdmackillop

This entry has been viewed 209 times.