Locking cells

[hviking]

I've got a spreadsheet where I have changed the EnableSelection to 1 - xlUnlockedCells so the users can't go to cells I have locked. When I save the sheet and re-open it, the EnableSelection is back to 0 -xlNoRestrictions

How do I keep the users from being able to go to a locked cell. When I protect they can't modify, but they can still go to it.

I am making any sence?

Thanks;

edit- It's Office 2000 Small Business edition

[ This Message was edited by: hviking on 2002-04-03 12:43 ]

[Mark W.]

Have you tried using the Tools | Protection... menu command?

[hviking]

Yes. That keeps them from typing in the selected cell, but I want them to not be able to even go to that cell.

In Office XP this is very simple, just Protect and save, but I can't figure it out in Office 2000

[Ivan F Moala]

Unfortunatly this sheet function does not
hold it's value when closed. You must set
it back upon openning via VBA code.

eg

Private Sub Workbook_Open()
Sheet1.EnableSelection = xlUnlockedCells
End Sub

[Tom Urtis]

Here's an alternative.

Right-click on your sheet tab, left click on View Code, and paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A1:D10")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Range("A1:D10").Select
ActiveCell.Select
Application.EnableEvents = True
End If
End Sub

Please note its features and limitations:

Cannot select outside of range.
Allows you to copy a range from within the acceptable range to outside the range, if the active cell of the copy range is within the acceptable range.
Allows you to drag from within the range to outside of the range.
Allows for spell check change, and for formatting change only after spell check change.

If you want your acceptable range to always be highlighted (selected), delete the line
ActiveCell.Select

Any help?

Tom Urtis


_________________
Tom Urtis

[ This Message was edited by: Tom Urtis on 2002-04-04 00:04 ]

[Ivan F Moala]

On 2002-04-03 22:53, Tom Urtis wrote:
Here's an alternative.

Right-click on your sheet tab, left click on View Code, and paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A1:D10")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Range("A1:D10").Select
ActiveCell.Select
Application.EnableEvents = True
End If
End Sub

Please note its features and limitations:

Cannot select outside of range.
Allows you to copy a range from within the acceptable range to outside the range, if the active cell of the copy range is within the acceptable range.
Allows you to drag from within the range to outside of the range.
Allows for spell check change, and for formatting change only after spell check change.

If you want your acceptable range to always be highlighted (selected), delete the line
ActiveCell.Select

Any help?

Tom Urtis


_________________
Tom Urtis

[ This Message was edited by: Tom Urtis on 2002-04-04 00:04 ]

Welcome back Tom
Where have you been ??

Long time no post.

[Tom Urtis]

Hi Ivan, thanks, I wish I could spend more time on this board!! Lately my business has really picked up and I've had several Excel and property management contracts keeping me occupied.

Still working on my web site...too bad there isn't VBA code to create >24 hours per day.