Protecting Specific Cells In Excel
 

Hi!

I am trying to protect specific cells in Excel, and would like to get
a few lines of VBA code for the same. I have tried changing settings
(Protection) for the worksheet and then using the Range().Protected =
True/False combinations, but have not been able to make it to work.
I'd appreciate if someone could write the process in a step-by-step
manner.

Thanks!

Protecting Specific Cells In Excel
 

Good afternoon Excel slave

The menu option Tools Protection Protect Sheet is what you need an
this will protect all locked cells, and all cells are set to locked b
default in a new workbook. So to leave them untouched by protectio
you need to unlock them. Select all the cells to be unprotected, ctr
+ 1, Protection tab and uncheck the locked box. Now protect th
sheet.

In terms of VBA:

Selection.Locked = False

will clear the clear / set the locked status.

ActiveSheet.Protect

will set the protection (Contents, DrawingObjects, Passwords etc ar
all optional arguments).

HTH

Dominic

--
dominic
-----------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...fo&userid=1893
View this thread: http://www.excelforum.com/showthread.php?threadid=39535

Protecting Specific Cells In Excel
 

You need Locked

Try this with the password ron for the activesheet

Sub test()
ActiveSheet.Unprotect "ron"
Cells.Locked = False
Range("A1:A10,D12,G1:G20").Locked = True
ActiveSheet.Protect "ron"
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"excel_slave - ExcelForums.com" wrote in message
...
Hi!

I am trying to protect specific cells in Excel, and would like to get
a few lines of VBA code for the same. I have tried changing settings
(Protection) for the worksheet and then using the Range().Protected =
True/False combinations, but have not been able to make it to work.
I'd appreciate if someone could write the process in a step-by-step
manner.

Thanks!

Protecting Specific Cells In Excel
 

Here's some code with a lot of commenting. Read through it and I think
you'll get the gist of what you need.

'/=========================================/
Public Sub Protect_Unprotect()
'Starting with the assumption that the worksheet is
' protected, you'll need to get information on HOW it
' is protected so declare variables for these 3 pieces
' of information
'The contents, Drawing Objects and/or Scenarios can all
' be protected OR NOT in the worksheet
Dim blnProtectContents As Boolean
Dim blnProtectDrawingObjects As Boolean
Dim blnProtectScenarios As Boolean

'if there is a password, you'll need to get that
' so declare a variable for the password
Dim strPassword As String

'set default for whether worksheet is protected or not
blnProtectContents = False
blnProtectDrawingObjects = False
blnProtectScenarios = False

'check if worksheet unprotected
' if it's protected, get various information
On Error Resume Next
If Application.ActiveSheet.ProtectContents = True Then
blnProtectContents = True
If Application.ActiveSheet.ProtectDrawingObjects = True Then
blnProtectDrawingObjects = True
End If
If Application.ActiveSheet.ProtectScenarios = True Then
blnProtectScenarios = True
End If
'try unprotecting the worksheet without a password
ActiveSheet.Unprotect
'check if worksheet is still protected
If Application.ActiveSheet.ProtectContents = True Then
'still protected so try password
strPassword = InputBox("Enter Password: " & vbCr & vbCr & _
"If there is no password, press ENTER." & vbCr & vbCr & _
"ONLY enter Password if source of this macro is TRUSTED!!!", _
"Password to Unprotect Worksheet...", "")
ActiveSheet.Unprotect Password:=strPassword
'check if worksheet is still protected
'if password didn't work - still not unprotected so stop process
If Application.ActiveSheet.ProtectContents = True Then
Exit Sub
End If
End If
End If
On Error GoTo 0

'now that worksheet is unprotected, set the protection
' property of the cells that you selected
Selection.Locked = True
' or unprotect the cells that you selected
Selection.Locked = False

'comment out one of the two "Selection.Locked = " lines above
' depending on what you want to accomplish

'set worksheet back to original protected/unprotected state
On Error Resume Next
ActiveSheet.Protect Password:=strPassword, _
DrawingObjects:=blnProtectDrawingObjects, _
Contents:=blnProtectContents, Scenarios:=blnProtectScenarios

End Sub
'/=========================================/


HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"excel_slave - ExcelForums.com" wrote:

Hi!

I am trying to protect specific cells in Excel, and would like to get
a few lines of VBA code for the same. I have tried changing settings
(Protection) for the worksheet and then using the Range().Protected =
True/False combinations, but have not been able to make it to work.
I'd appreciate if someone could write the process in a step-by-step
manner.

Thanks!