Combo box

[aksel børve]

I am trying to make a cell Locked or Unlocked by using the Combo Box value.
But when I click on one of the Combo drop down values, I get the message:
"The cell is protected and can not be wriiten to"
Any Suggestions?
Aksel

Private Sub ComboBox3_Change()
ActiveSheet.Unprotect password:="*******"
Application.ScreenUpdating = False

If ComboBox3.Value = "NONE" Then GoTo Line1 Else GoTo Line2
Line1:
'Protect Cell G18
With ActiveSheet.Range("G18")
..FormulaR1C1 = ""
..Locked = True
..Interior.ColorIndex = 15
End With
GoTo Lastline
Line2:
Unprotect the Cell G18
With ActiveSheet.Range("G18")
..Locked = False
..Interior.ColorIndex = xlNone
End With
Lastline:
ActiveSheet.Range("J24").Select
ActiveSheet.Protect password:="driller", DrawingObjects:=True,
Contents:=True, Scenarios:=True
End Sub

 

[Jim Cone]

Aksel,

You have to unlock the sheet with the same password
that you locked it with. In the following revised code,
I added the unprotect password, simplified
the code a little and eliminated the goto lines...

'-----------------------------------------------------------
Private Sub ComboBox3_Change()
Application.ScreenUpdating = False
Me.Unprotect Password:="driller"

If ComboBox3.Value = "NONE" Then
'Protect Cell G18
With Me.Range("G18")
.FormulaR1C1 = ""
.Locked = True
.Interior.ColorIndex = 15
End With
Else
'Unprotect the Cell G18
With Me.Range("G18")
.Locked = False
.Interior.ColorIndex = xlNone
End With
End If

Lastline:
Me.Range("J24").Select
Me.Protect Password:="driller", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End Sub
'-----------------------------------------------------------------

Regards,
Jim Cone
San Francisco, USA

I am trying to make a cell Locked or Unlocked by using the Combo Box value.
But when I click on one of the Combo drop down values, I get the message:
"The cell is protected and can not be wriiten to"
Any Suggestions?
Aksel

- snip -