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.
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 ...
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"
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 RelatedIs 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 RelatedThe 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.)
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?
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
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.
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].....
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?
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 RelatedI 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.
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.
Have you ever copy a row with formula in locked cells & insert it in a protected worksheet?
View 1 Replies View Related1.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 .
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 RelatedOn 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.
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 Relatedkindly help in jumping locked cell in a proteced worksheet..
View 9 Replies View RelatedSub 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.
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 RelatedOn 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 RelatedI'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()
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:
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?
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 RelatedIs there an easy way to show/return any Unprotected Cells on a Protected Worksheet?
View 8 Replies View RelatedHow 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
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.