Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



How To Hide Rows In A Protected Sheet

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

1. How to hide rows in a protected worksheet without unprotecting the
worksheet.
2. Is it possible to delete rows in a protected worksheet without
unprotecting the worksheet.
3. My requirements is that in a worksheet, I have input certain formulas
and the same is protected with a password, I will be sending these sheets to
my colleagues to fill in data and the formulas will automatically calculate
the results. I dont want them to modify the protected ranges.

This protection will be for certain columns for the entire worksheet. In
case if they print then there are totals at the end of the sheet which also
has to be printed but I dont want empty rows without data not to be printed.

My querry is that is it possible to hide or delete empty rows in a protected
sheet without unprotecting the same.

4. These sheets will be used regularly as templates is it possible for them
to store the same in one file with different worksheet. In other words they
will copy and paste the template in the same file in different sheet but I
need the protection to continue is it possible.

5. Is it possible for someone to unprotect the sheet without the owner
disclosing the password. In other words is it possible to break the password.

Expecting your immediate response on the same.

regards

Prakash


View Answers     

Similar Excel Tutorials

Hide or Unhide a Worksheet by Hand in Excel
I'll show you how to hide worksheets in Excel so that a user cannot see them but you can still access data on them ...
Hide Data Within a Worksheet in Excel
In Excel you can actually hide data that is stored within a worksheet. This allows you to show data that is useful ...
Allow Only Certain People to Edit Specific Cells in Excel
How to allow only certain people to edit certain cells or ranges in Excel. This is a security feature that you can ...
Prevent a User from Changing Any Data in Excel
I'll show you how to lock a spreadsheet so that nothing can be changed in it. This is a great feature to use when ...
Hide the Excel Interface and Show Only Data
I'm going to show you how to remove almost every interface from Excel and streamline the program so that the data ...
Print Comments in Excel
How to print the comments in a worksheet when you print from Excel. This includes how to print the comments within ...

Helpful Excel Macros

Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Protect and Unprotect All Worksheets at Once
- This will allow you to protect or unprotct all worksheets in a workbook at once. It doesn't allow you to input a passwor
Delete Hidden Rows in a Workbook
- This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics







Here's my dilemma;

I have a workbook which has a hidden worksheet where a password resides. My macro goes to this worksheet, collects the password and comes back to the current worksheet, unprotects it (with that password), imports data, and then reprotects the worksheet. Because the workbook is also protected, the sheet with the hidden password cannot be accessed.

Now I want the workbook to be "shared and protected" with "Track Changes". This will prevent the macro itself from being tampered with and will enable an audit trail of sorts on a 3rd worksheet within the workbook.

However, if the workbook is shared and protected, one needs to unshare and unprotect it first before unprotecting the worksheet that I need to import into. Sooooooooo,

I need to figure out how to (in VB) have the macro first unprotect and unshare the workbook (using the hidden password), then unprotect the worksheet (got that part figured out already), then reprotect the worksheet and the shared workbook. Any ideas on how I would do that?

I can send the current macro to those who wish to investigate.

Is there another way to protect the macros from being edited by someone with rights to the workbook?

Thanks in advance
ChemistB


The worksheet is already passord protected. I have assorted macros created to unlock the sheet with the password (without any user interface), perform a procedure and then reprotect the sheet. On the payroll portion of the worksheet, if the rows have been hidden, what is the code to require the user to provide a password that will unhide those rows (without unprotecting the entire sheet)?


Hi.

I have a workbook that has several protected sheets, several password protected sheet, and several non-protected sheets.

I've looked all over, and cannot seem to find code that tests for the password protection status of a sheet (i.e. not just protected but password protected).

I've found a lot of information on the Workbook HasPassword property, but nothing at Worksheets level.

Is there such a code for this test, and/or is there another way of finding out?

Thanks in advance.

Mark.

My workbook has a protected worksheet (including mostly locked cells); but I want to allow users to copy the worksheet (either by the worksheet "Move or Copy" function, or by clicking the upper-left corner to select all cells) to paste it into their workbooks (which I want no part of).

Is there a parameter I can set in the SheetX.Protect function to allow this flexibility?

The other options I can think of a
* unlock all cells to allow selecting and copying the entire worksheet;
* unprotecting the sheet for those specific users only (by VBA);
* giving those users the password;
* just unprotecting the sheet, period.


I need assistance developing vba code to unprotect a protected worksheet. Most tutorials concerning vba script and unprotecting sheets only refers to unprotecting an active sheet. I would like to have the vba script unprotect a specific sheet if possible. Please advise.

Ultimate Goal:
To prevent users of the worksheet from being able to input information into the raw data other than using the userform I've created.

The userform inputs information into the a sheet entitled "Details" and I'd like to have the "Details" sheet protected at all times. I would like the userform to be able to unprotect the "Details" sheet, input the data and protect the sheet when done.


I have a sheet that upon opening a macro runs to hide a selection of columns based on cell containing certain words (see here).

I now also require the workbook to be protected so that the hidden columns (which Im sure are locked as default when hidden) are locked as are columns N & S. Now, I know I can do this by protecting the sheet and unprotecting the columns I require (ie N & S - with the hidden cells remaining locked anyway).

The issue is, every time the workbook is opened this protection must be in place.

However, I require a button, which could simply reside in A1, that when pressed, will unhide and unlocked all cells. - This is needed as its vital I can easily copy rows to another sheet (and delete from existing sheet) as and when required.

if possible, re-pressing the button would hide and proetct the worksheet however this is not too significant as long as when I close and open the workbook everything is hidden and protected as specified above.

There is no need to password protect anything.


Ive had a good search of the forums and have little bits of info from varuious threads. Any help would, as ever, be much appreciated.


Oh and the sheet in question is called 'Open Projects'.


Please help me.

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....

Awaiting any responses.....


Hi all,

I have this excel files protected by password and I need to unprotect it so
I go to Tools -> Protection -> Unprotect Sheet to unprotect my worksheet.
The password was right and the process went on correctly without any error
or problem.

but the next time I open the same file again, I was prompted to key in
password again. and I had to use the same password to go in.

When I check on the Tools -> Protection. The unprotect sheet change to
Protect sheet. which means by right it should be an unprotected file. but
why I was prompt for password ?

I suspected it is due to corrupted file content. but how to fix it ? or it
is something else.

Any help is very much appreciated.

Thanks and Regards,

Bernard Goh
Success Electronics & Transformer Manufacturer Sdn Bhd





Morning All,

I have some formulas which are protected, so Ive put a password on the sheet. Problem is that I want the user to be able to delete rows... when I try and delete a row with the protection on I get the following error.

You are trying to delete a row that contains a locked cell. Locked cells cannot be deleted while the worksheet is protected.

Is there any code I can use to get around this? My protected columns are A and some of D.

Thanks,
Mandy


Using Custom Views in a protected worksheet, the column hiding/unhiding implicit in the views does not occur. The following error message is displayed:
Some view settings could not be applied In the Add View dialog box, I've checked the "Hidden rows, columns and filter settings" option.

In the protection dialog box, I've checked the checkbox for "Format Columns", which I believe indicates that hiding/unhiding columns is permitted when the sheet is protected. And, when the sheet is protected, I can manually hide and unhide columns, but I want that to happen automatically when I select the custom view.

If I unprotect the worksheet, column hiding/unhiding occurs automatically when I select the custom view.

Can I write code to make the column hiding and unhiding occur when the view is selected, perhaps by unprotecting the sheet temporarily and then protecting it again once the selected view has been displayed?


We have a large (4,000+ rows) excel worksheet from a prior employee that I
would like to be able to use, but it is password protected and no one has the
password. Is there a way to copy or save it as a different file that will
not copy the password protection? It would save me a couple days of work if
it is possible.
Thanks



I have a worksheet that has many cells/formulas protected so that the data
stays accurate. I left certain cells unprotected for others to access and
fill in their information. The problem is that when trying to send the
worksheet as the message body only, not an attachment, an error pops up
saying--

"A sheet in the workbook you are trying to save is password protected.
Password protected sheets cannot be saved in a Web Page. To save this file
as a Web Page, you must first remove the password."

I would prefer not to have to send the workbook as an attachment, but am at
a loss for how to keep certain cells protected and be able to send as the
message body. Any suggestions?


Thank you--
Bambi



I use Excel 2000. I have workbooks with many sheets that
are protected against changes to cells (data, formulas,
formatting, etc.).

Question 1: Is there a way to quickly determine if a sheet
and/or cell are protected or not without doing an input
test or checking out the Tools, Protection command?
Something like the PR or U codes Lotus places at the
bottom of the window.

Question 2: Is there a way to unprotect, and vice versa, a
number of sheets with one command? I tried selecting all
the sheets and using the Tools, Protection/Unprotect
command with no luck.

Search and Replace is not available when the sheets are
protected.

Thanks,

Maureen



I have column data that is grouped. When I apply protection to the worksheet
it is not possible to show hidden columns or hide displayed columns.
I want users to be able to do this so that they can adjust the detail that
is printed for their own purposes but I want the content of the sheet
protected.
Is there any way to do this?



Hi all,

I have a generated a XML through programming wih different worksheets with protection,

I want to make it password protected, when i try to open XML in excel it is giving error if it is password proteted, normal protection works fine.

when i try to save password protected excel in XML also it not allowing me to save..is there is any way to do this..??

Regards
Virkly


I have a workbook with 2 sheets. Both sheets are password protected. I saved the workbook such that cell A1 of the left-most sheet is the active, or selected, cell. Meaning, when you open the workbook, that is where the cursor is.

When I click the tab for the 2nd sheet (right-most), the following error pops up with just an "Okay" button.

The cell or chart 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 (Tools menu, Protection submenu). You may be prompted for a password.

Upon clicking "Okay", the same error pops up again, and again - 5 times total.

I do not understand why this is happening, as all I am doing is selecting the sheet. I'm not trying to change any cell contents at all.

I'm sure this has something to do with the "locked" status of cells on the sheet, and the fact that the sheet is password protected - I just can't figure it out what!

Any help will be much appreciated.


I have a protected worksheet showing results of users maths tests. When users try to add data to this worksheet, a Windows Office window appears, advising it is a protected sheet, then gives instructions on how to Unprotect it.

I do not want users unprotecting this particular worksheet.

Can anyone please advise a VBA routine tro prevent this popup appearing?


I need to allow delete rows to be done on rows that contain locked cells in a protected worksheet.
I know I can unprotect the sheet unlock cells, allow the delete and protect the sheet again but how do I identify that the worksheet user is trying delet a row in vba ?

Thanks




hi guys,

Basically, I have an Excel 2003 file with some VBA code which I want protected. I used the VBAProjectProperties Dialog to set a password restricting viewing of the code. However, if the file is open in LibreOffice Calc then the protected VBA code is perfectly visible.

Some details: I have protected the workbook structure with a password. However, in the VBA code I hide and show certain sheets as appropriate, using the Visible property of the sheet. It cannot be edited though unless the workbook is unprotected. So for this reason before making the necessary changes I have to unprotect the workbook, make the changes and then protect the workbook again. To do so I have the password written in the VBA code.

Is there any way to prevent seeing the protected VBA code in Libre Office Calc?

I am creating a macro is supposed to password protect a worksheet and then hide it.

I recorded the macro including entering a password. When I went to test the macro, it protected the sheet without setting a password at all.

I dont care if I have to manually enter the password or if it uses the password that i put in the macro, but at this point it doesnt password protect at all.

Anyone have any thoughs on how to correct this?


I have a worksheet that is password protected. Various users will use this worksheet without the need to ever know the password.
however, It is also used by a couple of their managers who will need access to unlock.
Is there a way to have a macro to prompt for the password and verify it to the real password. (rather than have these managers go to the Tools, Protection, Unprotect Worksheet).

I already have something in place that on exit it automatically password protects it with the password of choice.


Hi,

I have an Excel Workbook having a protected worksheet. I want to allow users to Sort the Worksheet and at the same time ensure that they are not able to edit (chnage) data excpet sorting which is allowed. I tried using the Tools>Protect Sheet by checking the checkbox for "Sort". I protected the sheet after that.

After that I thought I would be able to sort the data However i get the error pop up saying The worksheet is Read only and protected, the user needs to unprotect it for making any changes to it.

My worksheet data has 250 columns and 1500 rows. I want to keep an option open to sort it with reference to any one of it.

Can someone help?

awagdarikar


Any suggestions?
I am needing to turn off the PW protection on a worksheet before I do a copy.
I have the same code for other sheets and it works fine. For some reason on this sheet
if I have it pass protected with "mypw" and run the macro below I get a runtime error of 1004 (password supplied is incorrect). I can go into Tools, Protection, Unprotect sheet and type in the same PW without any problems. I can change the unprotect to Protect and it will set it, I just cant remove it when I do my copy

????


Sub test1()
Turn off PW on Sales Sheet
ActiveSheet.Unprotect Password:=mypw
End Sub




Hello everyone !!

It is my first post and I must say that it is a great forum with lot of useful information.

I am working on a spreasheet which is password protected. It has two section one is protected other is open.

Users are allowed to insert rows in protected area however they cannot copy and insert due to protection. So I have worked and developed a macro which allows them to do so. The only question I have is how to allow them to insert the copied cell to where ever they want.

Ideally I would like them to highlight a row and it insert the copied row there or may be input the row number (like Row 24) below which to insert the copied Row.

I have the below code at the moment to copy and insert and it only insert in the next blank row. I am new to VB and I think the below code can also be improved a bit.

Thanks for any help/suggestion.



Please Login or Register  to view this content.



I designed a calculator for a client and protected the sheet so that employees could use certain cells in the sheet but could not see or access the cells containing the calculations and formulas. On the calculator I placed a couple of combo boxes containing valid choices that the employee can choose from. When I protect the sheet, the combo boxes will not let the user choose a value - the error that comes up:

"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 I right click the control and look at the Protection tab in Format Control, the 'locked' check box is not checked.

Also, when I go to protect the sheet, I've actually clicked on every checkbox to give the user way more functionality that I intend (for testing purposes) and I still get the error.

Is this a bug - is there a fix or workaround?