Clearing Unlocked Cells On A Protected Worksheet
Dec 6, 2007
I have a workbook with a series of sheets that have tables for entering data. The table row and column labels and formulas in certain cells of each sheet are locked and some sheets are hidden.
When opening the file I want to clear all cell contents (interior color, comments, data, etc) in all the unlocked cells on each sheet that is not hidden.
I tried protecting the sheets first so only the unlocked cells would be accessible, but when I run the code below I get an error saying that the clear contents etc. cannot be performed because the sheet is protected. If it is not protected, everything gets wiped out.
How can I keep the locked stuff but clear the unlocked cells?
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
ws.Protect Password:="aaa" 'Protect each sheet
ws.Cells.ClearContents 'clear content of any unprotected cells
ws.Cells.ClearComments 'clear any cell comments
ws.Cells.Interior.ColorIndex = 0 'set background colour to no fill
Active.Cells.Range ("a1") 'make the active cell the top left
End If
Next ws
View 9 Replies
ADVERTISEMENT
Sep 25, 2009
I'm using Microsoft Excel 2008 for Mac version 12.2.0 (090605) (Mac OS 10.5).
I created a sheet, selected some cells to be locked and others to be unlocked (using Format > Cells), then I used Tools > Protection > Protect Sheet (choosing Contents only, not Objects or Scenarios). This has the desired effect of protecting the locked cells from users accidentally typing changes and overwriting their current contents.
I can still enter new data in the unlocked cells as expected, but unexpectedly I cannot change the formatting of unlocked cells (e.g., shading, font - bold). These are *unlocked* cells so even if the sheet is protected, why can't I change the formatting for them? It is too tedious to Unprotect the sheet every time I need to change formatting of an unlocked cell.
I have zero experience with Visual Basic and can't even find options that others have described (e.g., right clicking the sheet name does not yield "Show code" and Tools > Macros does not yield anything comprehensible about VBA). So if your suggestions involve something complex, please do include detailed instructions!
View 4 Replies
View Related
Mar 14, 2014
The text disappears when I want to correct it (protected sheet). see the discription of the problem in the attachment with figures.
View 3 Replies
View Related
Oct 5, 2006
Ok I've spent over 2 hours reading MANY peoples issues with this "BUG". Back in 2003 was the earliest... no one has an answer that works.
When you protect a worksheet and only have UNLOCKED CELLS selected...somehow through loading and unloading the file... you can select locked cells....
I can not find a pattern but many people have had this issue all with no concrete answers.
Just by loading and saving, exit and loading and saving, exit and loading.... i can now select locked cells that I previously couldn't. (I can't do anything as it's still protected...) It's a pain because it wrecks my tab flow.
View 9 Replies
View Related
May 16, 2014
I have a some sheets in a workbook that have collapsible columns, but I need to have the sheet protected/locked. This is for a my company's price book that goes out to distributors, so I can't have the sheet unlocked to where they can manipulate pricing. However, I need to have collapsible columns. Is it possible to have these functional while the sheet is locked?
View 10 Replies
View Related
Aug 15, 2007
I know that there is a lot on locking cells but it is confusing to me. I am able to lock a worksheet but that is not what I need. I have an Excel document that has thirty to thiry-six names on it. I do not want anyone able to edit the names. But I do want them to be able to add a name.
Example:
There are 30 students in the classroom. A new student comes in. I want the teacher to be able to add that student to the roster but not able to edit the names above. What do I need to do?
View 2 Replies
View Related
Feb 17, 2009
Below is the code that I copied from another thread. But what I need is to enable this command when the sheet is protected.
View 9 Replies
View Related
Apr 11, 2005
I need to protect my worksheet and only allow users to edit certain ranges. I am allowing the use of autofilter which I can select when I apply protection. However, I am now aware I cannot sort protected cells..which is essential. I basically need to be able to sort a column titled 'surname'. The worksheet is a record of pupils attainment in my class.
Now, I have a macro which seems to do the job: ....
View 9 Replies
View Related
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
May 28, 2009
I am looking for a code that will clear all of my unlocked cell in sheet 1. That is not a problem but since many of the cells are merged I know it keeps throwing me an error saying cannot change contents of merged cells or something like that. Does anyone know how to get around this without unmerging the cells. I saw a code to unmerge all of the cells on a sheet but I really don't want to do this as I already have worked around most of my problems with the merged cells.
View 14 Replies
View Related
Mar 21, 2014
I have a spreadsheet that has mostly locked cells but randomly placed unlocked cells.
The sheet is completed, often saved and printed. We then go back into it and use it for another customer calculation and need to ensure all previous entries are removed. With the locked cells you can't simply highlight an area to clear so I am wondering if there is a way to clear the unlocked cells in one step other than entering on each on to clear.
Would there be any way that if we cleared the first cell the others would clear?
View 4 Replies
View Related
Oct 16, 2009
I'm putting together a time sheet that can used to calculate total hours worked by department etc. I plan on protecting the worksheet and only allowing certain cells to be selected and data entered.
Is there a way using VBA to place a button on the first spreadsheet that will clear all cells that are 'unlocked'?
View 6 Replies
View Related
Oct 4, 2012
I'm looking for a macro that only selects the unprotected cells in a range. So that I can copy a formula in these cells.
I found the below macro on the web but it will select all the unprotected cells in the worksheet. I guess this one can easily be adjusted to only select the cells in a range, but I do not know how to do this.
Code:
Sub SelectUnlockedCells()
Dim WorkRange As Range
Dim FoundCells As Range
Dim Cell As Range
Set WorkRange = ActiveSheet.UsedRange
For Each Cell In WorkRange
If Cell.Locked = False Then
[Code]...
View 4 Replies
View Related
Aug 1, 2006
I have a macro which when run firstly unprotects the worksheet, makes a change,then protects the sheet again with line of code below.
Sheet.Protect Password:="whateveritis"
What do I need to add to that to ensure that it allows selecting unlocked cells only.
View 2 Replies
View Related
Mar 12, 2007
Is there any code to change unlocked cells to locked..
I have worksheets for each month which are protected with unlocked cells. At the end of the month I need to go into the worksheet unprotect it and run a macro to change the unlocked cells to locked so I can protect it again. This is to preserve the data entered into the cells.
View 9 Replies
View Related
Jun 17, 2007
I am trying to select all unlock cells in the active sheet. However, my method checks through each cell in the sheet and it takes too long.
Sub SelectUnlockedCells()
Dim rng As Range
If IsNull(Cells.Locked) Then
'if null then there is unlocked cells
For Each c In cells
If c.Locked = False Then
If rng Is Nothing Then
Set rng = c
Else
Set rng = Union(rng, c)
End If
End If
Next c
Else
End If
If rng Is Nothing Then Exit Sub
rng.Select
End Sub
View 9 Replies
View Related
Jun 18, 2007
is there a way in VB to make it fast, and tidy, clear the contents of all unlocked cells on one sheet ?
View 4 Replies
View Related
Nov 6, 2008
I have a locked spreadsheet with unlocked cells and checkboxes. The unlocked cells is for the user to enter text. The user navigation is top to botton in column a in sequential order by row.
For example: I have an unlocked cell in A1, then 20 checkboxes from A2:A21 and then another unlocked cell in A22. My problem is that when the user types text in A1 and hits ENTER - the cursor jumps to A22 (the next unlocked cell and scrolls the screen down accordingly. The user then has to scroll back up to select the checkbox in A2 and so on down to A22 before typing in A22.
I have created a command button that floats...that ultimately I would like the user to be able to select after typing in an unlocked cell(instead of hitting ENTER) and it removes the cursor from the unlocked cell - leaving the text entered - freeing the user to select checkboxes because excel didn't advance to the next unlocked cell.
View 4 Replies
View Related
Dec 16, 2009
I have had a workbook that has 2 sheets.
When I protect the main sheet with a password, the unlocked cells are also locked.
When I move my cursor over any cells, it has the pointed finger as though it has a macro over it, which it doesn't.
When I click on any cell, the screen flashes and a large part of the screen momentarily it looks like a selected area.
I also am using Freeze Panes. Removed the Freeze and still no good.
If I use the horizontal scroll bar and click the right arrow 3 times, they become unlocked.
If I move the screen 3 clicks to the left, it re-locks all of the unlocked cells.
View 9 Replies
View Related
Feb 19, 2012
I want to utilise an artificial protection by keeping a cells value constant even though it can be selected, deleted, edited etc.
Is this possible? I want to make a sheet where the cursor isnt visible except on certain sets of 4 cells.
My method being leave the 4 cell sets unlocked and lock everything else and then protect the sheet.
This means only the 4 cell sets are selectable/editable but I only want them to be selected (as this triggers events) but I dont want their values to be changeable. The values will always be 0,1,2,3
View 9 Replies
View Related
Apr 13, 2009
I want to do is clear all the cells on a worksheet that are not locked. Problem is that any cell could be locked, no rhyme or reason.
View 9 Replies
View Related
Apr 2, 2007
When unprotected my spreadsheet is working fine (apart from the fact that my users can do anything anywhere!) However, as soon as I protect it I am getting a mouse cursor that looks like its hovering over a clickable button? This is odd as the area that its hovering over contains unlocked cells?
Then when I click one of the unlocked cells I get a message box that tells me that Excel cannot locate a macro? Its as though there is a macro being invoked by a click on the screen almost anywhere? as allowing full access only to certain ranges of cells is a pre-req for my project. my file is double the allowable attachable size so I have not attached it.
View 4 Replies
View Related
Mar 28, 2007
I have the following code that 'sort of' works.
Private Sub Worksheet_Change(ByVal Target As Range) ...
View 9 Replies
View Related
May 9, 2008
I have users that are using a protected Excel Workbook where they sometimes have a need to copy values from Internet Explorer paste them into the protected Excel Workbook. When this happens, if the users select the cell and don't actually paste the text into the formula bar (or by double clicking the cell), they can replace the destination cell's formatting - which in this case makes the cells a locked cell instead of a unlocked cell.
Now is there a way to automatically have the pasted value keep the destination formatting? Or perhaps is there a way to set the copy and paste settings so it only pastes text from Internet Explorer into Excel and not the formats?
It is not an option for the userbase to select the option choice of matching the destination formating when pasting as it is a very large user base without much Excel experience.
View 9 Replies
View Related
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
Sep 30, 2007
I have a spreadsheet that is very large and formated a certain way. I want to make sure that a user cant put the cursor in the lower left corner and copy cells by dragging, copy & paste, or do anything other than input data and be able to highleght text and reinput data in case they mistyped or misspelled the data.
ex: user clicks on cell E33 and types in '1234568' and then move to next cell. The user can click back on E33 and can correct by retyping. That is all the user is allowed.
The cells in question are also in ranges if this helps. EX: 'E33:AJ67'
View 9 Replies
View Related
Jul 19, 2002
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?
View 9 Replies
View Related
Apr 27, 2007
My computer runs on Excel 2003 but my Laptop is on Excel 2000, the problem is I have the below code on a spreadsheet I wrote on my Computer which runs fine but when I open it on my Laptop it comes up with a run time error it doesn’t like the clear contents line. If some one could explain to me why it is happening and how to get round the problem so it will work in Excel 2003 and 2000.
Private Sub Workbook_Open()
Application. ScreenUpdating = False
Sheets("data").Visible = xlVeryHidden
Sheets("Incidents").Visible = xlVeryHidden
Sheets("Front Sheet").Select
Range("A17:S17").Select
Selection.ClearContents
Open1.Show
Range("A17").Select
Application.ScreenUpdating = True
End Sub
View 2 Replies
View Related
Apr 29, 2008
I am trying to clear the contents of cells in a very large spread sheet containing certain words. I am running the following macro and it will only do the first part, so it will clear cells containing "deceased" but stops at "esa". There may be any number of other words or text in the cell and I want it to delete cells with "esa" by itself so for ex. not delete cell containing "vanesa". I am very amateur so I am sure this is something simple but I cannot find the answer or figure it out for myself.
Sub DeleteAll()
Dim lCount As Long
Dim rFoundCell As Range
Dim rLookRange As Range
Set rFoundCell = Range("A1")
Set rLookRange = ActiveSheet.UsedRange
For lCount = 1 To WorksheetFunction. CountIf(rLookRange, "*deceased")
Set rFoundCell = rLookRange.Find(What:="deceased", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
rFoundCell.ClearContents.......................
View 6 Replies
View Related
Aug 23, 2006
One of my work colleagues need to add a worksheet to an existing Excel Spreadsheet which has VBA behind it, but the worksheet is password protected. The developer who wrote the application has now left, so we have no idea what the password is. Is there anyway of getting round it, like cracking into the spreadsheet to find out the password, or another way?
View 2 Replies
View Related
