Tuesday, 10 July 2012

How To disable scrolling in an Excel sheet / Make a Static Page in Excel

How To disable scrolling in an Excel sheet / Make a Static Excel Page:

Most of the Excel Geeks might have faced this problem.
How to make an Excel page static ?
How to lock the scrolling or disable the scrolling of the excel sheet along with mouse ?
How to make a static front page for your dashboard or excel report ?
How to prevent the end user from scrolling the sheets?
Even I have faced this issue.
I found a solution after long experiments.

 Click on this link to open the sample file for Reference. 
Click on SKIP ad to Download the file. (Note: The password is blank to unprotect the sheet)

Did you like the way it's built ? It's the billing file I had built for myself to note the time spent on projects and to send to clients for payments. You can see the file has made in a professional way. A Normal user will get wondered thinking what kind of file is this. So many clients had asked me that which software I had used to make this billing file.

It's a simple excel file. You can see the Column headers & Row headers are hidden, Scroll bars are not available, Sheet tabs are hidden. You can click or select only the cells which requires any changes. It's all basic Excel tricks.

But you can see that you can't scroll the mouse. You can't use PageUp Or PageDown. The page is completely fixed only the screen. I will explain how to do this.

Do the following steps:

1. First of all, hide the vertical or horizondal scroll bars or both as per your need.
    File > Excel Options > Advanced > Display options for this workbook >
   Untick the Show horizondal scroll Scroll bar and vertical scroll bar.
   Click OK, it will hide the bars. But it's not the solution. You can see still the scrolling is working.
2. Then Goto VBE ( Press Alt + F11)
3. Open Project Explorer > VBAproject > MS Excel objects > This workbook
4. In VBA code Window
                  From 'General' drop down , select 'Workbook'
                  Then, from the 'Declaration' drop down, select 'Open'
You will get a default code like this.
Private Sub Workbook_Open()

End Sub
5. Enter ThisWorkbook.Sheets("DashBoard").ScrollArea = "A1:E20"  into the above sub.
Final code will be like this :
Private Sub Workbook_Open()
ThisWorkbook.Sheets("DashBoard").ScrollArea = "A1:E20"

End Sub

This code will run when you open the workbook and the page will be static. User will not be able to scroll or select any cell out of the given range above.

Notes :
  • You should change name of the sheet and the range in the above code as per your workbook. 
  • If the current workbook is not macro enabled workbook, you have to save the file as macro enable workbook. 
  • Make sure the macro is enabled on your excel
  • You cannot unfreeze the scrolling once it's run the code while opening the file. If you want to unfreeze the scrolling, first comment the vba code and then save the file. So the code will not run while opening the file. Then Re-Open the file.
How to Comment the VBA code:

Add the comment operator (') before starting of the line as shown below. The Single Quote has placed before ThisWorkbook. You can see the commented line in a different color.

Private Sub Workbook_Open()
'ThisWorkbook.Sheets("DashBoard").ScrollArea = "A1:E20"
End Sub