Ifcontains "yes" make A1, B1 , C1 record read only

alpha2nl · #1 10-27-2016, 01:23 PM

can you help for the next? "If cell contains string restrict record" to read only.

Thanx

If B1 contains "yes" make A1, B1 , C1 record read only

macropod · #2 11-02-2016, 07:13 PM

Is this to be run automatically, so that the cell gets protected/unprotected automatically (when changes occur or, for example when the workbook is opened/saved/closed), or as a macro someone has to run?

alpha2nl · #3 11-04-2016, 04:20 AM

When changes occur,automatically protected . Thx

macropod · #4 11-04-2016, 05:35 AM

What happens if someone subsequently changes B1 to something other than 'yes'? Should the other cells remain protected; or should they be unprotected?

alpha2nl · #5 11-08-2016, 03:26 AM

The other cells should be unprotected. Only by "yes" protected.

macropod · #6 11-08-2016, 04:42 AM

You could add the following code to the relevant worksheet's code module:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const StrPwd As String = ""
With ActiveSheet
  If Intersect(Target, .Range("A1:C1")) Is Nothing Then Exit Sub
  If .ProtectContents = True Then Exit Sub
  If UCase(.Range("B1").Value) = "YES" Then
    .Cells.Locked = False
    .Range("A1:C1").Locked = True
    .Protect Password:=StrPwd, Contents:=True
  End If
End With
End Sub

As coded, no password is used. To use a password, simply type it between the "" characters in 'Const StrPwd As String = ""'.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Message "Unable to read file" when clicking on the Excel icon	roundman	Excel	3	12-03-2014 04:28 PM
Is it possible to make Word link to the last record in Excel	stephen_pen	Word	0	09-22-2011 11:00 PM
Mailing: how to make the "page number" in Word is the same as "row number" in excel w	Jamal NUMAN	Word	1	09-03-2011 11:37 AM
Monitoring "Record Narration"?	knewman	PowerPoint	1	04-23-2011 04:59 PM