Scroll area lock not working ?

[Pauljj]

I have a code which should prevent people scrolling out of a certain area but it doesnt quite work. My code is

Private Sub auto_open()



Worksheets("JAN07").ScrollArea = "$a$1:$GI$499"
Worksheets("FEB07").ScrollArea = "$a$1:$GI$499"
Worksheets("MAR07").ScrollArea = "$a$1:$GI$499"
Worksheets("APR07").ScrollArea = "$a$1:$GI$499"
Worksheets("MAY07").ScrollArea = "$a$1:$GI$499"
Worksheets("JUN07").ScrollArea = "$a$1:$GI$499"
Worksheets("JUL07").ScrollArea = "$a$1:$GI$499"
Worksheets("AUG07").ScrollArea = "$a$1:$GI$499"
Worksheets("SEP06").ScrollArea = "$a$1:$GI$499"
Worksheets("OCT06").ScrollArea = "$a$1:$GI$499"
Worksheets("NOV06").ScrollArea = "$a$1:$GI$499"
Worksheets("DEC06").ScrollArea = "$a$1:$GI$499"
Worksheets("JAN07").ScrollArea = "$a$1:$GI$499"
Worksheets("FEB07").ScrollArea = "$a$1:$GI$499"


End Sub


It locks when I get to column EN not GI, would there be a reason for this ?


Cheers

Paul

 

[shajueasow]

Does this occurs in all sheets or just some of them?
And "JAN07" and "FEB07" are shown twice in the code, which will not happen as there cannot be sheets with the same sheetname in a workbook. Or is it just a repetition?

 

[Pauljj]

This was just a repitition in the cpying over but it does happen to all of the sheets and not just a few of them??/any ideas??

 

[Dufus]

Pauljj,

What method are you using to scroll? For example, do you use the Tab key, the Right Arrow key, or something else?

Also, when you're done scrolling, what columns are visible in your screen?

Finally, is there any other code anywhere in the workbook?

Dufus

 

[Pauljj]

There are various codes both in modules and some in the workbook, noe are in the worksheets.

I use the right arrow key tomove across the sheet, went it reachers around EN columnit stop I can see everything up that column ??

 

[Dufus]

Can you be specific about which columns are visible?

Example:
DZ:EN

Dufus

 

[Rawlo]

Dudes

I wanted a similar no-scrolling property for a sheet, and used the following:

ActiveWindow.ScrollIntoView _
Left:=0, Top:=0, _
Width:=100, Height:=200

Whack this into a macro in the code for the sheet you're interested in, and then tell Excel to fire it up whenever a cell is selected*.

It'll then make the top left of the sheet appear in the top left of the window whenever anything is done - thereby locking you in the top left of the sheet, without causing any selection issues.

It causes a slight flicker when users move the cursor 'off-screen' with the arrow keys, and is probably a fairly inelegant fix, but works fine for me!

Enjoy - hope it's of some use.

[*Could do this with
Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Row = 3 Then
following the Then with the above or the name of the macro containing the above (I had mine doing other stuff too). Obviously this code only works when you change selection from something in Row 3, but you can mess about with that bit of the code.]

 

[Pauljj]

Cheers for that mate

Dudes

I wanted a similar no-scrolling property for a sheet, and used the following:

ActiveWindow.ScrollIntoView _
Left:=0, Top:=0, _
Width:=100, Height:=200

Whack this into a macro in the code for the sheet you're interested in, and then tell Excel to fire it up whenever a cell is selected*.

It'll then make the top left of the sheet appear in the top left of the window whenever anything is done - thereby locking you in the top left of the sheet, without causing any selection issues.

It causes a slight flicker when users move the cursor 'off-screen' with the arrow keys, and is probably a fairly inelegant fix, but works fine for me!

Enjoy - hope it's of some use.

[*Could do this with
Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Row = 3 Then
following the Then with the above or the name of the macro containing the above (I had mine doing other stuff too). Obviously this code only works when you change selection from something in Row 3, but you can mess about with that bit of the code.]