Scroll area lock not working ?

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,035
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

shajueasow

Well-known Member
Joined
Oct 7, 2004
Messages
1,926
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

Well-known Member
Joined
Mar 28, 2004
Messages
2,035
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

Board Regular
Joined
Aug 19, 2006
Messages
176
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

Well-known Member
Joined
Mar 28, 2004
Messages
2,035

ADVERTISEMENT

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

Board Regular
Joined
Aug 19, 2006
Messages
176
Can you be specific about which columns are visible?

Example:
DZ:EN

Dufus
 

Rawlo

New Member
Joined
Nov 16, 2006
Messages
1
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

Well-known Member
Joined
Mar 28, 2004
Messages
2,035
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.]
 

Forum statistics

Threads
1,136,992
Messages
5,679,018
Members
419,799
Latest member
APInfa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top