Identify Locked Cells On Worksheet
Oct 19, 2006Is there an easy way to show/return any Unprotected Cells on a Protected Worksheet?
View 8 Replies
ADVERTISEMENT
Protect Worksheet But Allow Selection Of Locked Cells
Mar 7, 2008I am fairly new to Macros and such so please bare with me. I work with workbooks that have 30-50 worksheets in them. They are fairly complex and we send them out to our agents to use and need them protected so they cant change anything. I found how I can protect all the worksheets with one macro. There is one more part that I would like to add to this macro that I cant seem to find an answer for on these forums (Yes I have spent the better part of the day looking).
When you manually go to protect a page. It asks you to type in your password for the first time and below that there are a bunch of boxes that can be checked or unchecked. I need the first box "Select locked cells" to be unchecked. It is checked by default. Is there something I can add to my macro to uncheck this box?
This is the topic I used to create my Protect/Unprotect macro.
Macros to Protect/Unprotect Worksheets and Workbooks
Can Hide Function Be Made Accessible In Locked Worksheet
Jan 5, 2014I have a protected worksheet that is shared between 2 departments. Is there a way of making the hide/unhide function available without unprotecting the worksheet.
Macro would be the most obvious way but believe you can not share a workbook when a macro is incorporated.
Filter Locked Worksheet Conflict - 2000 Vrs. 2003
Feb 1, 2007I recently launched a model that uses filtering on protected worksheets. The model was developed in Excel 2000, and everything works perfectly for the users who are also on 2000, but my Excel 2003 users get an error on opening the workbook.
The error asks for the password to unlock the sheet and is difficult to clear. Users have to hit Cancel 8 times to clear the error, at which point, the model appears to function perfectly. (There are 12 worksheets in the model, 2 with filters, all locked and password protected.)
The follwoing code is in the 'ThisWorkbook' module:
Private Sub Workbook_Open()
With Worksheets("Initiatives")
.Protect DrawingObjects:=True, contents:=True, userInterfaceOnly:=True
.EnableAutoFilter = True
.Protect contents:=True, userInterfaceOnly:=True
End With
Because the error occurs when first opening the model but not at other times, I'm thinking that there's something in the Workbook_Open procedure that is causing the error.
How To Copy Row With Formula In Locked Cells And Insert Copied Cells In Protected Sheet
Mar 29, 2014Have you ever copy a row with formula in locked cells & insert it in a protected worksheet?
View 1 Replies View RelatedProtect Certain Locked Cells From Editing And Allow Certain Unlocked Cells To Be Changed On Multiple Worksheets?
Jan 31, 20141.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 .
VBA To Clear Contents Of Cells And Keep Some Cells Locked
Jan 25, 2010I 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 RelatedJumping Locked Cells
Oct 21, 2008kindly help in jumping locked cell in a proteced worksheet..
View 9 Replies View RelatedCheck Locked Cells
Nov 6, 2008Sub 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.
Can't Click Hyperlink In Locked Cells
Jun 10, 2013Any 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 RelatedLocked Cells Have Changed Formatting
Jan 23, 2014On 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 RelatedMacro Is Unlocking Locked Cells
May 7, 2008I'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()
Locked Cells With VBA And Ability To Filter
Oct 23, 2008how 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:
Select Locked Cells In 2000
Jun 15, 2009I'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?
Edit Locked Formula Cells
Aug 12, 2006I 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 RelatedColor Protected/Locked Cells
Oct 7, 2007How 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
How To Paste Over Locked Cells When Pasting Over A Whole Column
Aug 23, 2013I have a formatted budget vs actual income statement that has subtotals in the appropriate spots. I would like to be able to paste over the entire column with updated data, but not write over the formulas. For instance, in one spreadsheet I have the data for the month of June and in another I have the data for the month of July. Both have the same number of rows, but are formatted slightly differently. I want to be able to replace the June data with the July data. My first thought was to lock the cells with the formulas, protect the sheet and then paste over the entire column, but I get the following error:
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 protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password)
When Protecting Workbook With Pw, Unlocked Cells Are Still Locked
Dec 16, 2009I 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.
Need Cells Locked Based On Text In One Cell
Aug 7, 2012What 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].....
Making Exception For Locked Cells In VBA Codes
Jul 6, 2014I have a big Excel file that is supposed to be used by many users and therefore I need some cells locked. But problem is I use these cells in my vba codes which the users must be able to run by using the buttoms in the file. But now I get error in every macro since the cells the code are trying the reach is locked. Can I make an exception for vba codes in some way so that I can run my macros?
View 4 Replies View RelatedCode To Identify Current Worksheet Name
Jan 22, 2013What is the code to identify the current worksheet name? I want to store it in a variable so I can return to the worksheet later. I thought that the code was this:
myWorkSheetName = worksheet.name
:
:
Sheets(myWorkSheetName).Select
I receive a "Run-time error 424: Object required" message on the worksheet.name statement.
IF Or LOOKUP: Search For Values From One Worksheet And Identify Whether Or Not Those Values Exist In Another Worksheet
Mar 23, 2009I tried both IF and LOOKUP and failed. I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:
=LOOKUP(B2,Sheet3!A$2:A$914,Sheet3!C$2:C$914)
B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 -to the bottom, which is incorrect.
Dragging Cells Changes Range In Formula Of Locked Cell?
Feb 19, 2014I 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?
Keeping Cells Locked And Protected When Copying And Pasting?
Jun 19, 2006So i have sheet 1 with some cells locked and protected (but open to copying) as a template and sheet two will be where people paste one or more of the template formula on an ongoing basis. How do i mantain the locked and protected cells after they have been pasted? I have unlocked sheet two pasted the first template and had to choose protect cells after pasting, and further pasting of locked cells are not protected.
View 1 Replies View RelatedCell To Clear Contents Of Group Of Locked Cells?
Feb 22, 2013I 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 RelatedVariable Locked Cells Dependant On Cell Change
Jan 12, 2010I'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.
Insert Row While Maintaining Merged Cells - Locked Sheet
Oct 28, 2011I have a protected sheet with merged cells. I would like the user to be able to insert a row and have the formatting (including merged cells) duplicated on the new row. I've searched and seen some different options using VB but I'm clueless as to how to use those. Is there a simple way to do this?
Copying the row and using "insert copied cells" won't work because they get an error because of the sheet protection.
VBA - Allow User Select Locked Cells While Sheet Is Protected
Mar 29, 2012Here is an excerpt of my code
Code:
Else
Sheet8.Unprotect Password:="rm123abc"
Sheet8.Range("V" & rw) = "Item is not found in the Data Base. Please Contact DME for all documentation requirements for this item"
Sheet8.Protect Password:="rm123abc", AllowFiltering:=True, AllowInsertingRows:=True, AllowDeletingRows:=True
End If
Unfortunately, once the code has run and has finished re-protecting my sheet, it doesn't allow the user to select locked cells. These locked cells contain hyperlinks, and they need to click on these hyperlinks to navigate through my workbook.
So I have two questions:
1. How can I enable allow user to select locked cells
2. Do I have to allow the user to select locked cells to access my hyperlinks, or is there another option?
Unlocking Locked Cells Based On Other Cell Text
Jul 5, 2012I 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.
Protected Sheet With Timestamps And Locked-after-saving Cells
Dec 31, 2013I have this excel sheet I'm trying to create (to make my life better at work). It's to track credit card refunds. It'll be saved on sharepoint. So my department will fill in the data on the "Ops" side, and then the accounts department will fill in their side. It's very rudimentary, but anything is better than the old, tattered BOOK we are currently using.
So far, I have the user/date/time stamps autofill, when the user fills in "Entered by" or "Recieved by". I have those cells locked so no one can edit it. I have separte passwords for both sides (Ops and Accounts).
What I want now is to be able to:
1. Lock each line once it's been entered and saved. Say my department fills in one entry. We save it. After that, it CANNOT be edited. If there are any changes, I'll put in a remarks column so people can enter stuff.
2. If someone enters the first two columns (Guest name, booking number), then the "Entered by" HAS to be filled, or the workbook cannot be saved.
3. ONLY letters can be used for Guest Name and Entered by. I've tried different things for this. While I've managed to make it work if the user enters a digit first, I can still enter John56, and it'll pass. I need it to only allow letters (and spaces).
4. The "Booking Number" should only allow six digits. No less no more than six digits. It can be 000001 even. But it has to have six. And, obviously, no letters, no spaces.
In the "ThisWorkbook" module, I've got this code:
Code:
Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="hurlEy!", _
UserInterFaceOnly:=True
Next wSheet
End Sub
In the sheet1 module, I've got this code:
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 3 Then
With Target
If .Value "" Then
.Offset(, 1).Value = Environ("username")
[code]....