I don't what to call this...

clumein2

New Member
Joined
Sep 1, 2006
Messages
32
I have this code on a sheet in my sprdsht. As you can see, when the cursor hits column 18 it jumps back to column 5. Oh, I should tell you that I don't know what the hell I'm looking at. This was given to me by David.

Public sColumn As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
sColumn = ActiveCell.Column
If sColumn = 18 Then
ActiveCell.Offset(1, -13).Range("A1").Select
End If
End Sub

Anyway. My sheet consists of 18 columns by 42 rows. I would like to add something that prevents the user from accessing any other cell on the sheet. ie If they try, or by accident, click in column 19 or row 43 etc, the cursor will jump back somewhere into the sheet cell dimensions.

thanks again & again

Cliff
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good afternoon clumein2

Why not make use of the ScrollArea instruction. This needs running every time you open your spreadsheet so tell David this needs to be placed into the ThisWorkbook module of the file :

Code:
Private Sub Workbook_Open()
Worksheets(1).ScrollArea = "A1:R42"
End Sub

HTH

DominicB
 

Sir Phoenix

Board Regular
Joined
Aug 30, 2006
Messages
146
Assuming that you're using the first 18 columns and 42 rows... try this:
Rich (BB code):
Public sColumn As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
dim offRow as long
dim offCol as long

sColumn = ActiveCell.Column

if (target.row > 42 then)
   offRow = target.row - 42
end if
if (target.col > 18 then)
   offCol = target.column - 18
end if
if (offRow + offCol) > 0 then
   target.offset(-offRow,-offCol).select
elseIf sColumn = 18 Then
ActiveCell.Offset(1, -13).Range("A1").Select
End If
End Sub

This will throw them back, then loop one more time in order to run the original code, which I don't know if it's needed, but I left in just in case.

Hope this helps.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Try -

Code:
Public sColumn As String ' Don't need this unless it goes to other apps
Private Sub Worksheet_SelectionChange(ByVal target As Range)
sColumn = target.Cells(1, 1).Column ' Don't need this unless it goes to other apps
Application.EnableEvents = False
If target.Cells(1, 1).Row < 42 And target.Cells(1, 1).Column > 18 Then
    Application.Goto Cells(target.Cells(1, 1).Row + 1, 1)
ElseIf Intersect(target.Cells(1, 1), [A1:R42]) Is Nothing Then
    Application.Goto [A1]
Else
End If
Application.EnableEvents = True
End Sub
 

clumein2

New Member
Joined
Sep 1, 2006
Messages
32

ADVERTISEMENT

Boy, I read once here that "there's more than one way to skin a cat in Excel'. Guess that's true... ok, off I go giving these things a try.

Thanks to all for you help.

Cliff
 

clumein2

New Member
Joined
Sep 1, 2006
Messages
32
Good afternoon clumein2

Why not make use of the ScrollArea instruction. This needs running every time you open your spreadsheet so tell David this needs to be placed into the ThisWorkbook module of the file :

Code:
Private Sub Workbook_Open()
Worksheets(1).ScrollArea = "A1:R42"
End Sub

HTH

DominicB

Dominic, this seems to be the easiest one but it only works on one of the two sheets I have in the sprdsht. I'm putting it in the 'workbook' object.
Is that correct?

Cliff
 

clumein2

New Member
Joined
Sep 1, 2006
Messages
32

ADVERTISEMENT

Private Sub Workbook_Open()
Worksheets(1).ScrollArea = "A1:R42"
End Sub

Is there an explanation why this only works on the first sheet of a two sheet sprdsht? It's in the 'workbook' module of the file....

Thanks
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Private Sub Workbook_Open()
Worksheets(1).ScrollArea = "A1:R42"
End Sub

Is there an explanation why this only works on the first sheet of a two sheet sprdsht? It's in the 'workbook' module of the file....

Thanks

It's the (1) bit, designates the 1st sheet only.

Add

Worksheets(2).ScrollArea = "A1:R42"

EDIT - or to get all worksheets --

Code:
Private Sub Workbook_Open()
Dim i As Integer
For i = 1 To Worksheets.Count
    Worksheets(i).ScrollArea = "A1:R42"
Next i
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Well you are only setting the ScrollArea for 1 worksheet.:)
 

clumein2

New Member
Joined
Sep 1, 2006
Messages
32
just_jon........ thank for the help and the explanation to this novice.

Norie..... :) If your intention was to help, you might want to give a bit more of an explanation..... It's obvious that I'm a complete novice, so it might have helped if you pointed out where this is in the code..... thanks anyway.
 

Forum statistics

Threads
1,141,731
Messages
5,708,151
Members
421,549
Latest member
Dtcfire

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