Conditional Locking Of Cells- More Than One Sub?
I am trying to create a form that has multiple drop downs, which are dependent on answers from other drop-downs. I'm trying to make it super user friendly and have the cells lock, so that the user can tab through only the items that they need.
I have it almost down, but I'm not sure how to do it for multiple cells. The closest I come is one big long if,else statement which runs through the whole thing on every cell change.
I have an example of the spreadsheet attached.
If F1 says "Other", I want H1 to be unlocked
If F2 says "Y", I want F3 and F4 to be unlocked
IF F4 says "Other", I want H4 to be unlocked
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Conditional Locking Of Cells ..
I have a condition whereby if cell C7 has a value entered, then cells D7 & E7 cannot have values entered. Like wise, if cells D7 & E7 have values, them cell C7 cannot. Cell F7 would be the product of the calculations using the data either in only C7 or only in D7 & E7. Please see the sample attached worksheet with my problem elaborated.
View Replies!
View Related
Locking One Cell If Another Had A Value In It (conditional Locking)
I recently posted a thread on locking one cell if another had a value in it, and vice versa for the other cell. I got this great response (thanks se1429!) and it works GREAT, but I failed to mention that the worksheet is password protected. It asks for a password when I enter a value in one of the cells. I just need help adjusting this code so I can put my password in the code and allow the worksheet to unlock and lock at will by using this password.
View Replies!
View Related
Locking Conditional Formatting In 2000: Copy/paste Text From Other Cells Or Columns Even Other Workbooks
I have a column "g" with this conditional formatting:- =A2<>A3 Format Bottom Border. However I will pass this workbook onto someone else who will fill in the text in column "g". They will use copy/paste text from other cells or columns even other workbooks that will not have the conditional formatting. I have used Cells > Projection > Locked unchecked then used Tools > Protection > Protect Sheet and checked all. There does not seem to be a way to unlock the cell but protect Conditional formatting. Each time I copy and paste from other non formatted cells it wipes out my formatting.
View Replies!
View Related
Conditional Locking Of Button
I have a sheet that people fill in using a combination of validation lists and manual imputs, the requestor then clicks a button which automatically emails the order form to their line manager for authorisation. they add their initials and click another button to email it to accounts. All very nice, but people are lazy and we are starting to get incomplete forms through.
View Replies!
View Related
Conditional Cell Locking Based On Font Color
Is it possible to write VBA code that will prevent a user from changing a cell's contents, depending on the cell's font color? More specifically, I have a column of text in range B15:B64. Some of the cells will have a black font, others will be blue. Is it possible to lock the black font cells only, leaving the blue cells unlocked for users to change?
View Replies!
View Related
Locking Different Cell Ranges Then Locking Whole Sheet With Macros
I have been assigned the task of creating our new integrated time and leave sheet in Excel 2007, previously we had 2 sheets, one for times and one for leave. The problem I have is that I need to protect the authorisaton columns therre are 2 one for AM and one for PM, so that only Managers can authorise leave by inputting a password and then initialing the leave. What I then need to do a the end of each month is for the manager to be able to click a Button with a Macro which will then lock the whole worksheet so that employees cannot change their times after the manager has checked the sheet. I can lock the authorisation columns and password protect but I have to enable protection on the sheet, when I do this I cannot then figure out a way of running a macro which will then protect the whole sheet and assign a password to it so it cannot be changed after being checked and signed off. I have tried to create a macro (button) to remove the protection on the 2 columns and then reapply protection to the whole sheet with another button but to no avail..... If anyone has any suggestions I would be eternaly greatful. Passwords on the sheet for the different buttons are abc or cobra. You can download my Spreadsheet here. [url]
View Replies!
View Related
Locking Cells
I did review some examples your members have already provided however I have a slight twist. I have multiple users adding to a spreadsheet from which data is later copied to a master sheet. I need to lock ONLY cells which contain data when user chooses to save spreadsheet or saves speadsheet on exit. If the cells do not contain data, they should remain unlocked.
View Replies!
View Related
Locking Cells By Time
Is it possible to lock out a column of cells between certain times. Lets say I want to lock the J column between the hours of 9 AM and 11 PM. I only want the user to be able to enter data between 11 PM and 9 AM. Is that possible.
View Replies!
View Related
Locking All Cells In A Workbook
I have been having problems protecting my worksheets, using the normal manner, mainly it does not seem to work, So i came up with another idea, but cant seem to work out how to do it. I have a workbook with about 7 worksheets, this needs to be sent via email to our customers, and on two of the worksheets the customer can enter there comments and then email back the workbook, the problem is that i cant protect the data. So i was thinking is it possible to use VBA to program a function i can enter in to the toolbar that the user can lock all the cells in the whole workbox except for two columns when they finish entering in data, maybe with a password for locking it and unlocking for the original user so that they can unlock the cells for editing.
View Replies!
View Related
Locking Cells That Contain Formulas
I currently have my template protected, and I've been getting complaints that they can't modify the width, fonts etc. The only reason I have it protected is because I don't want them to mess with my formulas. How can i protect ONLY the formulas, and have the rest editable.
View Replies!
View Related
Locking Cells Through Code
1. When I put data into one cell I need three other cells to be locked and not allow data to be inserted unless the data from the previous cell is deleted. 2. I have attached the Excel sheet for better understanding.
View Replies!
View Related
Unlocking/Locking Cells With A Button
The process that will be used is that, I would set up the sheet every month and send it to a data entry person. Then they would send it back to me, and i would send it to certain people for approval. Once they respond with approval I then send it back to the data entry person for filing. I'm hoping to create a button that will "flip" a switch to to speak. When I send it to the data entry person the first time, I need certain cells locked and unlocked. When I send it to the people for approval I need the whole thing locked as well as for when I send it back to the data entry person for filing. I know how to do this manually, but my issue is that every time I need to send it back to the data entry person at the beginning of the month I have to go over every data entry cell and set it up so that when I lock the sheet they stay unlocked. So that's where the button would come in...it could even be 2 buttons...Data Entry Locking and then a full Sheet Locking...I would need each password protected too.
View Replies!
View Related
Locking Cells And Protecting Sheet
A1 is number of days available this sheet goes to a routing recepient from me who fills A2 and B2. C2 and D2 are calculated. A2 has starting date B2 has ending date C2 is days between these two dates D2 is number of days left i.e. A1-C2 I want to lock and protect cells A2,B2,C2 and D2 so that these cannot be changed when I route it back to get next set of dates to be filled by the same person.
View Replies!
View Related
Locking Cells Based On Criteria
I have a query in relation to locking cells to disallow editing based on a dynamic range. The VBA code I have thus far is this : Sub PasteForecastWO() rowarr = Array(10, 26, 28, 69, 72, 79, 81, 87, 89, 104, 106, 114, 116, 122) For Each ce In Range("S8:AD8") If ce = "ACTUAL" Then For i = LBound(rowarr) To UBound(rowarr) Step 2 Range(Cells(rowarr(i), ce.Column), Cells(rowarr(i + 1), ce.Column)).Value = Range(Cells(rowarr(i), ce.Column), Cells(rowarr(i + 1), ce.Column)).Value Next i End If Next ce End Sub What I would like to do is lock the range that applies as the variable "ACTUAL" moves or changes along the range S8:AD8. I have attached the relevant sheet so that you may get a clearer picture.
View Replies!
View Related
Locking Cells Upon Entry Of Specific Data
I'm creating a spreadsheet for work, which for security reasons requires certain information to be locked except to administrators. However the cells need to be open for inputing information for everyone until the administrator enters specific data. Completed Date Time W/R No. Acc. No. Priority? Vunerable? Comments Closed Date/Time Yes 14/04/09 11:30:00 1234567890 8890976 Yes Yes N/A 14/04/2009 11:30 What I am looking to happen is once an adminstrator selects 'Yes' from the drop down menu on the Completed column, for that row of data to be locked for editing, unless unlocked later by an adminstrator.
View Replies!
View Related
Locking Cells Depending On What Is Entered In Another Cell
I'd like to lock some cells depending on what is put in a particular cell. As shown by the file attached, if the first column (Amendments) has a "no" in A4, I'd like to lock out cells B4:D4. (The sheet will be protected). If it's a "yes" then the cells are unlocked. I'd like to then apply this to all subsequent rows (i.e. lock B5:D5 if a "no" in A5 etc.)
View Replies!
View Related
Prevent Locking Of Unlocked Cells When Copy/Paste
I have a workbook with a protected sheet, some cells locked others unlocked, which the users continually update. The problem is some of the unlocked cells are becoming locked, preventing the users from carrying out their role. I couldn't figure out how they were doing this until I saw Powered Convoy's thread Prevent Unlocked Cells Becoming Locked Via Copy/Paste. This occurs when pasting from another application. My question is as the title, how best to prevent unlocked cells in a protected sheet from being locked by the user? I could try to write VBA to intercept all the different ways of pasting data, 5 that I can think of, but was hoping someone else has come across this flaw and has a better or easier solution.
View Replies!
View Related
Conditional Locking Of Cell Based On Another Cell
This is for Excel 2007. I simply want to lock cell H20 and make it's value equal to zero if anything other than 0 is entered into H19. And vice versa (make H19 equal to 0 and lock it if anything greater than 0 is entered into H20). So only one cell can hold a value not equal to zero at a time, and the one that is zero needs to be locked. Here's what I have so far. It locks H20 based on H19 being > 0, but doesn't do the vice versa part. And it also doesn't make H20 = 0 when it locks it.
View Replies!
View Related
Locking Worksheets ...
is it possible to lock a worksheet. For example, i have a single worksheet in a workbook that i do not want people viewing. Is there any way that a password would have to be used to gain access to it? I have to keep the sheet inside this workbook because information is pulling from other sheets inside the workbook.
View Replies!
View Related
Locking Timestamp
=IF(D2<>"",IF(A2="",NOW(),A2),"") Whenever I type into field D2 it populates current date and time in field A2 and down the A column as I write into current D columns. The next step I would like to do in which he was able to figure out so far is how to lock in the date and time after something has been typed into D2. If I make any changes to the D2 field it updates the date and time, which I dont want to happen.
View Replies!
View Related
Locking Workbook
I have a user that is trying to lock certian cells within a spreadsheet but to allow certian users still to be able to edit these cells. When this users is trying to do this and locks the cells it is locking for all users and not applying the "allowed" list of users to make changes.
View Replies!
View Related
Code Locking Up
I'm trying to run the below code, but every time I do, it locks up excel. The code is running on 900 rows of data within the "loop to delete rows" section and in the main for loop there are 49 values to evaluate for the string strSA. how I can improve this code? When I step through it with F5 it works fine.
View Replies!
View Related
Locking Column A Only
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B:B")) Is Nothing Then Application.EnableEvents = False Cells(Target.Row, 1) = Now End If Application.EnableEvents = True End Sub everytime i lock the column a by using the protect sheet, the vba does not work or debugs. is there a way for me to lock the cell in column a when data is inputted or changed in column b? i am using date and time for column a
View Replies!
View Related
Locking Numbers
my spreatsheet calculations work, but the overall thing is too large to print; i am running many numbers and formulas. I made them all finally refer to a small 'summary' box which i created...but when i try to transfer the box to a new worksheet so that i can print it and only it; it automatically has no references for the formulas on the new (blank) sheet, and comes up all errors. to the point, how do i lock the way a box appears (when it contains formulas) so that it will remain sensible after it is transferred elsewhere?
View Replies!
View Related
Locking Cell If Value
I am trying to unlock a cell if another cell is a certain Value and if it is not that Value to lock again i have got as far as unlocking it but i can not get it to lock if the value is faulse. This is what i have so far!! Private Sub Worksheet_Change1() If ("K4") = "Daily" Then ActiveSheet.Unprotect ("Password") Range("F6").Locked = False ActiveSheet.Protect ("Password") End If If ("K4") = "Monthly" Then ActiveSheet.Unprotect ("Password") Selection.Locked = True ActiveSheet.Protect ("Password") End If End Sub
View Replies!
View Related
Locking Date Column
I have set a set of cells to calculate dates from other cells and locked it so that people can not edit the date. The date will be automatically populated. However when i lock the cell, a debug error comes up says runtime 1004. I reckoned that when the column or cell is locked from editing, it will also unable to populate or run calculations. am i right?
View Replies!
View Related
Locking Autoshape Position
Can I lock an autoshapes position relative to the screen size? i.e. I have a disclaimer that appears at the bottom of my screen in the middle, however when the workbook is sent to another user and their monitor has different aspect ratio's the autoshape is positioned a couple of inches from the bottom of the screen. I have looked at locking to cell references but this has the same effect.
View Replies!
View Related
Locking Rows Together When Sorting
I need to lock consecutive rows together before sorting. e.g. A1 contains a name and A2 is blank: B1 contains data related to name in A1 - so does B2, and so on. Therefore need to lock rows 1 and 2 together, then 3 and 4 together and so on, but sorting on the data in the first cell of the group e.g. A1
View Replies!
View Related
Locking The Top Rows
top three or four rows so that scroilling down the screen leaves the title rows where they are . I know that this is possible and have done it several times in the past, age however, dulls the memory and I have just forgot the procedure,
View Replies!
View Related
Conditional Unlocking Of Cells
I have had a look around and found some answers to this question but not quite as complex and I don't know enough to adapt them correctly. Basically AX35:AX239 contain a formula which returns "TRUE" or "FALSE" dependant on certain values in the row, what I need is for the corresponding H, I & J cells to be unlocked on each row if the outcome is "TRUE" and no action if "FALSE".
View Replies!
View Related
Conditional Minimum Value Of Various Cells
I have two columns: column C has job functions, say engineer, cook, driver, etc; column D has salaries. I want to analyse the salaries: what's the min/max,median, mode, average values for each of the job functions. (e.g., if data in column C = "driver", then corresponding salary to be included in the data to be analysed.)
View Replies!
View Related
Conditional Formatting Of Cells
I want to add some colour conditional formatting to the cells in column C, dependent on their variance to the figure in column B. I've got the basic gist of applying the rule to the one single cell, but I'm not sure how to apply it to all the cells in the column. I have tried "dragging" it down, but it then just gives me the variance to the original row.
View Replies!
View Related
Remove A Hyperlink Without The Cell Locking
I want to remove hyperlinks from a spreadsheet, but I want the cells to STAY unlock and I want the borders to not go away as well. When you have a hyperlink and you delete the hyperlink it automatically deletes the border and sets the cell to locked - even if the sheets is password protected. I want that if a sheet is opened all the links are just text! (I know the ' trick, that doesn't help) I was doing this in VB, put the "manual" method does the same thing.
View Replies!
View Related
Protecting Or Locking Cell In Certain Column
In Excel 2007 I protect cells in certain cloumns in a large amount worksheets. By manually protecting or locking them. Once the sheets are protected without a password just blank and only select unlocked cells is ticked for sheet protection. I close the workbook and reopen it. These cells are now unprotected that were protected and theres always two ticks checked off for select locked cells and select unlocked cells.
View Replies!
View Related
Locking Columns On A Questionnaire Spreadsheet
I am trying to develop a questionnaire spreadsheet using a ranking system. I have different questions (based on certain criterias) and four columns for each question: NEVER | SOMETIMES | MOST OF THE TIME | ALWAYS. I want to see if there is a way that I can lock the other three cells when the user has chosen a particular column by typing an X. BY doing so, I want to ensure that the user will not choose two options (or columns) instead of only one, because if he/she does it will mess the other calculations that are not shown within the table. Only the input and output are shown.
View Replies!
View Related
|
TRACKER
