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.
I have copied from another post (here I believe) and modified for my situtation. I simply want a range of cells K13:U13 to stay locked unless there is an X in H13. This will repeat on down the spreadsheet, ie. K14:U14 will stay locked unless there is a X in H14. Using the script I found it seems to be working unless you go back and change one of the X's to a blank. The range stays unlocked.
Private Sub Worksheet_Change(ByVal Target As Range) Dim strName As String Dim strRange As String strName = Me.Range("H13") If strName = "" Then Me.Cells.Locked = True Me.Range("H13").Locked = False End If strName = Me.Range("H14") If strName = "" Then Me.Cells.Locked = True Me.Range("H14").Locked = False End If..................................
My problem seems quite easy to solve but for some reason I cannot get around the error messages. It seems it will not process the paste values argument:
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".
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.
I'm using three different workbooks for the macro I've designed. The macro works exactly as I planned it would, but I'm getting a curious side-effect of running the macro. I have over 5,000 workbooks that I need to open, unprotect, change (mostly by copy/paste), and reprotect. It's a fairly straight forward macro, but it is my first, and I must be overlooking something. When the macro is complete, cells that were not modified via the macro in any planned/anticipated way are being unlocked. This has left me puzzled and frustrated. The files are opened, unprotected, the cells are copied over as planned, reprotected, and closed/saved. Everything works perfectly, but the cells in parts of the workbook (Filename) (which were locked before the macro ran) have been unlocked and are no longer protected when the workbook is reprotected. Again, these cells were not within the ranges modified, and I'd rather not have to format every cell in every one of the 5 sheets of (Filename) to be locked.
File names are: "Finished Goods Inventory TREE (ToDMSI).xls" (alias: Workfile - sheet providing file path to be updated) "Random_workbook_selected_from_previous_filename.xls" (alias: FileName - the copy-to file being updated) "MACRO TEST BOM Master.xls" (the copy-from file always open)
Comma Delimited table layout in "Finished Goods Inventory TREE (ToDMSI).xls": Col A, Col B, Col C, Col D Customer ID,Item Code,File Name, Directory 4FRE01,4FRE01-0001,4FRE01-0001.xls,4FRONT
I have a sheet witch has a number of tick boxes and depending on the response a number of hidden rows may open to allow further info to be recorded, how do I protect the sheet in excel 2003 as unlocking certain cells & protecting the sheet will not work.
I need to unlock and lock certain cells when other cells have data entered.
I also need to enforce in the unlocked cells a minimum value.
In my worksheet i have the below requirement
Initially Cells C6 and 7 are unlocked and Cells C5, 8 and 9 are locked
1. Cell C6 must have a value entered greater than or equal to 50, when this value is entered I need to unlock cells C5 and C9 and lock cell C7 2. Cell C7 must have a value entered greater than or equal to 50, when this value is entered I need to unlock cells C5 and C8 and lock cell C6
I also need to unlock cells C15 and 16 when C13 has "Yes" selected in the drop down menu.
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.
I have a spreadsheet where I can change the colour of a cell by clicking the mouse, I also have text in many of the cells.
What I need to do is protect (lock) the text so that no one can change the text in any of the cells, but I still want to be able to change the colour of the cells by clicking the mouse in that cell.
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.
I am designing a car park rota on a spredsheet.Each person has a limited number of spaces. Is it possible to automatically stop people going in and deleting out entries from previous weeks (to stop any cheating!) or would I have to manually go in at the end of each week and protect.
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
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.
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.
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.
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.
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.
I have an excel sheet which will be password protected and only opened as Read-Only. There are 3 cells which have a dropdown list associated to select YES or leave blank. When YES is selcted in any of the 3 cells i want to lock the other 2 cells.
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.
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.
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.)
I have a spreadsheet where I need to lock certain cells, only problem is this then takes away the ability to filter the columns which I also still need in that spreadsheet.
Is there a way to lock cells so that they can not be edited but also keep the filtering function?
I am trying to protect cells & password protect my workbook on closing. Here is my code. Not only is the cell locking code not working, but it is also preventing the code from re-hiding the columns ("P:P,R:R,W:W,U:U,AA:AA"). Here is my code; I've tested it on a blank sheet & it was working:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ScreenUpdating = False Application.EnableEvents = False
