how to stop the cursor scrolling

Billm

Board Regular
Joined
Mar 19, 2002
Messages
88
I have a an excel spreadsheet which only has cols A - E that I want visible to the user. Cols G - L are used for off-screen calculations and reporting etc. So I have reduced the window size of the spreadsheet to only show cols A - E and applied Workbook and sheet protection with a password with all options ticked. This locks the window size ok and stops the user from viewing formulas etc.

However .. if they scroll right with the cursor .. the spreadsheet moves to the right to show my reporting colums ( which I need to leave un-hidden cos I use them as a print Preview )

How do I make the cursor movement restrict to columns A - E only ?

Thanks
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Dave Hawley

Rest in Peace
Joined
Feb 28, 2002
Messages
1,582
Hi Bill


Right click on the sheet name tab, select "View Code" an place in this

Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:E100"
'Set back to normal
'Me.ScrollArea = ""
End Sub


You could also just hide all the columns Except A:E
 

Billm

Board Regular
Joined
Mar 19, 2002
Messages
88
Dave thanks .. I just tried that .. but it still allows me to scroll to col F onwards ?

I saved/exited .. is there something else I need to do ?

Thanks for your help
Bill
 

Dave Hawley

Rest in Peace
Joined
Feb 28, 2002
Messages
1,582
Hi Bill

All you need to do is select any other sheet and then select the original sheet and the code will take effect.
 

Billm

Board Regular
Joined
Mar 19, 2002
Messages
88

ADVERTISEMENT

great Dave .. that works .. cheers

just one more question if I can .. how do I make Excel do this little "toggle" between the 2 sheets each time it opens to activate this ?

I notice that I have to do it manually and if a users doesnt do it then the "scroll lock" doesnt kick in.

cheers Bill
 

Dave Hawley

Rest in Peace
Joined
Feb 28, 2002
Messages
1,582
Hi Bill


While in Excel, Right click on the Excel symbol, top left next to "File" and select "View Code" then paste this code in.

Private Sub Workbook_Open()
Sheets("TheSheet").Activate
End Sub

Where "TheSheet" is the name of the sheet housing your code.
 

Billm

Board Regular
Joined
Mar 19, 2002
Messages
88
Dave .. excellent .. works a dream .. thanks very much for your help .. appreciate it.

Bill
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
to add:

Nerer to question my old pal Dave Hawley, some options i feel.

In VBE can set SCROLL area locking cells, this will work fine as you req, saves VBA

Alos set you works "reporting" data in a diff sheet and hide, also delete the non reqd cells by row and col this will TOTALLY remove them if say F:IV deleted and 100:65536 deleted ...

this will effect the scroll areas.

Just some options NON VBA. I will add that code Dave has provide is perfection ! (cheers Dave)

PS Dave if you see this i need you 6 mate!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,413
Messages
5,528,632
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top