Lock Cells Based On Other Cells
Aug 29, 2007
I have created a spreadsheet which I am trying to set up to allow users to select only 1 cell in a range and lock the rest. Each cell D5 to I5 has an assigned number 0 to 5. What I am trying to achieve is when a user clicks on a cell, the value of that cell is displayed and the remaining cells are locked. e.g. if the user selects G6, 3 is displayed and cells D6,E6,F6 and I6 are locked. The code which I am using is giving strange and unpredictable results.
Private Sub Worksheet_Change(ByVal Target As Range)
If [D5] = "0" Then
ActiveSheet.Unprotect ("PASSWORD")
[I5].Locked = True
ActiveSheet.Protect ("PASSWORD")
'Remove locked property if D5's value is anything else or is deleted.
Else
ActiveSheet.Unprotect ("PASSWORD")
[I5].Locked = False
'Optional, reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If............................
View 2 Replies
ADVERTISEMENT
Apr 22, 2009
I'm using the code below to lock certain cells depending on the value of other cells. The code below deals with one line of my spreadsheet only and as the spreadsheet comprises 38 data entry rows I've repeated this code 38 times in the worksheet module with the appropriate changes to row numbers.
It works, but causes much screen flickering and "thinking". I'm new to vba so no doubt I've made this code too extensive or lengthy or whatever (or just plain wrong).
Can anyone assist with suggestions on how to simplify the code and/or help with code to handle all 38 lines without repeating the routine 38 times?
View 7 Replies
View Related
Apr 18, 2008
I am trying to lock the unused cells in 32, 2 column by 7 row named ranges, based on whether or not two cells, above each range are equal or less than each other. In other words while one of the cells is less than or equal to the second cell all cells in the range below should be unlocked, as soon as that condition is no longer true the blank cells need to be locked.
I am trying to use this in the Workbook_Sheetcalculate so that the macro will run automatically.
View 3 Replies
View Related
Oct 31, 2006
I had posted a similar question on 10/27 but the thread has mysteriously diappeared. So, I am reposting my question. What I am trying to do is probably easily done using VBA. See attached workbook example.xls to follow along.
I have a dropdown (YES/NO) selection in Column C. I want to do the following when 'YES' is the selection for any row:
1) do a vlookup into the 'data' tab and return corresponding value in Column D
2) Lock the cell after the value is returned into Column D
If the selection is 'NO' then do nothing., ie., leave the cell blank and unlocked.
View 2 Replies
View Related
Mar 30, 2007
I have a spreadsheet where there are two columns for the user to enter data in, but they can only select one column. I want to lock the second cell if data is entered in the first. I can't find a way from validation to do this. Is there another way via VBA (stupid question, I know... VBA does everything but scratch your butt)
View 4 Replies
View Related
Jun 8, 2007
I need to lock certain cells on a worksheet when a value is selected from a validation list. For example:
user goes to select an option from a validation list, that cell is now locked.
User decides to change the value with a blank option, available on the same list. the cell becomes unlocked ready for new input from the list.
the cell can only allow input when the value in the list matches that of another cell. if another cell already has different data in, it wont allow you to change the value.
View 3 Replies
View Related
Dec 20, 2007
trying to put together a formula, I am trying enable two cells to change between being 'Locked' and 'Unlocked'. I am doing this by using a CheckBox which is referenced to cell [E16] so that when it is checked, 'TRUE' will be displayed and when Unchecked 'FALSE' is displayed. From this I have tried to devise a formula for the cells [c26:I26] and [K26] that when cell [E16] shows 'TRUE' the cells [c26:I26] and [K26] are Unlocked and when it shows 'FALSE' the cells [c26:I26] and [K26] then become locked.
View 3 Replies
View Related
Oct 26, 2013
Trying to incorporate the following in a worksheet:
Lock the cells automatically in the columns that are older than 2 days or more
Lock the cells corresponding to today...
View 2 Replies
View Related
Oct 25, 2013
Trying to incorporate the following in a worksheet: Lock the cells automatically in the columns that are older than 2 days or more Lock the cells corresponding to today and yesterday using a command button after entering X Copy the sheet multiple times with the same features
View 14 Replies
View Related
Feb 25, 2009
I want cell g12 to be locked unless cell h7 is Grass Fire or Timber Fire (H7 is a drop down validated cell)
View 8 Replies
View Related
Apr 24, 2008
I'm looking for a solution to a problem and from what I've read in the forums so far, this is the place to come. I first want to say that I have tried to solve the problem by using guidance from other similar threads and adapt those answers to my needs, but as my understanding of VBA is non-existent this was somewhat difficult. I don't want to post a repeat question and I hate wasting people's time if unnecessary, but I can't do this on my own.
That being said, here is the problem;
(I have attached a simplified copy of the worksheet I am working on for reference)
I would like to lock a particular cell in my spreadsheet based upon the selections made from two drop down validation lists (Cells “C4” & “D4”)
Essentially, if a correct choice is made in both, then the cell “E4” becomes unlocked for editing. If however, the correct selection is made in one list, but not the other, I need the cell “E4” to remain locked and a message to appear to prompt the user to correct their mistake.
Furthermore, both the validation lists have a whole range of selections that are very similar but have subtle differences;
E.g.
"Soft Eye by Talurit"
"Soft Eye by Splice"
Is it possible to write a VBA code that performs a similar function to the =IF(ISNUMBER(SEARCH("**",))) formula, so that if any choice that contains the word "Soft" is selected from either or both lists then the cell “E4” becomes unlocked, but if the selection does not contain that word then the cell remains locked?
If anyone could provide a code to make this happen, I would be very grateful! But please explain how to do this, as the word “Soft” won’t be the only parameter and I may need to alter the code so I need to understand the principle behind it; For example, on the spreadsheet, if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Soft Eye’ is selected then cell E4 is unlocked for editing. If in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Fused and Tapered’ is selected then cell E4 is unlocked for editing, but if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Standard Eye’ is selected then cell E4 remains locked and an error message appears to alert the user that his/her choices are incorrect. If however, "Soft" is not selected in either list then cell E4 remains locked (it has to be selected in at least one of the two lists to allow editing).
This is a work in progress (not all the formulas that will eventually be used have been entered yet, so please excuse the untidiness of it all) and the choices are likely to change based on input from my colleagues, so I need to understand how the VBA works in order to alter it accordingly, but I don’t want to put any more work into this (it’s still in the early stages) if it isn’t possible to achieve these needs.
View 9 Replies
View Related
May 15, 2013
I am looking to write a code to lock/unlock cells based on values selected using a drop down list (see attached Excel) For example if Netherlands is chosen as the Country (Column A), then except for the columns Amsterdam (column B) and Eindhoven (column C), all other columns must be locked. Similarly, if India is chosen as the country from the drop down list, then only the columns New Delhi and Mumbai must be editable for the user, the rest of the columns remain locked.
I tried tweaking some of the lock/unlock codes around, but got lost especially with getting to run the macro - still trying to come to terms with running a "Private Sub Worksheet_SelectionChange" function in the sheet from "Module"!
View 1 Replies
View Related
Mar 29, 2014
am making Excel for private use, i need the following makro to be active, here is the idea because i didnt yet work with makros at all:
If A1 = False
then Range B1:D1 will be locked cells
If A1 = True
then Range B1:D1 will be unlocked cells
Those rules apply to 1 day in the year.
the range A1:D1 will be copied about 400 so the makro should be active to each day separatly.
Check the picture attached for example of one day
View 1 Replies
View Related
Nov 7, 2006
I am aware of setting protection to certain cells by the Excel Options. which is unlocking the by defalut locked cells and then setting the sheet protect in tools->protection. I want this to be done through VBA code.
i have a template which has text in some cells and some cells are blank, i need to lock the cells which has text and keep the blank cells unlocked for user input. As this is a template it will be copied to another sheet, so i need to retain the protection properties in all new sheets (copied from the template). Attaching the template for which the protection needs to be done.As of now the sheet1 has protection property set through the toolbar properties.
View 2 Replies
View Related
Feb 8, 2014
I use the attached workbook to keep tack of feed use in turkeys. when a new flock is added to a farm (Airport View or Home farm) A template is copied and pasted from the "templates" sheet depending on what type of diet they will be on. (Ralco ABF or Ralco Standard) that template refers to the Ration sheet to calculate how much corn is in each diet. After a flock is complete I will never change the numbers again, it is just for record keeping. I want to be able to change the Corn/ton numbers in a ration (on the rations tab) if need be and not affect all the older completed records.
View 1 Replies
View Related
Oct 22, 2008
Is there a way to protect a sheet from a user dragging a cell?
View 6 Replies
View Related
Aug 1, 2014
I have used the following code to lock cells when saving.
I need to exclude Cell B2 when saving
let me know what changes i should make.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
if ActiveSheet.Name = "Summary" Then
On Error Resume Next
'Resume to next line if any error occurs
Dim Cell As Range
With ActiveSheet
'first of all unprotect the entire
'sheet and unlock all cells
.Unprotect Password:="pw"
[code]....
View 2 Replies
View Related
May 1, 2009
W3:W300 is a protected range containing formulas returning numbers from one upwards, with two decimal places.
I need some vba to check this range and, if a number greater than 1 is found, to make the corresponding cells in Columns X and Y blank and lock them.
View 9 Replies
View Related
Jun 20, 2006
I am assigned to a very tedious task which requires me to lock selectively, cells from Jan through to Dec. Instead clicking the cells and selecting the various options from the menu bar, I would like to do it the other way.
If I want to go locking a cell upon selecting it, what are the VB codings?
View 9 Replies
View Related
Mar 12, 2007
I am trying to get user to enter month and goto sheet named the month they enter. I would also like to tag on the end of each one a 1,2,3,4 as I have four sheets four each month. Jan1, Jan2, Jan3, Jan4 etc..
Sub Cell()
Dim InptPass As String
Dim Pass As String
Dim month As String
Pass = "mypassword"
InptPass = InputBox("Please Enter Password")
If InptPass <> Pass Then
MsgBox "Incorrect Password", vbCritical
Exit Sub
Else
month = InputBox("Enter Month to Lock")
month1.Activate
ActiveSheet.Range("A1:BK22").Locked = True
MsgBox "Cells Locked", vbInformation
End If
End Sub
View 4 Replies
View Related
Oct 10, 2012
I've got a spreadsheet set up for a simple barcode scanning system. The user scans the barcode of the product when complete, and it logs it to a sheet along with a date & time timestamp. I'm then planning to use a v-lookup (barcode to product name) & pivot-table to count instances of 'product name' so that I can monitor what is being produced easier, nothing too tricky.
The problem I'm having comes from the products of the same spec having the same barcode, so data validation is hard to apply, as I can't 'not allow dupes' - the concern I have is that if the user unknowingly (or knowingly!) scans the same ticket twice, I think I've got 2 of a product when I've only got one. The idea I had was to lock all cells for a set time period (say 1 minute) after an entry so that it couldn't be accidentally scanned twice.
View 1 Replies
View Related
Mar 27, 2014
I want to copy a formula and want to lock a certain column, just like you use the dollar signs in normal formulas. Because the data in tables can't be locked with dollar signs.
Example:
example 1.xlsx
View 2 Replies
View Related
Sep 8, 2009
Basically I have recently started my own personal music chart and I'm having problems with a Year To Date page.
Basically all I want to know how to do is how to lock some cells together and rank many rows of those cells with 1 column where the total is?
I have attached the spreadsheet as a zip files as I was having problems uploading it as a xls file.
View 13 Replies
View Related
Nov 6, 2009
I would like to lock some collumns and yet change the formating on the unlocked cells mi other columns. Is there another way to protect cells other than locking cells and then protecting the worksheet?
View 5 Replies
View Related
Jul 10, 2013
I have a worksheet i want to disable
(1) save as function (if someone wants to saveas another name then it will ask for password
(2) lock all cells but unable autofilter (in my document if some one type in textbox given above it filter as typing....
My target to make it copyproof sheet and if someone copies my sheet than it is useless without password although user can only search books...
View 1 Replies
View Related
May 17, 2014
I finally have the merged cells resizing the way I need, however it seems to lock the cell after the process not allowing it to be edited again until I unlock the sheet. below is the current code I am using
[Code] .....
View 5 Replies
View Related
Feb 10, 2014
I'm currently using this macro:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng
Dim MyCell
Set Rng = Range("A1:AA7672")
For Each MyCell In Rng
[Code] ....
The problem is that it is not dynamic and it's super slow. The larger my range is the slower it operates and right now it's almost unusable.
The workbook I am using gets touched by 10 different people and I want to prevent tampering. I want for the workbook to be, when protected, have all the blank cells editable and once a value is inserted, it cannot be edited again and not work so unbelievably slow.
View 5 Replies
View Related
Mar 8, 2014
i have a situation where i want cells c13:e13 to be locked if cell g5 is blank. and same cells will be unlocked only if g5 contains text only.
View 4 Replies
View Related
Oct 6, 2008
I have a macro in Module1 of workbook "BAU" called "ExtractToSheets" which exports data depending on the contents of a cell (Thanks royUK et al)
At the moment however, you must run the macro manually to export the data.
Now, i have put some simple validation in column K of worksheet "BAU Data" within the "BAU" workbook. Whenever a cell within a predefined range with column K is changed to "Yes" i want the macro to run AND i want the row the cell is in to be locked so it cannot be edited further by the user. I have had too many users mess up previous sheets i have made to collect my data, and i wish to make this one somewhat more idiot-proof!
Any pearls of wisdom folks?
I would have attached a representation of my sheet, unfortunately because there is now a macro in my spreadsheet, our trigger-happy security software won't let me post it!
View 14 Replies
View Related
Oct 18, 2012
I have a search form that will filter the result based on the requested category. Is it possible to lock the cells once it has been filtered and then remove the lock after they close the workbook?
View 2 Replies
View Related
