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,526
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,526
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,526
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

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:37 PM.


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