Protecting Worksheet But Only Cells
Jul 21, 2006
I have a spreadsheet with about 2000 lines of vba code. My application needs to be protected so users don't mess themselves up; however, I really only needs the contents of cells to be protected. I have many macros that run, and in order to do this I need to unprotect and re-protect the worksheets. My problem is this: when I re-protect the worksheets, everything is locked up. I would like to allow users to change column widths, hide and un-hide rows, etc. I can't find the parameters needed to do this more controlled type of locking. Is there a list somewhere of all the parameters that can be passed to the Protect method? Here is my current subroutine:
Sub ProtectSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="password"
Next
End Sub
View 4 Replies
ADVERTISEMENT
Jun 5, 2009
I need to protect the attached to stop people deleting the formulas. The problem is that there are hidden macros and when i protect the cells the conventional way they stop working and i lose all the data in the IAS sheet. I have highlighted all the cells in yellow that i want to be able to change once protected.
At this stage I would just like some one to explain how to protect it. Ideally I want to do it normally (unlock the cells individually that I want to remain unprotected, then protect the whole sheet). If its not then presumably its a macro alteration that's needed?? don't do it at this stage as depending on how it will be protected will mean that i need to make a few adjustments.
View 3 Replies
View Related
Dec 22, 2008
I have set up a workbook with a tab for each month. in the workbook open module i have the following code (below marked Password protect) that allows for three passwords to unlock certain ranges of cells, what i would like to do is for this code to apply to each worksheet.
Now in the main this works except for one range of cells H11 to H1000, on every sheet all cells are locked except for this range which is unlocked in every sheet except sheet one when you enter the password staff. The only other code i have is for a time stamp (marked time stamp) so that i can monitor when the user has entered data. this again has reference to the sheet name.
their must be something really straight forwad i am doing wrong here as everything works on an sheets except this range being locked.
I am in excel 2002 but my users are on 98, which is why i am protecting in VB rather than using the prtect range option.
View 7 Replies
View Related
Oct 26, 2009
modify the code below to do the following:
When I recorded this macro shown below, I entered in a password (twice) as prompted by Excel 2003.
So when I run this code, it seems to work fine. However, when I want to unprotect the worksheet, I am not prompted to enter in the password.
modify the code so that when I need to unprotect the worksheet, I am prompted to enter in the password?
View 12 Replies
View Related
Oct 27, 2009
I am looking for code which will protect a worksheet based on the value of a particular cell.
i.e. If cell A1=1, then the worksheet will be protected (ideally with a pswd)
View 5 Replies
View Related
Apr 17, 2014
I am working in the D3:K20
I have set my column width at 3.6 and row height at 19.6
I want to protect this area so that the column width or row height don't change.
View 1 Replies
View Related
Jan 25, 2008
I wish to protect a worksheet to prevent loose fingered people from altering formulas, but I want the "text box" that is below these formulas (within the same worksheet) to be able to be typed in.
But when I protect the worksheet, the text box cannot be typed in.
Is there a way around this to allow typing in the text box whilst the sheet is protected?
View 9 Replies
View Related
Jan 28, 2009
I am using the following code to enable users of a form to made make selections by adding a tick in one of the boxes by clicking on it. It works fine with thesheet unpotected but when I protect the sheet with cells E33:k33 unlocked i get Run Time Error '1004'
Unable to set the Name property of the font class.When I debug, Target.Font.Name = "Marlett" is highlighted.
View 4 Replies
View Related
Nov 24, 2011
Intention is to (automatically) fully protect each completed worksheet/workbook on "save"/"save as" in MS Excel 2007... Is this possible by means of a macro? Which one? Saving in a ".xlsm" format is required?
View 9 Replies
View Related
Jul 17, 2002
Is there a way to just protect just a single cell, in order to lock & hide a formula within that cell, without having to password protect the whole worksheet?
View 9 Replies
View Related
Dec 2, 2008
I have users filling in data in columns C and D, I need a macro which will select everything entered and lock those cells - well the catch is, if both columns have data. If column C or D are empty I need that row to remain unlocked. No sorting can occur as well.
View 2 Replies
View Related
Jan 11, 2009
I am working on both MS Office 2003 and 2007. I am currently working some formulas on the worksheet which I would like to be protected. Therefore I would like some cells in the sheet to be protected and therefore only the person knowing the password (administrator) will be able to change.
View 3 Replies
View Related
Nov 6, 2009
I would like to lock some collumns and yet change the formating on the unlocked cells mi other columns. Is there another way to protect cells other than locking cells and then protecting the worksheet?
View 5 Replies
View Related
May 6, 2009
How can I password protect a certain range of cells (titled 'list'), that when someone tries to edit them, a password dialogue box appears, saying only bob can edit these cells please enter password
View 9 Replies
View Related
Oct 27, 2006
I have a shared workbook is it at all possible to leave the cells unprotected so a user can enter data but prevent cells from being formated
for instance someone on night shift with nothing better to do has decided that black text on a white background is boring and changes it frequently to a rainbow of colors. and text types
it is making a well laid out and good sheet look like something a child has done
I would pursue this down a discipline line but it has proved impossible to nail down the culprit from the many users
View 5 Replies
View Related
Feb 14, 2007
A1 is number of days available
this sheet goes to a routing recepient from me who fills A2 and B2. C2 and D2 are calculated.
A2 has starting date
B2 has ending date
C2 is days between these two dates
D2 is number of days left i.e. A1-C2
I want to lock and protect cells A2,B2,C2 and D2 so that these cannot be changed when I route it back to get next set of dates to be filled by the same person.
View 3 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
Jan 22, 2013
I have a spreadsheet where I need to lock certain cells, only problem is this then takes away the ability to filter the columns which I also still need in that spreadsheet.
Is there a way to lock cells so that they can not be edited but also keep the filtering function?
View 2 Replies
View Related
May 13, 2008
I have a spreadsheet that i use as a log for recording when certain tasks have been completed.
Each worksheet in the workbook represents one month. Within each month, there are monthly tasks and weekly tasks. Currently i manually unlock the relevant weekly cells at the beginning of each week, and lock them again at the start of the next week. Similarly with the monthly task cells.
Is there a way for Excel to automatically unlock the relevant cells based on date so i don't have to go through this task at the start of every week and every month?
View 9 Replies
View Related
Jul 15, 2014
I have certain cells in my spreadsheet that contain formulas linking them with other sheets within my workbook. I have grouped sets of rows in my first 2 sheets (to show just headings and to expand to subheadings & details). When I try protect the cells with formulas on the sheet then it blocks me from expanding or contracting the row groups.
Is there an easier way to stop someone editing or deleting the data in my cells? There are about 2 dozen cells with formulas in my first sheet that need protecting - unfortunately not in any particular order...
View 8 Replies
View Related
Dec 3, 2013
I've found many resources that tell me how to allow for the insertion/editing of comments within a protected worksheet. When I protect the worksheet, I can select "Edit objects" to allow anyone to insert comments.
However, my issue is that I'd like the Insert/Edit comment functionality to require a password, and this happens to be the opposite of what those resources instruct. I also want the content in those same cells to be editable by anyone.
View 2 Replies
View Related
Sep 19, 2007
I have a worksheet from which I regularly clear all contents by selecting all the rows and selecting 'Clear Contents' from the right button menu. However, I now have added a formula in Col Y that I want to protect. I know I could select all columns up thru X, but that would clear the R1 headers, so I want to avoid doing that. I can always work around, but I'm curious, is there a way to clear only values and thus protect the formulas when using the Clear Contents command?
View 9 Replies
View Related
Feb 7, 2014
I need to reference the projected and actual expenses from the total on worksheet 'expenses' So i did so, however, if I change the drop down on the expenses worksheet to only display housing data, then the projected and actual expenses on my budget worksheet changes as well to the new data portrayed on the expenses worksheet.
I need to reference the cell, without it changing when I change the category display, but I need the cell to change accordingly if I enter new data in the overall tablet on the expenses worksheet.
Or do I need to create a seperate worksheet that has the data in and reference my cells on my budget worksheet to that new worksheet?
View 1 Replies
View Related
Sep 24, 2012
I've been tackling this data capture/paste issue for a week or so. I found the string below which does provide a good foundation for my challenge. But, my basic level of understanding macros limits my modifications to meet my needs.
[URL] ......
I have 20 worksheets in my master file corresponding to Excel files individual associates will update weekly. After the associates have updated their individual files for the week, I want to capture the data entered and paste values into a master file containing a worksheet for each associate (sharing the same name as the individual associate file). All of these files are housed on team SharePoint sites.
I need a macro to perform several steps after clicking a "Run Update" macro button in the master file:
Open individual associate fileIn master file, search for each Initiative listed in column B (starting cell B3) in the individual associate file (in column B starting at cell B11)If Initiative is found in individual associate file, copy adjacent data in columns D:J for the respective rowIn master file, paste values to the corresponding Initiative row for the corresponding week's worth of dataIf Initiative is not found in the individual associate file, move to the next Initiative listed in the master fileRepeat these steps for each individual associate file
Linking would be the easiest way to accomplish this if I wanted to have a multitude of weekly individual files for the associates. However, I'd rather each associate have one file for them to update (basically overwriting their previous week's entries).
I need to ensure the paste values corresponds to the appropriate day of the week. In simpler terms, if the date in the individual associate file in cell D9 reads Oct 1, 2012, the data captured from that row needs to be pasted to the corresponding row/column in the master file that reads the same date.
View 2 Replies
View Related
May 18, 2006
I need help with a macro for copying and pasting of cells. I believe this should not be a problem for the Excel VBA experts, but for someone who can only record macro, I'm really at a loss.
Attached is a sample file, where sheet 'Source' is an example of the sheet from which data are to be copied. The other sheet, sheet 'Final' is an example of the final format that I need. The reason I'm doing this is I'm planning to upload my data into Access and so I need to convert them into a list format.
List of target columns in sheet 'Final' and source cells in sheet 'Source':
Column A: Biz ID - not sure if I really need this, by right it should be listed automatically once I paste the data
Column B: B2 of 'Source'
Column C: B2 of 'Source'
Column D: B1 of 'Source'
Column E: row 6, relevant column
column F: column K
column G: row 5, relevant column
column H: the specific amount
So basically I'm creating an entry for every amount in the table.
View 9 Replies
View Related
Jan 21, 2013
I have a monitoring database and I want to create a 'source' sheet in sheet 1 whereby when I enter names into a certain column they rename different tabs/sheets in the worksheet. for example, the name 'Brown' inputted in cell 'A2' would rename sheet 2, Black in A3 would rename sheet 3 etc.
View 2 Replies
View Related
May 18, 2009
I would like to protect the worksheet from modification by other users except column A ( suppose A1: A15).
I have been doing this by removing Locked on format cell and then Protect Sheet on Review tab.
Is it the right way or there are other more reliable way?
View 2 Replies
View Related
Feb 6, 2010
I have created a form that allows users to input data to an excel sheet, and also will enable users to view the data once they are done.
Is there a way to unprotect the sheet using VBA so that the data can be written, and then protecting once the data has been input?
View 2 Replies
View Related
Feb 4, 2009
I've got about 50 worksheets in a workbook and I'd like to protect them all.
Problem however is that I've got buttons in my worksheets that insert rows, delete rows.
I've also got a dynamic chart with listboxes that select data.
When I protect my worksheets all this is disabled.
Is there a way around this?
This is what I've done so far:
[url]
View 14 Replies
View Related
Nov 27, 2006
I have a bunch of worksheets in my workbook say around 50
Each worksheet has the same template layout!
I am trying to figure out a macro that will select all worksheets from 'KLKN':'KFSN' and protect the cells L45:L52 accross to AL45:AL52
Unfortunately I cannot select all worksheets and protect them, instead I would have to manually do this, or create a macros that will do it for me...
View 9 Replies
View Related