I don't what to call this...

[clumein2]

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

 

[DominicB]

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 :

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

HTH

DominicB

 

[Sir Phoenix]

Assuming that you're using the first 18 columns and 42 rows... try this:

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]

Try -

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]

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]

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 :

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]

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]

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 --

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 you are only setting the ScrollArea for 1 worksheet.

 

[clumein2]

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.