Locking cells

hviking

Board Regular
Joined
Mar 31, 2002
Messages
61
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
Unfortunatly this sheet function does not
hold it's value when closed. You must set
it back upon openning via VBA code.

eg
</pre>
Private Sub Workbook_Open()
Sheet1.EnableSelection = xlUnlockedCells
End Sub

<pre/>
 
Upvote 0
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
 
Upvote 0
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 :wink:
Where have you been ??

Long time no post.
 
Upvote 0
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. :LOL:
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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