Excel Macro Tip – Restrict The Scroll Space Or Vary Motion In An Excel Worksheet

On this article we will be able to discover how one can restrict the variety motion or the world a person is in a position to scroll round on an Excel worksheet. That is in particular helpful for plenty of causes which we will be able to discover beneath.

Prevents Clicking In Spaces You Do not Need Customers To Click on.

From time to time, you wish to have to restrict the workspace this is visual to customers so they don’t wander into spaces they have got no reason why to peer and click on round in. Those spaces may comprise formulation or some knowledge you do not need the person to peer.

Visually You Can Regulate What The Worksheet Seems to be Like.

If you happen to create an Excel dashboard, the general end result typically seems much better visually if you’ll be able to set the boundaries of the scroll space

Assist Non Reviews Customers Navigate Across the Worksheet More straightforward.

Excel from 2007 onwards has a most selection of 1,048,576 rows and 16,384 columns. This can be a huge space for a person to scroll round and likewise get misplaced in the event that they hit a key in error to take them additional in columns or rows than the

So again to the Excel Macro. We will be able to simply write a small piece of VBA code to pre set the world of the Excel worksheet to lend a hand get to the bottom of all the above problems.

How Does The Macro Paintings?

This Macro makes use of the ScrollArea assets to set the scroll space for a worksheet that you select, and through hanging it within the Workbook_Open match code window permits it to run each and every time the paintings e-book is opened. If we didn’t position the code within the Workbook_Open match code, it will need to be reset each and every time the paintings e-book is opened.

So let’s get down the coding.

FIRST. Open Visible Elementary – through hitting ALT +F11 or Developer Tab – Visible Elementary.

Step 1.Within the Mission window to find the title of the Mission or paintings e-book you wish to have to put the code in.

Step 2. Click on ThisWorkbook

Step 3. Make a choice the Open match within the Tournament drop down checklist and sort or reproduction the code beneath – this case limits the scroll space of worksheet MM17 to B2 to L17.

Step 4. Check Your Macro – The A laugh bit!.

Here’s the code to duplicate and paste if you want to. Simply substitute your sheet title and scroll space as wanted.

Personal Sub Workbook_Open()

Sheets(“MM17”).ScrollArea = “B2:L17”

Finish Sub

Supply through BJ Johnston