Allow Adding New Row To A Table When Worksheet Protected
Feb 10, 2014
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 need a macro code to do this.
View 5 Replies
ADVERTISEMENT
Jan 20, 2010
I have (several) worksheets that have protection enabled. I have unlocked all the cells that users need access to and locked all the column and row headers/labels.
When I enabled protection on the sheets, the ability to add comments was taken away.
Is there a way to add comments to an UNLOCKED cell in a PROTECTED worksheet?
View 5 Replies
View Related
Mar 19, 2012
I was hoping to force end users to use a user form to input data into a work sheet. The problem is that the form wont input the data when the sheet is protected.
View 2 Replies
View Related
Mar 16, 2012
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
View 9 Replies
View Related
Feb 11, 2014
I'm trying to manage my Stock by using protected sheet, but i stuck with this "row adding with formula" stuff.
The user can only write in the colored cell, but everytime the user add new row, the formula doesn't copying by itself
I attach the file below with no password, if it's protected, the password is none, just press enter ( password : "" )
Book2.xls
View 5 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
Dec 11, 2013
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.
View 1 Replies
View Related
May 31, 2006
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.
View 4 Replies
View Related
Jan 5, 2013
I have this workbook to keep track of current and new work for the team - each individual inputs information on their own tab.
In turn, the current information is displayed on a summary page so we can all see who's doing what.
I want to protect that summary page and its formulas from accidental amendment.
However, while the summary page (when unprotected) updates as soon as someone enters new information on their own tab, the summary page won't update when I have it protected.
Is there any way of doing this - preferably without VBA as it's a work situation and the employer doesn't like VBA code running?
View 5 Replies
View Related
Mar 25, 2014
I want to run a macro in a worksheet which is proteced.
It's a simple macro which erases the content in (some) cells, nothing more. Even those celles are formatted as unlocked the macro fails.
View 4 Replies
View Related
May 20, 2012
I have the following code which works fine when Sheet1 is unprotected (The code is placed in Sheet3)
however if I protect sheet1 then the code does not work
I have tried it with
Code:
ActiveSheet.Unprotect
Code:
ActiveSheet.Protect
it still will not work on Sheet1
here is the code
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
On Error GoTo Handler
With Sheet1.Range("B6:B10045")
[Code] ....
View 2 Replies
View Related
Dec 12, 2007
I have a protected worksheet where I allow all users of the worksheet to filter yet when I filter, Excel gives a run time error 1004 - you cannot use this command in a protected worksheet. Could someone let me know what am I doing wrong?
View 9 Replies
View Related
Nov 16, 2006
I've built a workbook using Excel 2000 that uses several combo boxes. When I try to protect the workbook the combo boxes become locked, even when I have done FORMAT CONTROL / PROTECTION and unticked the LOCKED box. Is this normal?. I also have data validation cells and they work fine if I unlock them and Protect the workbook. As a result, I am thinking of converting the combo boxes to data validation cells, but should I even need to do this
View 3 Replies
View Related
Aug 16, 2007
I have the following macro and when it "reprotects" my worksheet I need it to also turn on these Protect Sheet properties:
Format cells
Format columns
Format rows
Insert hyperlinks
and I need it to leave on the defaults of Select locked cells and select unlocked cells
Sub Spell_Check()
ActiveSheet.Unprotect Password:="pmo"
Cells.CheckSpelling "SRdictionary.dic", SpellLang:=1033
ActiveSheet.Protect Password:="pmo", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub
View 4 Replies
View Related
Aug 12, 2008
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?
View 3 Replies
View Related
Dec 15, 2008
I have a data list which needs to be updated by others on a shared drive. I want to protect the worksheet as there is other info on it (advancefilter from the main list). The problem is that the >>Data>>Form tool will open, but all the boxes necessary to update the list are greyed out (New, Delete etc.)
My other option is to create a macro to advance filter out the other data to another worksheet.
View 3 Replies
View Related
Sep 13, 2013
What if you forgot the password in a protected sheet? is there a way to unlock it?
View 1 Replies
View Related
Oct 13, 2013
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.
View 3 Replies
View Related
Jul 21, 2014
I was able to create a macro that allows a user to unhide rows in a protected worksheet without unprotecting the other locked fields (see below). My question is: I want to add to this command so that 1 row can not be "unhidden" if the previous row is still hidden. For example, row 25 can not be unhidden if row 24 is still hidden.
Macro command used:
Private Sub Commandbutton4_Click()
Sub Hide_Rows2h()
ActiveSheet.Unprotect Password:="xxx"
Rows("25").Hidden = Not Rows("25").Hidden
ActiveSheet.Protect Password:="xxx"
View 3 Replies
View Related
Nov 1, 2013
I have the following VBA in my spreadsheet.
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveSheet.Unprotect ("kellyz")
If Intersect(Target, Columns("o:o")) Is Nothing Then Exit Sub
For Each C In Intersect(Target, Columns("O:O"))
[Code] ......
As soon as i type anything in the columns, my sheet is unprotected. I need the sheet protected so that the users can not change my formulas.
View 2 Replies
View Related
Feb 13, 2009
I have a protected worksheet that from time to time I need to insert a new row and copy a specific range of cells that are with formulae (protected) to the newly inserted row. For a better idea of what I want to achieve, a snapshot of the worksheet is attached.
******** language="JavaScript" ************************************************************************>Microsoft Excel - ACR-INFRA.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutI23K23M23F24H24I24J24K24L24M24N24F25H25I25J25K25L25M25N25H26I26J26K26L26M26N26D28E28F28G28H28I28J28K28L28M28N28D29I29=
ABCDEFGHIJKLMNO22CR NoCodeStatusEstimateContr's PriceDeltaAgreed ValueCommitment Impact Impact ImpactFunded from23 2,730,382.00 320,000.00 180,000.00 241380AIA180,000.00
180,000.00 2,910,382.00 (180,000.00)140,000.00 0.00 180,000.00 0.00 252482AII
0.00 2,910,382.00 0.00 140,000.00 0.00 180,000.00 0.00 26 0.00 2,910,382.00 0.00 140,000.00 0.00 180,000.00 0.00 27 28 180,000.00 0.00 0.00 0.00 180,000.00 2,910,382.00 (180,000.00)140,000.00 0.00 180,000.00 0.00 29 A0.00 2,910,382.00 1036
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I would like to insert the new row at row27 downwards each time the macro runs.
View 9 Replies
View Related
May 18, 2009
I have incorporated print/print-preview command buttons/VBA into a workbook with protected sheets. To enable these command buttons to function when the relevant sheet is protected, I have had to add VBA code to unprotect the sheet before generating the print preview, and then to protect it again afterwards. However, the code I have used (see below) prompts the user to enter the protection password, is there any code I can use where I can write the password into the code itself to unprotect the worksheet without the using having to enter the password?
'Unprotect Sheet
ActiveSheet.Unprotect
'Print preview & cell formatting code
'Protect Sheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True
View 2 Replies
View Related
May 22, 2006
I am being asked to create a workbook where 80% of it is protected. And user input is restricted to specific columns and rows.
However the user wants column R unprotected so that Comment boxes can be used, but when I protect the workbooksheet the Insert option is not available when you right click.
View 9 Replies
View Related
Jul 25, 2006
I curently using the group and outline to subtotal certain data. I do not want to data to be modified. As a result I protect the particular sheet. Problem is once I protect the worksheet, I can't expand the data hidden (using the group & outline). Is there anyway that i can expand the data & in the same time protect the
data. A sample in enclose(without worksheet protected)
View 6 Replies
View Related
Jun 8, 2008
I'm not fond with macros, but this is what i need help with.
I need two macros one to sort ascending (Ctrl+A), and one to sort descending (Ctrl+D).
This is what the macro to do. Unprotect, sort selected column, protect.
View 7 Replies
View Related
Mar 5, 2013
I have a worksheet which is used to display analysis data to the user. The sheet will be further protected with only some cells available to the user. I've used the attached code to retrieve some data from the sheet, clear a bunch of cells to reset the sheet and reset a graph (the graph itself is plotted as a log-log with the 1 to 100 on the Y-axis, hence the resetting of data to 0.5 to push it below the axis and make it invisible ready for later data dumping).
The code worked fine until I protected the worksheet. I have set the charts to unlocked using the format option in the chart area prior to locking. When I run the code though, it stops on the line indicated with the error "Application-defined or object-defined error". I've double checked and Chart 7 is the correct chart, and it is unlocked according to its format properties.
VB:
'get date of survey and equipment number for retrieving the data from the archive
dateSurvey = Worksheets("Calculation Page").Range("B2").Value
equipNum = Worksheets("Calculation Page").Range("F2").Value
'stop screen updating
Application.ScreenUpdating = False
[Code]....
EDIT: Oops, I've just noticed that even if I unprotect the sheet, I get an error on the .select within the seriescollection stating "Method 'Select' of Object 'Series' Failed", and the code worked perfectly before. I'm completely lost now...
View 9 Replies
View Related
Jun 19, 2014
I got a worksheet here. I'd like to lock all the cell height and width using protected sheets function. I realized from time to time I have the need to hide them. How do I enable hiding sheets while maintaining cell integrity?
View 3 Replies
View Related
Oct 3, 2009
I want to hide a command button when a worksheet is protected and unhide when the sheet is unprotected.
View 14 Replies
View Related
Feb 15, 2012
I'am trying to make a button that will let me send a email via outlook. trick is work book is password protected but need to send some of the data contained to perssonel that dont have the password so sending teh complete work book doesnt work just need something simple. I have learned all ( little) i know from reading and searching here.
no links to other sites as my firewall here at work will possibly reject it
View 9 Replies
View Related
Apr 1, 2014
Is there any method to disable notification / error message of password protected sheet when we try to edit restricted cells?
Note: In my scenario I've to allow user to select protected cell; that's why cell selection redirection is not useful for my.
View 7 Replies
View Related