Protecting Cells in Excel

  Desert Andy 14:19 14 Nov 2007
Locked

Hi there

I have set up a worksheet to accept data from a user. I have conditionally formatted 3 cells to appear if the user makes a certain selection further up on the worksheet. Is it possible to unprotect these cells at the same time that they are made visible, if that is the case i will protect the cells normally so that they cannot be selected, and if required they will be unprotected for the user to input data.

Thanks for your help

  VoG II 14:26 14 Nov 2007

I think that you would need code to do this, like:

Sheets("Sheet1").Unprotect password:="xyz"
'Code goes here
Sheets("Sheet1").Protect password:="xyz"

  Desert Andy 15:06 14 Nov 2007

What I am trying to achieve is to set up the page before the user sees it. I have protected the worksheet, so that the user can only enter data into a couple of cells. However, if he enters a certain bit of information into 1 cell, I would like to unhide and unlock 3 more cells to get some data. I don't know how to pre-arm the cells to unlock depending on information in a particular cell, if indeed that is possible.

Does this help any further with a solution?

  VoG II 15:46 14 Nov 2007

You should be able to use an event procedure to do this. To enter the procedure, right click the sheet tab and select View Code. Then

Private Sub Worksheet_Change(ByVal Target as Range)
If Target.Address(False, False) = "A1" Then
'your code to unlock goes here
End If
End Sub

Change A1 to suit.

To get the unlocking code the easiest way would be to record a macro whilst doing it manually then adapt the code produced.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Best of the Grad Shows 2017: University of the West of England (UWE)

Best value Mac: Which is the best £1249 Mac to buy

Les meilleures GoPro 2017