Lock cells

[Legacy 287389]

Hi good people!,

I have posted a similar thread but this one incorporates a change and I believe this approach will be easier. Please please help, I have honestly searched the forums up to the point of loosing my mind...

Cells A1 and A2. If the contents of cell A1 changes (by means of formula) to "na()", A2 must lock, otherwise A2 must un-lock. The range affected is A1:G20. In this range when all or any Odd number rows change values to "na()", the cell just below in the even numbered row, must lock.

The sheet is protected. So, the sheet should un-protect and then again protect, But without the need for a password prompt. Only in the event of someone trying to manually un-protect the sheet, should a password be prompted. Will anyone please assist me?...I will be eternally grateful...

 

[igold]

Hi juriemagic,

Could you post your code so that we can have a better idea of what needs work.

igold

 

[Legacy 287389]

Hi igold,
I have scraped together some coding and it seems to be working. This is just a small part of the code, it repeats for other cells another 30 times, so I'm sitting with a long code, which doesn't look to nice. I do however have a major drawback with this, which is that I cannot have the sheet protected with a password, because the user will select room numbers to view the maintenance standard of the room, and will from time to time edit status's of certain room items, and you can imagine having to enter a password every single time you select a different room number!...SO, the ONLY real reason to have certain cells lock, is to show the user that a certain cell should not be edited. Off course the user will know to respect this feature as it will ruin a whole lot of things for them should they decide to override this. I was really just hoping to find a code which would prevent the user to select the cell at all, whether or not the sheet was protected. I would honestly appreciate any assistance with this...Thank you..

If Not Range("D3") Is Nothing Then
Sheets("Update Room").Unprotect
 If Range("AH1") = 3 Then
 Range("I8").Locked = True

 Else
 Range("I8").Locked = False
 End If
  If Range("AH2") = 3 Then
 Range("I10").Locked = True

 Else
 Range("I10").Locked = False
 End If
If Range("AH3") = 3 Then
 Range("I12").Locked = True

 Else
 Range("I12").Locked = False
 End If
  If Range("AH4") = 3 Then
 Range("K8").Locked = True

 Else
 Range("K8").Locked = False
 End If
If Range("AH5") = 3 Then
 Range("K10").Locked = True

 Else
 Range("K10").Locked = False
 End If

 

[igold]

Have you thought about using data validation instead. There are a lot of possibilities or strategies you could employ...

 

[Legacy 287389]

I have spent considerable time reading on data validation, but as far as I can see one cannot use "IF" with data validation. You see, cells are locked and un-locked depending on cell values, unless I missed something somewhere. Is there not a possibility maybe to have a small code that will inhibit selection of cells that have the lock status?

 

[igold]

Hi,

I have been playing around with this... What method are you using to trigger the code?

igold

 

[Legacy 287389]

Hi igold,

I have in the meantime stumbled upon some other coding and ideas, and I seem to be sorted for now. Thank you however for being there, your willingness to help is greatly appreciated!..have a blessed day...

 

[igold]

That's great. I am glad you were able to get to where you need to be. If your current code stumbles, just post back in this thread and we can pick up where we left off.

Regards,

igold

 

[Legacy 287389]

Thanx..will keep in mind...