Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Activex Button To Hide/unhide Rows In Protected Worksheet

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

Hi all,

I've got a worksheet that is bilingual. I input all cells in the worksheet once, did a translation and input all that translated info into a second set of rows. The first column includes a reference, either "1" or "2", depending on whether the row uses the original or the translation. Finally, I inserted a activeX control button linked to a macro that identifies the reference in column 1, hides all the rows with a 1 and unhides all the rows with a 2 or vice versa.

I'd now like to hide the first column from the user. But when I do this, the hide/unhide function freezes. I've seen a number of solutions proposed. I prefer not to use this option since errors in the code could stop the vb while the worksheet is still unprotected:

ActiveSheet.Protect Password:="password"

[Other code here]

ActiveSheet.Unprotect Password:="password"
Internet blogs suggest that if you check the option allowing users to format rows and columns when protecting the worksheet, it should allow users to hide unhide. I think this wouldn't solve my problem since I need to prevent a column from getting unhidden but allow rows to get hidden/unhidden [which includes a bit of the protect column]. But I tried it, and even with both options checked, protecting the worksheet blocks the translation macro. This is why I've not even bothered to try programmatically using the "Allowformattingrows" property of the protection object.

I found this proposed solution:

ActiveSheet.Protect Password:="password", UserInterfaceonly:=True
I tried putting this bit of code into sub procedure [e.g., Protect()] and module of its own. I then tried calling that procedure from the translation sub procedure with something like:

Sub Translation()

Call Protect

[Other code for hiding/unhiding rows]

End Sub
That seemed to work when I run the sub procedure from the vb editor. But when I try it from the activeX control button on the worksheet, it doesn't work!

I could use people's thoughts or suggestions to solve this. Thanks.

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 ...
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 protect ...
Easy Way to Manage Names in Excel
How to manage Names in Excel so they are easy to view and change as needed. This is a little trick that I use to k ...

Helpful Excel Macros

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
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
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Hide Comments in Excel Completely - Even Indicators Will not Appear
- Hide all comments in an Excel workbook. No indicators will be displayed and comments will not appear when you hover ove
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

Similar Topics

I am stuck! I have a worksheet that is protected. However, I have an ActiveX control button that I want users to be able to use. I have tried building in the unprotect functionality into the ActiveX control as follows:

Sub PrefSelection()
' PrefSelection Macro

Sheet13.Unprotect Password:="password"
(my code)
Sheet13.Protect Password:="abqaiq1!"
End Sub

But no luck. I have also tried building it into ThisWorksheet as a start event as follows:

Private Sub Workbook_Open()
Sheets(13).Protect Password:="password", _
End Sub

I have a Spash Screen thus the "Welcome.Show" coding. Unfortunately this does not work either. The macro appears to work but I still get the error indicating that the spreadsheet is protected when I try and run the ActiveX control.

I'm considering putting in a control box to unlock the sheet that the user has to push before making an ActiveX selection. It is really sloppy though.

Any ideas??



I have this code which allows me to Hide the formulas but i have a macro that needs to be run but this wont allow me to run the macro if the sheet is protected. What i wanted to do is have the sheet protected and password and formulas hidden the way it is but also have the option to enable me to run the macro without having to unhide the sheet/unprotect password etc.

Another thing i would like to do is have some sheets hidden (Not all the sheets but the the ones i want to hide) but also have them password protected by VBA so that its not a case where users can right click and Unhide sheet tabs but need the password to unhide

I am new to VBA.

Your help is much appreciated

Sub LockDown()
Dim WS As Worksheet
For Each WS In ThisWorkbook. WorkSheets
WS.Cells.FormulaHidden = True
WS.Protect Contents:=True, Password:="terces", UserInterfaceOnly:=True
ThisWorkbook.Protect Password:="drowssap", structu =True.
End Sub

I have a workbook with 3 worksheets named: Entry, Reader, and Data.

I want Data password-protected & hidden at all times. For the sake of this discussion, let's say that the password is "protect".

Now, I have a macro button on the Entry sheet, that when clicked, transfers data from the Entry sheet to the Data sheet, and then resets associated fields on the Entry sheet.

My problem is the hide/unhide and password-protected/unprotect parts, and ensuring that the end user never sees the Data tab at all.

My macro works just fine as long as I am 1) not hiding Data and 2) protecting Data without using a password. I just cannot figure out the correct code to both 1) hide/unhide Data and 2) password-protect/unprotect Data using the password "protect".

Side question - Do I even have to unhide Data in order to transfer data to it?


For what it may be worth, this is the code I use at the beginning of the macro:

Application.ScreenUpdating = False

and at the end of the macro:

Application.ScreenUpdating = True


I have a spreadsheet that contains a mixture of sensitive and non-sensitive data.

I would like to give authorised users complete access to the full spreadsheet and while unauthorised users can view all columns apart from column H.

The simplest way of doing this is to hide column H and use "Tools/Protection" to password protect the spreadsheet. However, this means that if authorised users want to write in column H they have to:
unprotect the worksheet;
unhide column H;
write in column H;
hide column H; and
protect the worksheet.

This is a rather cumbersome process, and I do not want to rely on authorised users remembering to protect the worksheet once they are finished.

Is there a way of automatically giving different access to different users depending on whether they know a password?

FYI - I am using Excel 2002.


John John

I am working on a timesheet that will allow the user to input data, but not change certain cells. I have two buttons built into the worksheet: one to delete a row and one to insert a row. The buttons delete and insert in relation to the location of the active cell.

My problem: I am getting 1004 errors, as well as others at other times.

Since the line I want to insert involves specific formatting and merging, I thought the easiest way to go about this would be to make a "line master" worksheet and copy the line from the line master and insert it into the input sheet.

Insert Button:
1. User clicks a cell in the row on ws."Input" where they want the new row to appear.
2. Unprotect (with password) ws."Line Master" and ws."Input"
3. Unhide ws."Line Master"
4. Copy Row 1:1 from ws."Line Master"
5. Go back to ws."Input" and insert the copied row at the location of the ActiveCell from Step 1
6. Hide ws."Line Master"
7. Protect (with password) ws."Line Master" and ws."Input"

My code: (I was having problems, so I stripped the protect/unprotect/hide/unhide lines from the code and went back to basics.)

Sub InsertRow()
    Sheets("Line Master").Select
Rows(ActiveCell.Row).Insert Shift:=xlDown

Delete Button:
1. User clicks a cell in the row on ws."Input" they want to delete.
2. Unprotect (with password) ws."Input"
3. Delete the row.
4. Protect (with password) ws."Input"

The delete code I'm currently using, thanks to : (Except for the lack of protect/hide code, it seems to be working perfectly.)

Sub delete_row()
'Erik Van Geit
'050608 0041
'to delete rows when sheet is protected

Dim nrs As String

If Selection.Rows.Count = 1 Then
nrs = "row " & Selection.Row
nrs = "rows " & Application.WorksheetFunction.Substitute(Selection.EntireRow.Address(0, 0), ":", " to ")
End If

If MsgBox("Do you want to delete " & nrs & "?", 36, "DELETE ROWS") = vbNo Then Exit Sub
End Sub

Can someone please help? I want to be able to protect/hide but still allow the user to add/delete lines by using the buttons I've supplied (but not the menus). I can't seem to tie the protection lines in with the rest of my code and make it work.

I tried to protect worksheets with password while allowing formatting rows. The following is my code. I found I could only do " protect worksheets with password, not able to allow formatting rows" or "protect workshees and allow formatting rows, but no passwords". Can I do all three of them? The following is my macro code:

Sheets("xyz").Unprotect Password:="secret "
ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _


Can anyone help me fix my macro code here. I am trying to write a macro that uses a toggle button to unprotect/protect a sheet then hide/unhide Rows in the worksheet I am currently working with called "Project Questions" and unprotect hide/unhide rows in another worksheet call "Risk Estimate" then protect again.

In Short
With a protected workbook
Toggle Button located in "Project Questions" is ON then Rows 56 and 57 unhidden in sheet "Project Questions"
Row 21 is unhidden in "Risk Estimate"

Here is what I have written so far:

[QUOTE]Private Sub ToggleButton15_Click()
Sheets("Project Questions").Unprotect
If Sheets("Risk Estimate").Rows(21).EntireRow.Hidden = False Then
Sheets("Risk Estimate").Rows(21).EntireRow.Hidden = True
ActiveSheet.Rows("56:57").EntireRow.Hidden = True
Sheets("Risk Estimate").Rows(21).EntireRow.Hidden = False
ActiveSheet.Rows("56:57").EntireRow.Hidden = False
End If
Sheets("Risk Estimate").Protect
End Sub

Thanks for any help

Have created button that takes a series of worksheets for one month and converts the information into a new month (w/blank cells). I am trying to include in macro for sheet to protect with the password, "controL" -- I can get it to unprotect the original w/the password, and to re-protect the worksheet (w/the entry below) -- however can't get it to re-protect w/password. HELP!!??

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True


ActiveSheet.Protect Password:="hi"
ActiveSheet.Unprotect Password:="hi"

Here I assign password using VBA. It is hardcoded.

My users won't have access to vba code. Some user will unprotect worksheet and will protect worksheet using new pass. The above code wont allow that. I can't use a named range to define pass in cell because it needs to be hidden from most users.

Is there any system level global type variable that returns worksheet passwords?

ActiveSheet.Protect Password:=Worksheets("LockedSheet").SheetPass

I am assuming SheetPass stores password for the worksheet. This password was entered by user from Review>Protect Worksheet.

Is there something to do this?


I have inserted a macro button that unhides one row at a time in a specific place on a worksheet. The goal is to provide extra lines in a section of a form. The initially hidden rows span from row 40 to 53 and stop at a cell named "Total." When the button is clicked, one row is unhidden beginning with the smallest row number. So if 40 - 53 are hidden, the macro 1st unhides row 40, then 41 etc.

I now need to insert a button to HIDE one row at a time but I need it to hide the LARGEST row number in the range. For example, if rows 40 to 50 are unhidden, I need the button to hide row 50 - not row 40.

The initial Unhide macro is as follows:


Sub HideOneMoreRow()
Dim TotalRow As Long
TotalRow = Range("B:B").Find("Total").Row
For ThisRow = TotalRow - 1 To 1 Step 1
  If Rows(TotalRow).Hidden = True Then
    Rows(ThisRow + 1).Hidden = True
    Exit For
  End If
Next ThisRow
End Sub

Can you help

I was wondering if it is possible to run a macro that will unlock a password protected worksheet do its task and then protect the worksheet with a password

Test Macro
ActiveSheet.Unprotect "cbre" this is the unlocking password which works when the macro is ran for the first time but after that the password is not necessary as the macro does not re enter the password at the end of the macro
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowInsertingRows:=True, _

Thanks for the help


I am trying to password protect a range of cells on a worksheet and it won't work. I go through the motions of setting up the range, setting the password and then protecting the sheet, but it then allows editing without requiring a password. The sheet does have macros that hide and unhide some rows and columns. Will this impact the protect range function? Any Ideas?

I have a workbook with approximately 15 worksheets. One of the worksheets
has confidential information that I do not want the general user to see
unless they have the password to "uhide" the worksheet. I have a main
worksheet (visible at all times) with hyperlinks to each worksheet. I want
to piggyback a macro off the hyperlink for the confidential worksheet that
prompts a password. If the password is correct, the macro will unhide the
worksheet. If the password is not correct, an message box will appear
informing the user the password is incorrect and to try again. When closing
the workbook, I want the macro to hide the confidential worksheet.

Also...I want to protect the workbook so the user cannot manually "unhide"
the confidential worksheet from the toolbar. I want to protect the
confidential worksheet since certain cells will be locked to avoid user data

Will someone please help me with the VBA code? and what is the code to get
around the conflict of having the workbook and worksheet protected?

Thank you very much.


I'm trying to protect/hide the formulas on a w/s by protecting the w/s so that I may not accidentally delete or overwrite them.
It works fine with the exception that the Hide & Unhide column no longer available either via Format::Column::Hide and Unhide or by a macro.

The macro to hide / unhide columns produces:
Run-time error '1004':
"Unable to set the Hidden property of the Range class"
and in the macro code:
...>Selection.EntireColumn.Hidden = True
is highlighted

Is there a workaround this difficulty; namely protecting the w/s formulas and still be able to Hide / Unhide columns ??

Thank you kindly.

Just wondering if any suggestions exist for why this code, once executed, leaves the sheet not requiring a password to remove the protection:

Sub ProtectOn()

ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.Protect Password:="test"
End Sub

So the sheet ends up protected, but all a user has to do is access the Tools>Protection>Unprotect Sheet menu to take the sheet protection off - the " ActiveSheet.Protect Password:="test" " statement is not executing. I'm calling this procedure from one other code initiated procedure in Excel 2003. Any advice or guidance anyone would have would be greatly appreciated!


I have created a workbook with one worksheet. I will need to make changes to this on a regular basis, so am building code that will allow me to input a password to unprotect the worksheet and workbook to allow for changes.

I also want the button to relock the sheet when pressed a second time. Basically I need this:

If Worksheet is protected then prompt password box and if correct unlock. If worksheet is unprotected then protect with xyz password. I can do all the actions EXCEPT the first if statement. There is no property that I am aware of that allows you to evaluate the protection of a worksheet to true or false.

My thoughts were something like this:

If Worksheet("xyz") = True Then...


If ActiveWorkbook.Protect = True Then...

None of these ideas work, but no error is generated either. Any thoughts? Thanks,


Hello Everyone. I am using Excel 2007 and I cannot get my table to allow filtering when my worksheet is password protected. I recorded a macro to unprotect the worksheet, sort the table, then protect the worksheet again. My problem is that when I protect the sheet again, I lose the ability to filter the table even though the macro includes the following code when it protects the sheet again.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True

By trying different things I have found that if I password protect the sheet BEFORE the above code, filtering still works but the page is no longer password protected. It is simply protected (without a password). If I password protect the sheet after the above code, the sheet is password protected, but then I cannot filter the table.

Is there some way to allow filtering AND to have the sheet password protected?

I have a worksheet that I have data below row 11.

Rows 1 thru 11 are protected with password.

Rows 12 on down are unprotected.

The macro does a number of things and then sorts the data.

It works fine when the protection is turned off (no password)

When I enter the password, the macro will not sort and it will not sort manually either.

I need a way in the macro, before the sort code, to unprotect (using the password automatically) then allowing the macro to continue on and when finished, the password would again protect the sheet with the same password.

Does anyone know the code for doing this. I believe that I seen this some years back, but no idea on the syntax.

I have the following VBA Code:

Option Explicit

Sub ProtectAll()

Dim wSheet As Worksheet
Dim Pwd As String

Pwd = InputBox("No password is needed...Just click OK and the sheets will be protected", "Password Input")
Sheets("DOR").Protect Password:="techedit", UserInterfaceOnly:=True, AllowInsertingRows:=True
Sheets("Menus (hidden)").Protect Password:="techedit", UserInterfaceOnly:=True
Sheets("DFP").Protect Password:="techedit", UserInterfaceOnly:=True
Sheets("Platform Model").Protect Password:="techedit", UserInterfaceOnly:=True

End Sub

Sub UnProtectAll()

Dim wSheet As Worksheet
Dim Pwd As String

Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
On Error Resume Next
For Each wSheet In Worksheets
wSheet.Unprotect Password:=Pwd
Next wSheet
If Err 0 Then
MsgBox "You have entered an incorect password. All worksheets could not " & _
"be unprotected.", vbCritical, "Incorect Password"
End If
On Error GoTo 0

End Sub

Can I allow users to use spellcheck without unlocking the sheet???


I have VBA protected my worksheet. Furthermore I have protected my worksheet with a button wherein I can put in a new password each time (if I like) with the following code:


 Private Sub CommandButton1_Click()
Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        If wSheet.ProtectContents = True Then
            wSheet.Unprotect Password:=TextBox1.Text
            wSheet.Protect Password:=TextBox1.Text
        End If
    Next wSheet
Unload Me
End Sub

My problem: When I do not password protect the worksheet with the aforementioned code/button all Macro's work perfectly. However, when I do protect the worksheet with a password I get the 1004 run time error. Please be informed that both passwords (VBA and Worksheet) are the same.

Can anybody please help me with this? Thank you very much in advance!

Kind regards,

Hi there,

I got this worksheet which i will hide/unhide rows depending on user selection in a drop down list. However i do not want user to click on "locked cells". It work fine but whenever i save the document, the "locked cells" can be click. How do i disable the "locked cells" from being click? Any argument which i can add to unprotect/protect command?


sht.Unprotect Password:="abc"

... hide/unhide row....

sht.Protect Password:="abc"

I have a macro that hides columns and another that reveals these columns. I
want to have parts of my worksheet protected. When having the worksheet
protected I cannot use the macro. I know that you can write:

ActiveSheet.Unprotect Password:="MyPassword"
Selection.EntireColumn.Hidden = True
ActiveSheet.Protect Password:="MyPassword"

This works. The problem is if the user that has access to the password wants
to change the password but is not informed of the fact the macro has to be
changed. Is there any choice you can make when protecting the worksheet so
that columns can be hidden/revealed? They are after all not altered? I am
very greatful for any assistance

I have code that hides and unhides rows and columns based on different checkbox selections. When the workbook is unprotected the code works no problem. When the workbook is protected I get the error message in the title. The code I'm using to hide and unhide looks like this:


Private Sub checkbox1_click()
    Worksheets("Sheet1").Range("G:G").Columns.Hidden = Not     checkbox1.Value
End Sub

and resides in a different worksheet then Sheet1. In the workbook_open event I call a sub called lock_ws which looks like this:


Sub lock_ws()
    Dim ws As Worksheet
    'lock worksheets
    For Each ws In ThisWorkbook.Worksheets
            ws.EnableSelection = xlUnlockedCells
            ws.Protect "password", UserInterfaceOnly:=True
    Next ws
End Sub

In another project I have I was able to hide and unhide rows and columns that were locked while the workbook was protected using basically the same code as above. Any help will be greatly appreciated

I have the macro code below to unprotect a worksheet, then run a function, and then finally re protect the work sheet.
However i would like to add in a password to be entered when the macro is run, then when the worksheet is re-protected the password is automatically entered.
e.g. the code would say:
1. enter password
2. unprotect worksheet
3. delete selection
4. password protect worksheet.


Sub Delete_Rows()
' Delete_Rows Macro
' This macro will delete the entire row and all cell contents. 

    Selection.Delete Shift:=xlUp
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowDeletingColumns:=True, AllowDeletingRows:=True
End Sub

Hello everyone,

I'm trying to create a macro that is not a button and unvisible to other users except for myself. This macro will hide certain worksheets, protect a worksheet and Protect the Structure and Windows. I want these to be password protected as well. Hopefully, I explained this well but I have some locked, unlocked cells in certain worksheets and if I can get all these functions down to one or two actions each time that would be great. I tried recording a macro on a form control just to see what would happen but it seems to protect everything without a password.