Making cells readonly

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet that I am working with in VBA. I have tried making the sheet read only after populating it by using the following code
For i = 1 to
sheet1.Columns(i).Locked = Tru
Nex

However, I am still able to edit all cells. Is there some other syntax that I should use? Can't figure out why this won't work

Thanks much
 
Sarah

You have to Protect the sheet before locked cells become functional.

Sub lockdown()
With ActiveSheet
For i = 1 To 3
Sheet1.Columns(i).Locked = True
Next
.Protect DrawingObjects:=True, Contents:=True, _
Scenarios :=True
End With
End Sub

The default for Excel is that all cells are "locked" when protection is on.

You may want to "unlock" the other columns before locking up Columns 1 to 3.

Sub lockdown2()
Cells.Select
Selection.Locked = False
Columns("A:C").Locked = True
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

Gord Dibben Excel MVP
 
you have to protect the sheet as well.

--
Regards,
Tom Ogilvy


Sarah said:
I have a spreadsheet that I am working with in VBA. I have tried making
Click to expand...
the sheet read only after populating it by using the following code:
For i = 1 to 3
sheet1.Columns(i).Locked = True
Next

However, I am still able to edit all cells. Is there some other syntax
Click to expand...
that I should use? Can't figure out why this won't work.
 
-----Original Message-----
I have a spreadsheet that I am working with in VBA. I
Click to expand...
have tried making the sheet read only after populating it
by using the following code:
For i = 1 to 3
sheet1.Columns(i).Locked = True
Next

However, I am still able to edit all cells. Is there
Click to expand...
some other syntax that I should use? Can't figure out why
this won't work.
Thanks much.
Click to expand...

Sarah

Locked cells only have an affect if the sheet is protected.
Add code to protect the sheet or do it manualy.

Non formula cells or cells to be edited will have to have
the locked property removed before protecting the sheet

Regards
Peter
 
When I try the code you had in your post, I get an error: "Expected statement"

Here is the line of code I am using
sheet1.Protect DrawingObjects:=True, Contents:=True, Scenarios:=Tru

Is this not correct
 
Sub lockdown()

For i = 1 To 3
Sheet1.Columns(i).Locked = True
Next
Sheet1.Protect DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True

End Sub


worked fine for me.
 
Sarah

Either of these will work.

Sub lockdown()
With Sheet1
For i = 1 To 3
.Columns(i).Locked = True
Next
.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True
End With
End Sub


Sub lockdown2()
For i = 1 To 3
Sheet1.Columns(i).Locked = True
Next
Sheet1.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True
End Sub

I still think you should go for the other scenario which unlocks all other
cells except columns 1 to 3

Gord
 
Back
Top