Variable Locked Cells Dependant On Cell Change

Jan 12, 2010

I'm trying to make a spreadsheet more secure to prevent unintentional changes.

What i'm looking to do is to lock all cells so no changescan be made unless the user clicks column A.

Column A should remain unlocked and when the user clicks it i would like the entire row unlocked for editing (but only after column A is clicked)

I will run a macro to lock the entire sheet again once the sheet closes.

View 2 Replies


ADVERTISEMENT

Locked Cells :: Change The Cell Color On The Click Of A Command Button

Mar 1, 2010

i want the user to be able to change the cell color on the click of a command button. the worksheet is protected. when the user click the command button the active cell changes to red and offsets by 1. then the work sheet is locked again.

The two problems I am having is 1. I want the range to begin from row 10, column k to column FD. all cells down

the second problem is the current code allows me to edit locked cell columns A to J ...

View 9 Replies View Related

Change A Worksheet Tab Colour Dependant On The Contents On A Range Of Cells?

Mar 24, 2014

I have a worksheey of data and i would like the tab to be a certain colour dependant on the the contents of a range of cells within that worksheet is that possible. i know i need to use VBA and it cna be done based ont he contents of one cell.

So for example i have a sheet of people i am awaiting payment from, based on the last column " bill paid" i want the tab to be Red if there are any blanks in this column/ range and go green when all the cells in the column/ range are filled with "y"

View 5 Replies View Related

Change Cell Colour Dependant On Text

Jul 20, 2006

how hard would it be to chane the background colour of a cell when a certain word is entered into it? so someone would type 'james' and press enter then the cell would turn red....would only be in a set cell range..

View 9 Replies View Related

Change Cell Colour When The Protection Is Locked

Dec 3, 2009

Is there any way to change the pattern color of a cell (or a group of non-adjacent cells), say from No Color into Red if the cell(s) protection is locked (or unlocked)? Seems to me that the Conditional Formatting cannot work this way.

View 3 Replies View Related

Change A Cell To Being Locked When Cell Equals Certain Value

Oct 14, 2007

The spreadsheet is password protected. The only cells that students can enter values into are the answer cells.

I am trying to figure out how to have a cell switch to being locked if the correct answer is inputted. ( I have a column that I hide that has the correct answers in it. Then I use a countif function to determine if the answer cell equals the inputted answer.)

View 9 Replies View Related

VBA Restriction To Cell Input Dependant On Other Cells

Oct 2, 2007

I have a sheet where I want to not enable someone to enter data in a cell unless there is data entered in a range of other cells.

Range A1:A5 compulsory data to be entered
Range A6:A20 No data can be entered can be entered unless A1:A5 is filled in

Does someone have some code that can enable me to do this?

View 9 Replies View Related

Cell To Respond Either Or Dependant On Values Of 2 Cells

Mar 3, 2008

I am trying to get a cell to respond either or dependant on values of 2 cells.

If A1 = 13
B1 = 0
C1 = 0

I would like D1 to return A1, but

If A1 = 13
B1 = 1
C1 = 0

or

A1 = 13
B1 = 0
C1 = 1

I would like D1 to return the sum A1 + B1 + C1

View 9 Replies View Related

Variable Data Dependant On Number Entered

May 10, 2007

Example;

Cell 1 equals a number
Cell 2 is the number entered into cell one times(X) 35 up until the number 6 is entered. Any number above 6 is Times 50. So. 1-6 is times(X) 35 while 6+ is Times(X) 50.

I am. Here's another example:

A salesperson gets a commission of $35 for each widget sold up to the first 6 widgets. If he sells more than six widgets, he gets paid $35 for the first 6 and $50 for anthing over 6.

The sales person will enter the number of widgets sold into a cell and the formula should do the rest.

I have been stuck on this for months.

View 4 Replies View Related

Need Cells Locked Based On Text In One Cell

Aug 7, 2012

What I'm trying to do has been done before, However, all of the Code i am seeing online is not working for me...so maybe i'm missing something

I want a range of cells to remain locked unless SCP is entered. I need this done for four different selections (SCP, SD, MM, PP)

So if SCP is entered, then a certain range of cells remains locked, If SD is entered then another range of cells remains locked, so on and so forth for the 4 selections.

This was what I was working with.... However, i am not sure if i need to protect or unprotect the spreadsheet before i run the macro, also am i supposed to leave all cells locked or unlocked?

Private Sub Worksheet_Change(ByVal Target As Range)
If [B3] = "SCP" Then
ActiveSheet.Unprotect ("PASSWORD")

[Code].....

View 1 Replies View Related

Dragging Cells Changes Range In Formula Of Locked Cell?

Feb 19, 2014

I have a workbook with multiple sheets interacting with one another. On one of them the user is prompted to make lists of expenses in multiple columns. The column labels are in row one, and row two has the sums of all the cells below. I have every sheet in the workbook protected, so that only the cells that need to be modified are unlocked. The problem is that the user can drag cells around and change the range of the functions in row two (locked cells).

For example, A2 has the function =sum(A3:A100). But if the user drags the values in A3:A5 to A6:A8, the formula in A2 changes to =sum(A6:A100). Is there anyway that I can allow the user to drag cells (this could be a useful feature), but keep the formulas in the second row fixed?

View 5 Replies View Related

Cell To Clear Contents Of Group Of Locked Cells?

Feb 22, 2013

I have created a worksheet with many calculations based on user input into several unlocked cells. I would like to create a cell that when selected, would clear the contents of this group of unlocked cells.

View 10 Replies View Related

Unlocking Locked Cells Based On Other Cell Text

Jul 5, 2012

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.

View 3 Replies View Related

Highlighting A Range Of Cells Dependant On Data In Any Of Those Cells

Jul 20, 2009

On the attached sheet I am trying to indicate that enough data has been entered by highlighting the entire column. Any 3 cells in Rows 7-13 and 18-36 will be filled in with any of the values from cells O6-O11 (hidden). When the total = 6 i would like to highlight the entire column to indicate it has been completed.

The aim is two fold: to ensure that the correct number of points is allocated in each race, and secondly to indicate which column is the next to be filled in (as human error sometimes misses the column and adds the points to either the previous or next columns). You could call it idiot proofing the sheet. At present i have the cells in Row 37 conditional formatted to show this, but would much prefer the entire column to highlight.

View 2 Replies View Related

How To Copy Row With Formula In Locked Cells And Insert Copied Cells In Protected Sheet

Mar 29, 2014

Have you ever copy a row with formula in locked cells & insert it in a protected worksheet?

View 1 Replies View Related

Protect Certain Locked Cells From Editing And Allow Certain Unlocked Cells To Be Changed On Multiple Worksheets?

Jan 31, 2014

1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.

2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)

3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .

View 1 Replies View Related

Change Form Caption To Cell Value Or Variable

Sep 24, 2009

it's possible to change the caption (title) of a user form to a variable or cell value? Or does it always have to be hard coded?

View 2 Replies View Related

Conditional Formatting Dependant On A Range Of Cells

May 21, 2009

On attachment, I am trying to highlight values in column AA dependant on any values appearing in range AF4: AQ8 less 15 minutes. eg at present the value in AA29 is 13:22. I would like this cell to highlight as it is greater than 15 minutes before AH4 at 13:25.

The purpose of the sheet is to highlight any times in column AA that fall within 15 minutes of the start of a major meeting. These major meetings are pulled from another sheet, and a drop down box is used on that other sheet that then transposes the major meeting times to this sheet. I have tried an "Or' statement in a conditional format, however an error message appears around not using a range in a conditional format.

View 2 Replies View Related

VBA To Clear Contents Of Cells And Keep Some Cells Locked

Jan 25, 2010

I have written this code to clear the contents of certain cells, lock the content of others and protect the sheet again it works on sheet1 but not on sheet 7. This is suppose to happen when the Print button on my sheet is clicked.

View 4 Replies View Related

Jumping Locked Cells

Oct 21, 2008

kindly help in jumping locked cell in a proteced worksheet..

View 9 Replies View Related

Check Locked Cells

Nov 6, 2008

Sub PastSpec()
If Selection.Locked = True Then Exit Sub
On Error GoTo ErrHan
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ErrHan:
Exit Sub
End Sub
I recorded part of it and adapted it afterwards. You will notice I have a line to check for locked cells, this does its job if the cell selected is locked. However if the cell selected is unlocked and the cell below is locked the code still excutes and pastes.

View 9 Replies View Related

Can't Click Hyperlink In Locked Cells

Jun 10, 2013

Any way to make it so that a cell can remain locked but be able to click a hyperlink? I need the cell to remained locked so nobody can alter the hyperlink name and value but I would like users to be able to click it and have it open in their browser.

View 9 Replies View Related

Locked Cells Have Changed Formatting

Jan 23, 2014

On an excel document that was originally formatted as a number and then locked, somehow users have changed the formatting to a date. I think it is an accident, but I can't figure out how they are doing it. I have several data files on a server where several offices have access to them to input data and somehow formatting gets changed sometimes, even when locked.

View 3 Replies View Related

Macro Is Unlocking Locked Cells

May 7, 2008

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

Here's the

Public FileName

Public Workfile

Public ItemCode

Public CustCode

Sub OpenBOMSeq()

View 14 Replies View Related

Locked Cells With VBA And Ability To Filter

Oct 23, 2008

how to create two buttons so that I can have a data entry specific protection and then a lock all button. Here's the link to the thread:

http://www.excelforum.com/excel-prog...-a-button.html

And here's the code I'm currently using:

View 3 Replies View Related

Select Locked Cells In 2000

Jun 15, 2009

I've created quite a few workbook/worksheets in excel 2007 compatibility mode that are data entry intensive. When protecting these worksheets, you have the option of allowing the user to "select locked cells". I invariably uncheck this option because the user doesn't need to interact with these cells.

However, several of my users are running Excel 2000 and the same option is not available when one protects a worksheet in Excel 2000. Is there a way to do that in VBA for these Excel 2000 users such that when they click on a locked cell, the cursor will not respond to that cell?

View 5 Replies View Related

Edit Locked Formula Cells

Aug 12, 2006

I have a template that I have hidden the formulas but allowed editing. Is it possible to create a pop up box for the end user to use to edit a cell with the hidden formula? This is a budget template with already set up formulas but the end user needs to be able to make edits but the end users are beginner excel users and if they double click on the cell the formula will automatically delete leaving the cell blank. I need a way around this so they cant view the formula but only change the data if they really mean to change it.

View 3 Replies View Related

Identify Locked Cells On Worksheet

Oct 19, 2006

Is there an easy way to show/return any Unprotected Cells on a Protected Worksheet?

View 8 Replies View Related

Color Protected/Locked Cells

Oct 7, 2007

How can i set up my spreadsheet so that all protected cells are a different colout to the main sheeT.

I would like this colouring to appear in the sheet when in protected and unprotected mode

View 4 Replies View Related

Inputting Dependant On Cell

Oct 12, 2008

on sheet1 which is called working sheet and cell d25 is asking what type of goods been sold

i have several different sorts"

ie
s25
s28
s50
s69
s70
s82
abd
t70
t140

i want the spreadsheet to look up somewhere on a sheet called delivery all the components that could be used for that type

so i guess i need to name define?

ie if s25 is selected

on delivery sheet in a20 it would then list all the components underneath each other.

View 14 Replies View Related







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