Locking a range

  • Thread starter Thread starter Bhuktar S
  • Start date Start date
B

Bhuktar S

I am using Excel2000.
I need a code to lock certain range in the sheet but all other cell
should be available for editing, formatting, copy etc. (as norma
unprotected sheet). How to do it?
I tried by protection method but such thing could not be done.
I cannot change the version since Office 2000 is still our company'
standard & cannot be changed.
Shall appreciate for the solution.
Thanks
 
Assume target range to lock is A1:D5

Steps taken in Excel with macro recorder turned on:

Selected entire sheet (Press Ctrl+A)
Clicked Format > Cells > Protection tab > Uncheck "Locked" > OK
(All cells are by default "Locked", so this will unlock the entire sheet)

Selected A1:D5 (the target range)
Clicked Format > Cells > Protection tab > Check "Locked" > OK
(Now we can choose which cells/range to "lock")

Apply sheet protection:
Clicked Tools > Protection > Protect Sheet > OK
(No password)

Turn recorder off

The result is the sub Macro2 below ..:

Sub Macro2()
' Locks only range A1:D5
' Rest of sheet unlocked
Cells.Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("A1:D5").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("A1").Select
End Sub
 
Max, I had tried this but all other cells also cannot be formatted, say
I cannot make other cell font Bold.
Are you using Excel 2000
 
Bhuktar,

I'm using xl97.

But sorry, you're right.

Protecting only a range on a sheet
does disable a lot of normal functions
for the rest of the sheet.

Perhaps others would offer their insights ..
 
xl2002 was the first version that offered this level of protection.

Maybe you could provide a macro that the user could run that unprotected the
sheet, did the formatting, and then reprotected the sheet?
 
Umm.... Dave, that does not sound me a good solution.
May be I can make these locked cell informations/data stored
displayed through control tools such as labels, text box etc. so tha
users cannot change them.
Thanks for your info/help
 
Hi!

You said you needed to protect a range of cells.
Are they configured in such a way that you can keep people fro
accessing them by using the ScrollArea property of the worksheet? (i
turn it round and only give access to part of the sheet)

Al
 
But I can still get to that cell by typing the address into the Namebox (to the
left of the address bar).

But maybe (if macros and events are enabled), the OP could just not let the user
select a range.

I'd give the range of cells a nice name ("DONOTTOUCH")

To the OP: Name that range, then rightclick on the worksheet tab and select
view code. Paste this in:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Me.Range("donottouch")) Is Nothing Then Exit Sub

'give them somewhere else to go
Application.EnableEvents = False
Me.Range("a1").Select
'or
Me.Cells(Target.Row, "A").Select
Application.EnableEvents = True

End Sub
 
Back
Top