Locking Specific Cell Blocks

May 7, 2014

When cross linking cells from one file to another, I sometimes lose the source link to rearranging cells on one of the other spreadsheets. Is there a way I can "lock" the source cell so that it isn't lost as a reference?

View 1 Replies


ADVERTISEMENT

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

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

Dynamically Adding Row Blocks Based On Cell Value

Apr 2, 2014

I've managed to get coding that allows me to insert single rows dynamically based on a cell value. Example is if I enter a value in cell C3 the coding inserts the same number of rows below.

I've also created a simple macro that inserts a multiple row block under the active cell, complete with formatting, text and some merged cells. Example is I run the macro and it will insert a block of 3 rows, complete with formatting, borders, text and merged cells.

My challenge is to bring these two elements together. So enter a number in a single cell which will then insert the same number of row blocks, complete with formatting etc. I've added a file to sample what it is I'm trying to achieve. I can add the coding I already have as well.

Dynamic list sample.xlsx‎

View 2 Replies View Related

Locking Cell If Value

Mar 29, 2007

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

Excel 2010 :: Enable Cell Locking Based On Value In Another Cell?

Jul 17, 2014

Is there a way in Excel (2010) to lock data from being entered into cells based on the value in another cell? Here is an example:

Row 4 contains dates from 2015-2030 as a header starting at column C (C4 and on) that represents years of revenue. Column B contains dates as well, this date can be any year but this becomes the driver for the data input in columns C through X. Column B contains the "Delivery Date"

Data is input in Columns C and so forth. The issue becomes that let's say that the date in C4 is 2016 and this is the beginning year. Obviously there should be no inputs for 2015 (C5) and the data should start to be input for this year at 2016 (C6). Often people just begin filling in the first available year not looking at what the year actually is.

Is there a way to add a formula or some logic/protection to prevent inputs in previous rows based on the values in column B? So if the value in column B is XXXX than there can be no inputs in cells less than that value?

View 6 Replies View Related

Conditional Cell Locking?

Mar 8, 2013

Is it possible to have a formula in a cell, based on the value in another cell, but if the other cell value is not true then allow for data entry in the cell

So
J10 executes a formula if the value in I10 (a list field) is 'Cash and Shares' =IF(I10="Cash & Share",ROUND(IF(AND(K10<>0,O10>0),K10/O10,0),0)).

However if the value of I10 is anything other than 'Cash and Shares' then do not execute the formula in J10 but allow users to enter another value.

Note: the cell needs to be locked. The worksheet will be protected and will allow users to Select Locked Cells and Unlocked Cells.

View 3 Replies View Related

Locking Hyperlink To A Cell?

Mar 30, 2012

I am working in a workbook that uses hyper-links to navigate between locations on various sheets.

When rows are added to a sheet above the destination cell, the hyper-link no longer points to the original data but has moves the corresponding number of rows that have been added.

Is there anyway the hyper-link can be locked to a cell and adjust automatically if new rows are added or existing rows deleted?

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

Locking Cell After Data Entered?

Jan 27, 2013

I have a command button [Start] that when clicked, will enter the time the process started. I then wish to have the cell locked so the time cannot be changed after it has been entered. I tried this code:

Private Sub CommandButton7_Click()
Range("I5").Select
ActiveCell.FormulaR1C1 = Now()
Cell.Locked = True
End Sub

But am getting an error for the "cell.locked = true"

Any proper code to lock the cell after the data has been entered?

View 4 Replies View Related

Protecting Or Locking Cell In Certain Column

Feb 26, 2007

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

Locking A Cell After Data Entry

Nov 17, 2008

I want to lock cell after cell in a worksheet after data entry. Entering the data is what drives the locking of the cells. All cells with data are locked but those without are unlocked.

View 9 Replies View Related

Locking Cell References For A Whole Range At Once

Dec 8, 2008

how to lock ALL cell references in the formulas within a range in one go, rather than ahving to go to each and every cell and lock them manually?

I have a table with 966 cells all with just one cell reference like this: .....

View 9 Replies View Related

Remove A Hyperlink Without The Cell Locking

May 24, 2007

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

Locking Cell Dependent On Data Entered

Oct 18, 2009

I'm working in MS Office Excel 2003. I have a spreadsheet with numerous drop down boxes and references to named ranges. Here's my current quandary:

In cell I12, I have a drop down box with listed choices A, B, C and D. In cells I15, I18, I21 and I24, I also have drop downs.

What I'm trying to do is when choice D is picked from I12 it locks cells I15, I18, I21 and I24 so that the choices in the drop down cannot be picked. Also, a popup message is displayed.

I was able to receive help in getting this pretty much taken care of. But the problem I'm having with the code is that the popup message is also being displayed whenever I make a choice from other drop down boxes. So if I choose option D from I12, it locks the other 4 cells and displays the message just fine, but then if I choose anything else from say K12 or L21 then the message continues to come up. Here's what I have...

View 5 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 Multiple Cell Reference Formulas At Once

Jan 15, 2010

I am working on a massive Pivot Table but in order to compile it I have to copy data from many worksheets into one. My problem is that each worksheet contains about 11,500 formulas with references to other workbooks and cells. However, none of these is locked for cell reference (meaning none has the $ sign for row or column) so copying and pasting obviously screws it up since the reference is changing but I need to have the links active in the pivot table.

It would obviously take me a year to add 440,000 individual $ signs. Is there any way, a trick of sorts, where I can just highlight the entire worksheet and add the $ sign to each and every cell reference automatically?

View 7 Replies View Related

Locking Cell Dependent On Data Entered..

Oct 10, 2009

In cell D10, there is a drop down menu with choices A, B, C and D. Cells D13, 16, 19 and 22 also have a drop down menu. What I would like the code to do, is when option D is chosen from D10's menu, cells D13, 16, 19 and 22 are locked so that the user cannot choose an option from any of those cells.

So basically, if D10="OptionD" then [whatever the value is to lock cells D13, 16, 19 and 22 from drop down use)

If possible, I'd like this as an IF function, and not a VBA macro. I'm not completely familiar with all of the IF functions...um...functionalities, so forgive me if I ask the impossible. But if the IF cannot do it, VBA code is fine.

View 9 Replies View Related

Toggle/Switch Cell Locking/Protection On/Off

Sep 4, 2006

I work in the field of education and the spreadsheet I'm working will require pupils to enter answers into the spreasheet. When a command button (New Questions) is pressed I want the VBA code to switch the cell protection (locked) on or off depending on a random value in cell c2. If c2 = 1 I want f3 to be locked, if c2 = 2 then d3 needs to be locked, but not f3. I've attached a copy of the spreadsheet to this email

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

Conditional Locking Of Cell Based On Another Cell

Oct 7, 2008

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

VBA Code For Locking Cell Disabling Dropdown Validation Lists

Mar 28, 2012

I am currently working on spreadsheet which is using a mix of Conditional Formatting, Validation and a bit of VBA code.

The spreadsheet is a log of vacancies and applicants, When "vacancy" is selected in A I have introduced some VBA code to lock down cells S:AT on the same row.

The code is working but the issue is it is stopping you using the drop down validation throughout the sheet, if you click the arrow that appears on the cell nothing happens, However you can still access the dropdown by right clicking and choosing the 'Pick from Drop-Down List' Menu Item.

The validation is set up by ranges from another Worksheet.

The Code I am using is:

Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
Dim myrow
Dim StarLock, EnLock

[Code].....

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

Paste Into Cell Is Locking The Cell

Apr 29, 2009

I have a sheet with locked and unlocked cells.
The unlocked cells are where the user inputs data.

The user at times will grab their data from say MS word (say a number like 565399 for example). They copy it with a normal Ctrl c or right click copy.

Then they go back to the workbook and sheet, click on the proper cell and paste (via ctrl v or right click paste).

The problem is, when it pastes, it pastes then locks the cell for some reason. Then the cell can't be changed and the other macros fail because its locked. Paste special values or text works ok but I cant expect the user to realize this.

View 9 Replies View Related

Minus Number From Cell And Result In Specific Cell And Specific Column

Jan 20, 2009

I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)

second question

I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)

View 3 Replies View Related

Add Names To Blocks

Oct 9, 2008

In a worksheet I have (not equal) blocks of data. In column A there is the projectnumber, in columns B-K there are data. Projects are devided by an empty row. All rows in some block have in column A the same value (projectnumber).

What I need is the following: I would like to add names to that blocks. The name must be equal to the projectnumber.

View 7 Replies View Related

Transposing In Blocks Of 10?

May 19, 2014

I have a list of numbers in Col.A which can be variable in length.The objective is to copy these numbers into Columns with 3 col gaps in between.So the objective is that these numbers are listed horizontally (with 3 col.gaps separating each nr).To achieve this I have written the script below. However, as the orig.list of p/ns in Col.A can be variable, I would like to perform the transposing in blocks of 10 numbers.The transposed end product should be listed 10 numbers in each row (with 3 col gap between)rather than in one row. how to modify my vba script to do the above?

Code:
Sub TransposeSkippingColumns()
Dim c As Range
Dim i As Long

[Code]....

View 8 Replies View Related







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