I am creating a database for others to enter data. Is it possible to use conditional formatting to "lock" or protect certain cells? For example, if someone types "0" into A1, is it possible to lock cells B1 through E1, effectively requiring them to be skipped?
Not sure if this will become an issue, but all the cells have data validation as well, although blanks can be ignored.
If this is possible, I will be needing pretty detailed instructions (i.e. exact formula to type into cell, or menu option play-by-play... I am semi-new to all of this
Thanks!
Hit ALT F11
Select the sheet that you are working on (Sheet1 for instance).
Paste in the following code.
Hit ALT F11 again to get back to the worksheet.Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Me.Unprotect If Target = 0 Then Range("$B$1:$E$1").Locked = True Else Range("$B$1:$E$1").Locked = False End If End If 'Repeat above segment as necessary for other cells Me.Protect End Sub
Martin
Eighty Twenty Spreadsheet Automation http://homepage.ntlworld.com/martin.rice1/ for all your Excel customisation and consulting needs.
If my solution has saved you time and/or money, please consider donating to Cancer Research UK.
Thanks, mrice. I think this will work, but I'm on my home computer, which is a Mac. Can you use this type of code on Excel for Mac (2008)? ...not sure if this is considered a "macro," but I don't think macros can be used on excel for Mac. I'll try on Monday when I go back to work.
In the mean time, is there any type of shortcut where I could make sure that the code works for every row (I have allowed for up to 100 rows of data, so I don't want to have to repeat the code 100 times so that it works for each record).
Thanks so much for your help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks