I am working on an excel table in my worksheet contains formulas and locked cells. lets say table range is from column "a" to "f" and there is formula on cell in columns "c" and "f". I just locked cells in column "c" and "f" to prevent people changing the formula accidentally. but when the sheet is protected i am not able to insert or delete rows as the rows has locked cells in columns "c" and "f".
I tried the below macro to unlock the sheet, insert row and lock the sheet again:
[Code] .....
So by running the macro it asks the password and do the job but the issue is it needs password and i do not want people to know my password. I don't want them able to unlock the workbook and make any undesired changes. I want them to be allowed insert or delete rows and input data in unlocked cell. I want the sheet to be locked all the time and only when people want to insert or delete a row it be unlocked and be locked again after inserting or deleting whit no need to insert password in this case only.
Workbook Password Protect 2 Levels. I have managed to write simple but effective code that allows only one password to be enetered to open a SS & offers 3 levels of protection, limited permission to enter data, read-only & data completely hidden. This is excellent for my needs however I want the user who has limited rw to be able to insert a pic file which is their signature but because the sheet has protected cells it won't allow user to insert picture jpg file.
I have just thought of a creative solution tho. What if the picture file is already saved in the workbook and by using code to ask to insert pic file it copies from one location in SS to the appropriate cell in the worksheets?
The scenario is that I have fortnightly timesheets in 12 calendar months in 12 separate sheets. When staff member fills in timesheet I want them to insert signature for that f/night indicating thatthey have filled it in & it is correct but because of protection it won't allow me to.
In sheet1 (SA Awards) I have the source table for my pivot table in Range ("A1:G50"). In sheet2 (Team Listing) my pivot table is located in Range("K2:S13") When I make changes in sheet1 I need my pivot table to update, I recorded a macro to refresh, however have only got it to work via a button & only if Sheet2 is unlocked
Sub PivotTableUpdate() Sheets("Team Listing").Select ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh Sheets("SA Awards").Select Range("B2").Select End Sub
1. How can I get this to work in the Worksheet_Change Event? 2. How can I password protect Sheet2 & still have it work?
I have created a table where the first 6 columns are data entry and the next 6 columns are formulas to give desired results.
I have made all the data entry cells unlocked and the rest of the spreadsheet locked (cells with formulas etc.)
I would like to be able to insert rows to the protect sheet which I have managed to achieve but when I insert the row the formulas do not update in the row.
I need a VBA code for a worksheet to automatically insert cut paste a row from a range of unprotected rows to the range of protected rows once the user decides to do so.
I have a sheet called "Customer Stock" which has two tables called "Uncollected" and "Collected".
I need "Uncollected" table to be unprotected and "Collected" Table to be protected.
Once the a Customer collects the goods, the user can cut and insert the collected row from the unprotected "uncollected" table to just above the first row of the protected "collected" table,
so that the inserted cut row now should be protected.
The password should be 1234 and the user won't know the password. Only the administrator will be having it.
I have a protected worksheet, which I have 2 macros, 1 to spell check and 1 to insert rows, they both unprotect the sheet and re protect it again once they have completed. The problem I am having is that when I protect the work sheet first time round I tick the box to allow users to insert rows, once the Macros run they disable this functionality. Is there anyway I can include this in my Macros or do I need to add a new button!
I have a worksheet that I have some "locked" cells on. I also have protected the worksheet but when I check to allow users to insert rows and to delete rows and then save it, when I re-open it I can't insert rows or delete rows???
I am using some VBA to allow for the collapseing of rows while maintaining protection and that works perfectly. I want users to be able to insert and delete rows while maintaining protection. I thought a simple check in the protection was good enough, but for some reason it isn't.
I am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.
My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?
I have A TABLE (Named Table1) and some columns filled by formula. (Like E,F,G,H columns) When I protect the worksheet E,F,G,H columns are locked, but A,B,C,D columns are UNLOCKED. When is sheet PROTECTED , If I add a row bottom of Table1, does NOT automatically fill the E,F,G,H columns and Table1 range does not expanding. But, if UNPROTECTED, it's OK. when add a row bottom of Table1, automatically fill all columns with formula and table range is expanding aotomatically with new row.
When sheet is PROTECTED , How can I allow, to add a row and aouto fill all columns like unprotected.
And I want to protect sheet in any case, after allowing to add row.
I've referenced a few threads this one in particular [Solved] Pivot Tables : Refresh on protected sheet
I've tried the example given in the link and it still does not work. I want to protect the sheets that contain pivot tables, but still be able to refresh them. When I try I get an error. I've tried this a few ways.
My first attempt was to protect the sheets using allowuserinterfaceonly at a workbook open event. This caused warnings as soon as the workbook was opened. Then I tried Dave's suggestion and I get a runtime error 1004: Cannot edit PivotTable on protected sheet.
I have spent ages creating a work diary and have protected the sheets using VBA however on finishing the sheet one important thing that needs to happen is the ability to copy and paste information from the boxes to another worksheet. i.e MONDAY - SUNDAY
Does anyone know if there is a way to unprotect a sheet. The person who created this spreadsheet no longer works for our company and although we can see the spreadsheet, the formulas are hidden so we can't figure out where the data is being pulled in from. Any ideas?
I have a table of data 300 rows long with protected formulas in adjacent columns and at the bottom of the column. Some of the data cells are blank but the non-blank cells are contiguous and start at the top of the range (see Before Example).
a macro that would allow users to insert a blank cell at any point in the data table without losing any of the other data or disrupting the formulas. In other words I want to move all the data from that point to the bottom of the range down by 1 row. A before and after example for a smaller 10 row data set is shown below.
Before
AB1data aformula 12data bformula 23data cformula 34data dformula 45data eformula 56data fformula 67 formula 78 formula 89 formula 910 formula 1011formula Aformula B
After
AB14data aformula 115data bformula 216data cformula 317data dformula 418data eformula 519 formula 620data fformula 721 formula 822 formula 923 formula 1024formula Aformula B
I have a workbook whose worksheets use protection (don't want my calculations stomped on).
When the Protection is on, the search function does not work. You call it up, write what you are looking for, it accepts it, and then when you tell it to search it ignores you.
When you turn the protection off, the search function works just fine.
HOWEVER....Other workbooks I have with protection on its sheets do not share this problem, just this one workbook. And the problem is on all sheets in this workbook. The problem is not on any of the sheets in the other workbooks. I can't see anything different between them, but then I may not know what to look at.
Q1. How can we insert new rows in a protected sheet?
For example: In the attached sample protected excel sheet "insert rows.xlsx" , if for Employee ID and Name fields, the user has more no. of records than provided, how can he insert new rows considering the fact that Company ID and Name field are locked.
Or any way to address this issue via some other method like adding a scrollbar,etc.
I've locked certain columns on my worksheet so that users cannot overtype target dates etc. I've password protected the worksheet.
The password protection means that for some reason the users can't use the autofilters that are on the header row.
Can someone help me solve this problem; I still need the locked cells and password protection on the sheet but the success of the sheet depends on users being able to filter for specific rows using the autofilter....
When I protect my sheet and have the option to allow the users to autofilter the sheet, how come it doesn't work? I want to allow people to autofilter the data in the worksheet but not modify the contents of the cell.
I have a question relating to the use of autofilter in a protected sheet.
My code is working fine in setting the autofilter and locking down the sheet but a user then loses the ability to use the (Data - Filter - Show All) menu button.
Is there a way to have the worksheet protected, allow the use of the auto filters, and allow the use of the show all button?
I am looking for VBA code that will unprotect the Workbook, prompt the user for the number of rows to be added, find the last row in the Worksheet, insert the requested number and reprotect the Worksheet. I would like the location where the rows are inserted to NOT be dependent on the "active" cell.
I've been working on a macro that makes copies of a template sheet based on a table in my Opps sheet. If column B isn't empty, make a copy of the template sheet, rename it to Opps column A, and then hyperlink column A's current A.row to the newly copied and renamed sheet.
I'm not sure what is wrong exactly, it keeps making duplicate Template(x) and stops renaming them, and the hyperlinks are not working. -This is my first go at VBA hyperlinks to internal workbook sheets
I have a protected sheet to avoid delete of functions. In a specific area, however, i have merged cells of five columns in rows. Because in these lines i have write some text and overcomes the rows, after, what i do is: i unmerge the rows and choose 3 rows from 5 columns together and merge. Thus, the text is in one framework. In fact, every time, i want to merge different regions, but always i want to merged any areas who have marked with the mouse. Then, i delete the text and i need to redo the context of three lines, three separate lines. I hope you understand what i need.MergeAndUnMerge.xlsm
I want to allow user copy(Ctrl + C) and paste(Ctrl + V) a row. because some columns are locked and the sheet is protected. so, when user doing so, a warning dialog is show and can't do the paste.
so, I want to make it available in code. what I want to do is add some codes in this function:
VB: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Application.CutCopyMode = True Then 'there are someing in clipboard Sheet1.Unprotect 'unprotect sheet, so can paste End If
and in Worksheet_Change function, I protect the sheet again.
But I find that when I do Sheet1.Unprotect , the clipboard is empty! I just want to allow user can copy and paste a row, the locked cell's value can be ignored.
We are needing to do a two tier sort in a protected spreadsheet. Is this possible?
The two columns that need to be sorted have protected cells between them.
If there a way to ad a button that will automatically do this sort for the user... as in can I add a button some how in one of the empty cells that will do it?
Here is the sheet. [URL] ..........
Looks like I need to sort column A thru Z, except W.
