Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 10-27-2016, 01:23 PM
alpha2nl alpha2nl is offline Windows 8 Office 2013
Novice
 
Join Date: Oct 2016
Posts: 3
alpha2nl is on a distinguished road
Default Ifcontains "yes" make A1, B1 , C1 record read only

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
Reply With Quote
  #2  
Old 11-02-2016, 07:13 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,137
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 11-04-2016, 04:20 AM
alpha2nl alpha2nl is offline Windows 8 Office 2013
Novice
 
Join Date: Oct 2016
Posts: 3
alpha2nl is on a distinguished road
Default

When changes occur,automatically protected . Thx
Reply With Quote
  #4  
Old 11-04-2016, 05:35 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,137
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

What happens if someone subsequently changes B1 to something other than 'yes'? Should the other cells remain protected; or should they be unprotected?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 11-08-2016, 03:26 AM
alpha2nl alpha2nl is offline Windows 8 Office 2013
Novice
 
Join Date: Oct 2016
Posts: 3
alpha2nl is on a distinguished road
Default

The other cells should be unprotected. Only by "yes" protected.
Reply With Quote
  #6  
Old 11-08-2016, 04:42 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,137
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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 = ""'.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


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 read 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


All times are GMT -7. The time now is 07:35 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft