unlock, input and re-lock

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

Guest

Hell
I have a problem. I have a protected sheet that i want to insert a number in to a cell, using at the moment

Private Sub Workbook_Open(
Worksheets("Sheet1").Selec
Range("I2").Value = Range("I2") +
End Su

This works, but only if i leave the cell it is writing to unlocked. I need somehow to unlock, input the number, and lock it again once the above has done it's thing
Any Suggestions please??
 
Hi
change your code as follows:

Private Sub Workbook_Open()
Worksheets("sheet1").Unprotect
Worksheets("Sheet1").Select
Range("I2").Value = Range("I2") + 1
Worksheets("Sheet1").Protect Secenarios = TrueUserInterfaceOnly =
True 'you may change this to your needs
End Sub
 
Hi Fran
Thanks for your suggestion, unfortunatly, i got a message saying

Compile error in hidden module: This Workboo

Not sure what i have done wrong!

HELP
 
I think Frank was saying that you may want to record a macro when you set the
protection in this note:
'you may change this to your needs

But this corrects a typo or two:

Option Explicit
Private Sub Workbook_Open()
With Worksheets("sheet1")
.Unprotect Password:="hi"
With .Range("i2")
If IsNumeric(.Value) Then
.Value = .Value + 1
Else
MsgBox "I2 isn't numeric"
End If
End With
.Protect Password:="hi", _
Scenarios:=True, UserInterfaceOnly:=True
'you may change this to your needs
End With
End Sub

You may want different things protected than what Frank chose.
 
Hi
I think Dave already gave you the correct answer. The compile error is
cause by a line break. The following two lines:
Worksheets("Sheet1").Protect Secenarios = TrueUserInterfaceOnly =
True 'you may change this to your needs

are ONE line. Combine them and the compile error is gone :-)
 
You guys are Excel gods. It works a treat now, thanks very much for your help. Will no doubt be back soon!!

Cheer
Andrew
 
Back
Top