Locking Cells Based On Criteria

Jun 28, 2006

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 7 Replies


ADVERTISEMENT

Locking Specific Cells That Are Hidden / Shown Based On VBA?

Jan 13, 2014

I am having trouble with my VBA code, it hides groups of rows (projects) based on the contents of corresponding cells above. these have either 'yes' or 'no' in them (indicating if the person is active in that project). The Yes/No is formula based from another sheet.

what I am trying to do, is have particular cells, withing the projects rows, locked (as they are also populated by formulas) but have other cells open for editing.

when I try and do this i get an error with my VBA, i am assuming that as cells are locked, the VBA is prohibited form hiding inactive projects/rows. i tried an addition at the bottom but this hasn't worked

Code:
Sub Worksheet_Calculate()
Dim i As Long, StartRow As Long, EndRow As Long
StartRow = 13

[Code]....

View 1 Replies View Related

Shading And Locking Multiple Cells From Input Based On Value In Separate Single Cell

Sep 23, 2013

Now the problem, Each client has a sheet in a workbook. In each sheet, I input the number of days of service available to that particular client in a single cell. We'll say cell [G8] is the cell used for this input in all sheets. If the client has 30 days of service available then we type [30] in cell [G8]. Each sheet is a 100 day template. I want to shade in dark grey and lock from input the days NOT available to the client based on the days of service input in cell [G8]. If 30 days of service are available in cell [G8] then the remaining 70 days on the 100 day template would be shaded dark grey and locked from input. That is, day 31 through day 100 of the template would be locked and shaded. Each individual day has multiple cells in a column. I want to lock and shade each column of cells in each day not available.

View 5 Replies View Related

Locking Different Cell Ranges Then Locking Whole Sheet With Macros

Jan 4, 2010

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 9 Replies View Related

Locking Text In Cells But Not The Ability To Change Colour Of Cells With Mouse Click

Mar 5, 2013

Locking text in cells but not the ability to change colour of cells

******** width="234" height="60" frameborder="0" marginwidth="0"
marginheight="0" vspace="0" hspace="0" allowtransparency="true" scrolling="no" id="aswift_0" name="aswift_0" style="left: 0px; position: absolute; top: 0px;">*********>

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.

View 2 Replies View Related

Locking Cells

Oct 6, 2008

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 9 Replies View Related

Locking And Unlocking Range Based On Another Cell

Jan 29, 2007

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..................................

View 6 Replies View Related

Fill Cells Based On Corresponding Cells Matching 3 Criteria

Jul 1, 2008

I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.

I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.

I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.

View 8 Replies View Related

Conditional Locking Of Cells

Jul 29, 2009

Lock cells based on conditions? For example, in my spreadsheet I need my cell J42 to stay locked at all times unless my cell C12 reads "Klongtoey".

View 10 Replies View Related

Locking Cells On Certain Columns

Jan 24, 2013

WILL IT BE possible to lock columns / cells for any user to avoid editing? Only the creator of the file should be able to edit?

View 2 Replies View Related

Two Different Persons Locking Cells?

Jul 16, 2013

Is it possible for 2 different persons to lock different cells on the same worksheet?

View 3 Replies View Related

Automatic Locking Down Cells After Use?

Oct 21, 2013

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.

View 7 Replies View Related

Conditional Locking Of Cells- More Than One Sub?

Aug 19, 2009

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 2 Replies View Related

Locking Cells Out With Macro

Sep 23, 2009

I'm trying to set up a macro that locks or unlocks a column depending on what is put in the column before it.

at the moment i've got the code written in the worksheet

View 2 Replies View Related

Locking All Cells In A Workbook

Feb 4, 2010

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 14 Replies View Related

Conditionally Locking Cells

Feb 23, 2008

I need to lock a cell (F21) if J26 is less than 100. Can't work out how in conditional formatting. Is there a different way?

This is what I need to do:

F21=(IF,J26<100,Locked,Unlocked)

View 10 Replies View Related

Locking Cells That Contain Formulas

Jan 31, 2007

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 9 Replies View Related

Locking Cells By Time

Jan 24, 2008

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 9 Replies View Related

Locking Cells Through Code

Feb 16, 2007

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 2 Replies View Related

Conditional Locking Of Cells ..

Sep 1, 2007

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 9 Replies View Related

Conditional Cell Locking Based On Font Color

May 26, 2009

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 9 Replies View Related

Locking One Cell If Another Had A Value In It (conditional Locking)

Oct 10, 2008

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 9 Replies View Related

Locking Multiple Cells On Value Of Another Cell?

Jul 16, 2013

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.

View 9 Replies View Related

Unlocking/Locking Cells With A Button

Oct 22, 2008

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 6 Replies View Related

Locking Cells And Protecting Sheet

Feb 14, 2007

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 3 Replies View Related

Locking Cells Depending On What Is Entered In Another Cell

Sep 22, 2009

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 5 Replies View Related

Locking And Protecting Certain Cells - Filtering Columns

Jan 22, 2013

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?

View 2 Replies View Related

Runtime Error 1004 - Cells Not Locking?

Jul 23, 2013

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

[Code].....

View 2 Replies View Related

Locking Cells Upon Entry Of Specific Data

May 14, 2009

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 9 Replies View Related

Prevent Locking Of Unlocked Cells When Copy/Paste

May 9, 2008

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 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved