I have certain cells that are unprotected but still will not allow the user to highlight "Fill" the cell. The can only edit or type in the cell.
How can I allow a unprotected cell to also be filled with color by a user on a protected worksheet?
I want to highlight the unprotected cells as seen in the attached PDF document in excel 2007.
View 14 Replies View RelatedI am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells.
For example:
Cell 'A1' starts out blank.
Cell 'B1' has a drop down to select specific items.
Cell 'C1' is the date of the order and is a locked cell.
Cell 'D1' is the date of completion and is a locked cell.
The worksheet is protected to prevent direct changes to cells C1 and D1.
If a user enters any information in A1, then C1 displays the current date.
If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.
Using excel 2003, Workbook has several sheets, all sheets are protected workbook is not. I have my cells containing formulas locked, I have unlocked the other cells that require input of various data. When the user goes to type in a "unlocked" cell, (to over write the now obsolete data) it will not allow the user to delete the data, the curser remains a cross and the formula bar or the cell itself will now allow any changes.
I have double checked that the cells are unlocked. I can enter data after the last entry in the unlocked row and or column, but I want to be able to "cut or delete the data that is no longer need and begin entering data from the beginning cell of choice. I want to copy this "old" data to an archive sheet, but it will now allow me to select it.
Example: I have 6 columns starting, (a to f) the formulas are in columns e and f and start at row 4 These columns are locked and hidden. Columns a,b,c and d are unlocked for user input. All data is started from A5 which I want it to start from each time the old data is deleted and new data entered. If I place my curser on any of the cells in the e and f rows, the cursor remains a cross which is what I expect it to do, but columns a to d are doing the same thing, they are acting as if they are locked OR atleast those cells in the a to d areas which have existing data, as I can enter below.
I have unprotected cells in excel where I need to change the content (number) and cell color, but I don't want the cell borders to change. I'm a beginner with vba. I've tried protect sheet and workbook options, but looks like I need a vba code and don't know where to start.
Win 7
Office 2007
I'm trying to change a cell in a 2007 workbook but I'm getting this message:
"the cell or chart that you are trying to change is protected and therefore read only. To modify a protected cell or chart first remove the protection using the unprotect sheet command (review tab, changes group) you may be prompted for a password."
However, the unprotect button on the changes group tab is greyed out (the protect button is available though). The workbook isn't password protected and I'm not being asked for a password. What am I missing?
I have a button, and I need a Macro that will cause it to clear all unprotected cells. I think I can figure out how to get it to clear an individual cell, and I suppose I could enter every cell I want cleared individually...but that might take awhile.
Is there a way to set up a Macro that will delete the contents of every unprotected cell? I guess I haven't tried it yet - if I protect the cells, will it not delete those? Also, there is a small image - I don't want that deleted either.
I need to know if it is possible for a macro to run if a specific worksheet is unprotected?
For example, if someone takes off the protection to my costings sheet either through VBA or finding out the password, i want Excel to run a certain macro if possible?
I have a spreadsheet that has I have protected. On this sheet I have a button labeled Reset Form. When you click the button it it goes to each Unprotected Cell and deltes the contents. I created this by starting the macro and then tabbing to each and every cell and deleteing the contents I then ended the macro...
Is there a code that I can use for to clear all unprotected cells as opposed to creating the macro manually?
I am working on a worksheet that has been protected off and on. I unprotected this AM, but XL will not allow changes and instructs to unprotect the WS, but it is already unprotected. The WB is not protected and never was. I am at a loss as to how to work around this problem.
View 9 Replies View RelatedI'm not an Excel expert and I'm wondering whether to protect an Excel workbook in order to allow users to modify only specific cells in the sheets.
Also, each user should modify only his related sheet whitout permission to modify the ones created for the other users.
Function Dice(DiOne, DiTwo) As Double
Dim sum As Integer
Dim DiOne, DiTwo As Integer
Dim Odds As Double
DiSum = DiOne + DiTwo
Select Case sum
Case Is = 2
Odds = 1 / 36
Case Is = 3
Odds = 1 / 36
Case Is = 4
Odds = 2 / 36
Case Is = 5
Odds = 2 / 36
Case Is = 6
Odds = 3 / 36
Case Is = 7
Odds = 2 / 36
Case Is = 8
Odds = 3 / 36
Case Is = 9
Odds = 2 / 36
Case Is = 10
Odds = 2 / 36
Case Is = 11
Odds = 1 / 36
Case Else
Odds = 1 / 36
End Select
Dice = Odds
End Function
I'm trying to allow users to enter a cell in the worksheet and type =dice(number, number) and it calculate the probability of that amount coming from two rolls of a dice...
I'd like people to be able to use this just as though it was the SUM or VLOOKUP functions....
I have created an Excel template for others to use. They can enter data in certain unprotected cells and the data is linked to other places in the workbook. If they Cut and Paste something in the unprotected cells, the formulas get a REF#. There is no problem if they Copy and Paste. How can I prevent the REF# error without resorting to VBA?
View 3 Replies View RelatedI'm currently using a vba script to unprotect all of my worksheets within my workbook in order to refresh the pivots and then protect them again once the pivots are completed, but there are two worksheets I would like to remain unprotected in order for users to be able to edit them after the pivots have been refreshed. Now I've tried adding a sub code in order to unprotect one of the sheets as a test to see if it will allow me to edit the worksheet after I update the pivots. Unfortunately the coding isn't working, and the worksheet is still remaining in protect mode. Here's the code I am using to protect the worksheets when refreshing the pivot as well as the code I am attempting to use in order to unprotect the one worksheet.
View 4 Replies View RelatedI have macros that I use in my PERSONAL.XLSB to protect/unprotect all sheets.
My unprotect sheets macro is:
[Code] .....
Some of my workbooks have many sheets, but have different passwords.
What I would like to change is, instead of the "One or more worksheets could not be unprotected". I'd like to have the msg box show a list with the names of the worksheets that couldn't be unprotected.
The Special GoTo function doesn't have a listing for highlighting (going to) only protected or (preferribly) unprotected cells. Is there an easy way to do this?
View 9 Replies View RelatedI Need to protect some cells from being Changed or deleted, if i protect the whole sheet there are some cells that need to be changed using the formatting bar I.e borders, colours, font- colour, Merged cells.
the protection setting allows you to leave individual cells unprotected however you still cannot change the border for example.
Excel 2007 or 2003 Is there code to be able choose the formating of a cell when right clicked if the cell is Unprotected. But, the sheet is Protect and the Workbook is Protected. Without Unprotecting anything 1st.
The reason being is sometimes the user may need to change the type of font from Arial, or Gdt or Y14.5M.
I have the following code (provided here some years ago) which works fine. But I now need to modify it to include an optional password and to allow sort of unprotected cells on manually chosen sheets.
[Code] ....
I have a file used by numerous facilties. I would like code to track any time a protected sheet has been unprotected / protected. I would also like the stamp to show the user data if that can be done.
View 2 Replies View RelatedI have a worksheet that has headings along the and summary stats down the right and bottom of an area where details can be entered.
Depending on a persons user name columns are hidden/visible so that they can only enter information relevant to their section. The workbook is also shared.
What I want to do is stop people from being able to overwrite the headings or formula that calculate the stats but as the workbook is shared and the VBA that hides/unhides the columns needs the sheet to unprotected I am having to use code to prevent this: ....
With the code below it searches my entire workbook.
What do I need to change so it will ONLY search UnProtected cells in my workbook?
Code: ....
Is it possible to adjust column width of a PW protected worksheet without unprotecting it, or unprotect it and adjust in background then PW protect it?
On sheet1 H1 = column width value
Will adjust Sheet2 (password protected) column E width
Amongst the several sheets contained in my workbook, there is one called 'Inspection Report'. Users fill in whatever data is required in the other sheets, and once they get to this one, they are supposed to enter a number from 1 to 3 into Cell X1 (which is currently selected) before they select anything else. Unfortunately, I am currently unable to stop them from doing what they should not be doing.
So, I would like to have a notification of some sort pop up into their face if they click or move the selection anywhere else while Cell X1 is still empty. Something like a validation would be nice.
This is a conditional formatting problem I have not been able to resolve:
I have a range, say A1:N30
Each cell contains a text/number combination.
I would like to highlight each cell on a specific row if the vale exists within the row above - the issue is that the cell values are not in the same columns.
I have attached a sample workbook with the desired output.
Conditional formatting.xlsx
I have a spreadsheet where if Cell A2 says "Deposit", Rows A2-E2 would be formatted with a Blue background.
I've tried conditional formatting, but I can't built it into a Macro (I can't make it run), and it only highlights the cell that has that value. I also tried having the spreadsheet filter down to only the values I want highlighted, then highlighting all cells and un-filtering the column- this didn't work either.
It would basically say :
If (any cell in Row A) has text = "Deposit" (it needs to be exact, it can't be "containing"), set cell with the word "Deposit" and 4 cells to the right as Blue (I'm not picky about the color).
If I wanted to highlight a cell if any cell in the range to the right was greater than zero, what formula would i use. I have tried =IF(L1:AD1>0,1) with the result returning for only the cells in column L. Row 3 has no value in column L but a value in column N with no result to highlight the cell.
View 4 Replies View RelatedLet's assume Cell A2, A3, A4, etc, only contain names/text.
Then let's assume I have set up cell B2, B3, B4, etc, to contain a percentage value based on a formula and some other entries I have made.
It should be simple, but I'm having trouble - how do I set up A2, A3, A4, etc, to highlight into different colours based on Column B's data/percentage falling into a certain range?
ie: - if B2 is between 0% - 25%, then highlight A2 green
- if B2 is between 26% - 50%, then highlight A2 orange
- if B2 is between 51% - 100%, then highlight A2 red
How can I change the font color or highlight cell A1, when I select cell A2. Cell A2 is still blank, no value entered yet. I also would like cell A1 to return to original font color or no hightlight when cell A2 is no longer selected (active), whether a value is entered or not in cell A2. Let me clearify, (A1 thru N1) and (A3 thru N3) have diferent dates and the font color is white, as is the cell too. Therefor you can not see the dates in (A1 thru N1) or (A3 thru N3).
However, when any cell (A2 thru N2) or (A4 thru N4) is selected (example A4), the cell above it (cell A3) changes font color or highlights, so the date can be seen in (A3). But when cell (A4) is no longer selected the above cell (A3) hides the date (changes back to white font on white cell). Data or a value is not necessarily entered into (A4) for (A3) to change. (A4) is only selected to show the date in (A3). But if data or value is entered into (A4), the date in (A3) will still be seen until (A4) is no longer selected/active.
i have got a spredsheet, with clients names on it and in cell Q i have the clients account number, when iam looking at the clients name i also want to see the number, is there any way to highlight the cell i am on.
What i mean isusing the arrows keys i want to make the row highlighted, is there a way to do this