how to stop the cursor scrolling

[Billm]

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

 

[Dave Hawley]

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]

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]

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]

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]

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]

Dave .. excellent .. works a dream .. thanks very much for your help .. appreciate it.

Bill

 

[Jack in the UK]

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!