Email:      Pass:    Pass?
Advertisements


Free Excel Forum

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.


Similar Excel Video Tutorials

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

Similar Topics







I currently have the code below for a button that will unhide last 175 rows in my worksheet. I now would like to have it hide the rows that are blank in in the range (C7:BL206) looking from the bottom up. For example, if there were values in any of the cells (Columns C up to BL only) in row 190, the macro would only hide rows 191 thru 206. Any suggestions??

VB:

Private Sub CommandButton2_Click() 
    ActiveSheet.Unprotect Password:="" 
    Range("A32:A206").EntireRow.Hidden = False 
    ActiveSheet.Protect Password:="" 
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ 
    , AllowFormattingColumns:=True, _ 
    AllowFormattingRows:=True, AllowFiltering:=True, _ 
    AllowUsingPivotTables:=True 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




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()
Welcome.Show
Sheets(13).Protect Password:="password", _
UserInterFaceOnly:=True
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??

Thanks,
Omar





Dear Experts,

I would like to provide an option button to enable users to hide preselected
rows (25 to 128) in a worksheet. Click on the button, the rows are hidden,
unclick the button the rows reappear. I have tried the following code in the
Sheet 5 (Code) window of the VB screen:

Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then
Row("25:128").Select
Selection.EntireRow.Hidden = True
Else
Row("25:128").Select
Selection.EntireRow.Hidden = False
End If
End Sub

This code works to hide the rows, but the rows do not reappear (possibly
because the option button doesn't respond to the second click to clear the
button). I would appreciate any help please.

As an unexpected bonus, it would be excellent if the main user could be
asked for a user-specified password after clicking the option button to hide
the rows so the hidden rows are protected from viewing by others who access
the file. Then, when the main user wants to view the hidden rows, clicking
on the option button to clear the button would prompt the main user for his
password, after which the rows would unhide. If code can't do this, then I
will direct the user to click on Tools, Protection, Protect Sheet to password
protect the sheet after hiding the rows.

Thank you for your expertise and assistance.



Hey Guys/Gals..

I'm utilizing locked spreadsheets of which I wish to hide and unhide rows at random utilizing a button from a module that I can use on any sheet.

Thanks to the many post on this board the hide rows scenerio has been tackled and looks like this:

Sub Macro19()
'
' Macro19 Macro
' hide unused rows
'
' Keyboard Shortcut: Ctrl+a
'
ActiveSheet.Unprotect Password:="password"
Application.ScreenUpdating = False
With Selection.Offset(0)
Selection.EntireRow.Hidden = True
.Select
ActiveSheet.Protect Password:="password"
End With
End Sub

I figured just by changing True to False on another module I could then unhide rows @ random...

Silly Me

Can someone solve this for me please?


Hi

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
Next
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?

Help!

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

'****TURN OFF SCREEN UPDATING
Application.ScreenUpdating = False
'****UNPROTECT SHEETS
Sheets("Entry").Select
ActiveSheet.Unprotect
Sheets("Data").Select
ActiveSheet.Unprotect

and at the end of the macro:

'****RESET PROTECTED SHEETS
Sheets("Entry").Select
ActiveSheet.Protect
Sheets("Data").Select
ActiveSheet.Protect
'****TURN ON SCREEN UPDATING
Application.ScreenUpdating = True


Hello

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.

Thanks

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.

Ideally:
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.)
Code:

Sub InsertRow()
    Sheets("Line Master").Select
    Rows("1:1").Select
    Selection.Copy
    Sheets("Input").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 MrExcel.com : (Except for the lack of protect/hide code, it seems to be working perfectly.)
Code:

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
Else
nrs = "rows " & Application.WorksheetFunction.Substitute(Selection.EntireRow.Address(0, 0), ":", " to ")
End If

ActiveSheet.Unprotect
If MsgBox("Do you want to delete " & nrs & "?", 36, "DELETE ROWS") = vbNo Then Exit Sub
Selection.EntireRow.Delete
ActiveSheet.Protect
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.


How I can make the current VBA code, below, allow users to insert rows but not allow any column inserts??

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
Sheets("Menus (hidden)").Protect Password:="techedit", UserInterfaceOnly:=True
Sheets("DFP").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



I want to allow users to insert rows on the sheet named"DOR". Any way I can do that

Hi,
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, _
AllowFormattingRows:=True

Thanks.
Henry


Hi

1st post on these forums.

I recorded a couple of macros on excel last night, one hides certain rows then sorts by columns E and B then hides certain columns and protects the sheet. The other does pretty much the opposite, unprotects the sheet then unhides the columns, sorts by columns B and E then unhides the rows.

My question is can I password protect the macro button so only authorised people can use it?

The reason being the hiden rows and columns need to stay hidden and only certain people able to unhide them due to there being sensitive information on them.

If theres no way of password protecting this any ideas on how I can keep these rows and columns from being unhidden by unauthorised people would be a great help.

Thanks in advance for any help provided.


Hi

1st post on these forums.

I recorded a couple of macros on excel last night, one hides certain rows then sorts by columns E and B then hides certain columns and protects the sheet. The other does pretty much the opposite, unprotects the sheet then unhides the columns, sorts by columns B and E then unhides the rows.

My question is can I password protect the macro button so only authorised people can use it?

The reason being the hiden rows and columns need to stay hidden and only certain people able to unhide them due to there being sensitive information on them.

If theres no way of password protecting this any ideas on how I can keep these rows and columns from being unhidden by unauthorised people would be a great help.

Thanks in advance for any help provided.


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"
and
Row 21 is unhidden in "Risk Estimate"

Here is what I have written so far:

[QUOTE]Private Sub ToggleButton15_Click()
ActiveSheet.Unprotect
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
Else
Sheets("Risk Estimate").Rows(21).EntireRow.Hidden = False
ActiveSheet.Rows("56:57").EntireRow.Hidden = False
End If
Sheets("Risk Estimate").Protect
ActiveSheet.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


Code:

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?
Say,
Code:

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?

Thanks.


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:

Code:

 
Sub HideOneMoreRow()
ActiveSheet.Unprotect
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
ActiveSheet.Protect
End Sub


Can you help

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

Sub SpellCheck()
With ActiveSheet
.Unprotect ("techedit")
.Range("A1:F100").CheckSpelling
.Protect ("techedit")
End With
End Sub


I need the code to allow for pictures to be inserted into the sheet named "Platform Model". Any ideas on how to allow user to do this???


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 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
Rows("42:42").Select
Selection.Copy
Rows("10:10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowInsertingRows:=True, _
AllowDeletingRows:=True

Thanks for the help

Mark


Hi

Please help on below, i have password protected and hidden formulas in this workbook. The only problem is i have a macro assigned to a button but this will not allow me to run any macros. How can i edit this code to allow me to have the Macro enabled at all times but also have the formulas hidden and password protected.

P,s how can i prevent users from deleting the Code (i.e hide the VBA code and password protect) and also prevent users from deleting the button which has the Macro assigned to it and the macro itself WHICH IS STORED MACROS (ALT + F8)

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
Next
ThisWorkbook.Protect Password:="drowssap", structu =True.
End Sub

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

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



Would love to simply hide a colum and use a password when "unhidden".
This is a worksheet wherein people enter data into it, but when
viewed, a column is hidden. Not that simple though.

Here is what I am doing now, but its cumbersome to say the least. Way
to automate or simplify this?

Undo protection to add data:
Open document, choose Tools, Protection, Unprotect, Enter password

Unhide hidden column:
F5 to open Go To box
Enter B2, OK
You wont see it but cursur in hidden cell
Choose from the top menu Format | Column | Unhide

Enter new data

Rehide column:
When ready to close choose Format, Column, Hide.


Reprotect document:
Tools, Protection, Protect, enter password


I have a workbook that has 6 worksheets. The first worksheet is the instructions to the users on how to use the worksheet. On the second worksheet, I have a command button that hides various rows of the worksheet depending on the user's needs. I'd like to expand this so that when the command button is used, it hides the same sets of rows in worksheets 2 through 6.

I know that I can use For Each to hide the rows in every worksheet, but of course I don't want to hide anything on the instructions page.

The worksheets are names Instructions, Wk 0, Wk 1, Wk 2, Wk 3, Wk 4 & Wk 5 if it matters at all.

The code to execute for each worksheet is:

Dim strMyPassword As String
strMyPassword = "2jt63an9"
ActiveSheet.Unprotect Password:=strMyPassword
Range("4:100").Select
Selection.EntireRow.Hidden = False
Range("14:18,24:26,32:33,43:43,45:45").Select
Selection.EntireRow.Hidden = True
With ActiveSheet
.EnableSelection = xlUnlockedCells
.Protect Password:=strMyPassword
End With

Obviously, the defining of the variables can be outside the loop, as it only needs to be defined once for all the worksheets.

Thanks in advance for the help!


Hi,

I am running a macro which updates a worksheet with user inputs. As part of this update I am using the find function to determine which fiscal year the user is updating information for. In order for the find function to work properly I need all columns and rows to be unhidden. Therefore within my macro I am allowing column and row formatting to be adjusted by unprotecting certain sheets. At the end of the macro I call the following procedure which reprotects each sheet. This works fine until it gets to sheets(13) at which point the hourglass pops up and the macro stops running. Sheets(13) is the worksheet which is being updated by the original macro.

Code:

Sub WSProtect()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    For Each ws In Worksheets
        ws.Unprotect password:="password"
        ws.Protect password:="password", userinterfaceonly:=True
    Next ws

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

End Sub


If I skip sheets(13), the code works fine. Additionally, if I change the code to the following:

Code:

    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    Sheets(13).Unprotect password:="password"
    Sheets(13).Protect password:="password", userinterfaceonly:=True

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

End Sub


It works just fine. Any clue why this might possibly be happening?


Thanks a lot.

Matt


Hey people,

I have a workbook that needs to be shared on a network.

I have a VBA code that hides all rows, and only unlocks specific rows based on a password the user inputs, and then hides all rows again on exit.

The problem is when the file becomes shared, I can not protect/unprotect sheets anymore, so I am forced to leave it unprotected and thus anyone can just unhide the rows themselves.

There might not be a solution to this problem, but does anyone have ideas for restricting access for users to hide/unhide rows and at the same time allow VBA to do so (in a shared workbook).

Thanks!