Two macro button but in a double action

**k0st4din** · 09-16-2014, 03:04 AM

ok, I'll do a sample workbook and attach it. from your letter I feel that you think that I do not know the password - it's not because I'm trying now to do this workbook and actually it has no passwords, ie I will ask these passwords and will fit in any I wish. in the above macros (which are not good for anything I have given password "kosta", but it is indicative. if we can do macro then I will change the password of the sheets (it will be the same for all sheets with names of cities). really thank you very much.

**k0st4din** · 09-16-2014, 09:51 AM

Hello everyone.
So - here is the sample file.
I have done it roughly the looks like my file.
In the original file I have many more cities and worksheets.
In orange I show which lines should be hidden in each worksheet with the name of the city and be locked.
Then worksheets with names of cities are unlocked and to show hidden rows - >>> as it wants to unlock password.
I am very grateful to you all.

**k0st4din** · 09-17-2014, 09:28 PM

Friends I beseech you for any assistance.

**Aussiebear** · 09-18-2014, 03:25 AM

Okay, so given that something is missing in the translation, please go through all of the posts.

1. Password protection. Sam gave you a great example on using a password to unhide those sheets you require.

2. The sheets to display. Sam also gave you an example of how to arrange the sheets you wish to display.

Please go back through the posts and indicate which posts relate to points 1 and 2.

**jonh** · 09-18-2014, 06:55 AM

Sorry, I haven't read all the posts. May have missed something.

'this workbook
Private Sub Workbook_Open()
    'Startup hidden
    HideUnhide
End Sub

'module
Dim hidden As Boolean

Public Sub HideUnhide()
    If hidden Then
        Unhide "kosta", "london", "ny", "paris"
    Else
        Hide "london", "ny", "paris"
    End If
    hidden = Not hidden
End Sub

Private Sub Hide(ParamArray sheetname() As Variant)
On Error Resume Next
    For Each sht In sheetname
        With Sheets(sht)
            .Range( _
            "4:4,5:5,10:10,11:11,13:13,14:14,17:17,18:18,34:34,35:35,38:38," & _
            "45:45,46:46,49:49,50:50,72:72,73:73,76:76,77:77,78:78,79:79" _
            ).EntireRow.hidden = True
            .Protect ("kosta"), DrawingObjects:=True, Contents:=True, Scenarios:=True _
                , AllowFiltering:=True
        End With
    Next
End Sub
Private Sub Unhide(pw As String, ParamArray sheetname() As Variant)
On Error GoTo ErrHandler
    For Each sht In sheetname
        With Sheets(sht)
            .Unprotect Password:=pw
            .Cells.EntireRow.hidden = False
        End With
    Next
    Exit Sub
ErrHandler:
    MsgBox "Wrong password", vbExclamation
End Sub

**k0st4din** · 09-18-2014, 08:04 AM

Originally Posted by Aussiebear

Okay, so given that something is missing in the translation, please go through all of the posts.

1. Password protection. Sam gave you a great example on using a password to unhide those sheets you require.

2. The sheets to display. Sam also gave you an example of how to arrange the sheets you wish to display.

Please go back through the posts and indicate which posts relate to points 1 and 2.

Hello Aussiebear
pleace read my post N= 12 and 16 -> for 1st question
(I think there I asked my questions accurately and exactly what I do not understand.)

pleace read my post N= 19 (VB2 my green text and question).
----------------------------------------------------------------------------------------------
jonh
: first steps to hide the desired cities and lock are great.
: the second step - unlock all desirable cities and unhide the hidden rows (Please read the green text in the macro (below))

Private Sub Unhide(pw As String, ParamArray sheetname() As Variant)    On Error GoTo ErrHandler
    For Each sht In sheetname
        With Sheets(sht)
            .Unprotect Password:=InputBox("Please write your password!") ' If I use this = pw - tells me directly "Wrong password", I tried to move in with my version displaying window and there to write the password to unlock all of the desired cities and then show hidden rows
            .Cells.EntireRow.hidden = False
        End With
    Next
    Exit Sub
ErrHandler:
    MsgBox "Wrong password", vbExclamation
End Sub

Screen_252 Sep. 18.jpg
I'll be grateful if you show me how to do that, write the password only once to unlock them all and show rows.
Now also was prepared but I write password as many times as the designated cities

**jonh** · 09-18-2014, 08:33 AM

Your error handler says 'Wrong Password' for any error.
You need to make sure that it really is the password that is incorrect. Put a breakpoint on the code and step through it.

Also, unless you have different passwords for each sheet you need to put the inputboxes in HideUnhide(), otherwise it will pop up for every sheet.

**k0st4din** · 09-18-2014, 10:28 AM

No, no - maybe you do not understand me.
I attach the file and there is your macro.
Try it:
First - when you open the workbook - the selected sheets will be locked and rows will be hidden.
2nd - press the button to unlock all selected worksheets (you can see them later in the macro), and see the result - no unlocks them and does not show the hidden rows.
3rd - if I get this "= pw" and put "InputBox (" Please write your password! ")" For each worksheet selected (recorded) in the macro begins to climb my form and wishes of each worksheet entering a password (this well if i have different passwords).
but in my case for all selected worksheets I have only one single password - ie the same for all sheets - and I wish once you show the form, I write the password only once to unlock all sheets and show rows.
Please try the file.
I hope now to be able to understand it.
But if there is something - please ask boldly.
Thanks
Perhaps the quickest option that comes to mind as an explanation:
pressing the button
displayed form
write password (same for sheets -> "kosta", after that i will change passwor, this is for the sample file)
pressing OK
unlocks the sheets (only those who have found its way into the macro., not all)
shows rows

**jonh** · 09-19-2014, 02:21 AM

Ok, try this.

ThisWorkbook

Private Sub Workbook_Open()
    HideUnhide True
End Sub

Module

Public Sub HideUnhide(Optional LockSheet As Boolean)
'LockSheet = true forces sheet(s) to be locked. Use on workbook open.
'LockSheet not specified or false; sheet(s) locked based on button caption.

    Dim shp As Object: Set shp = Sheets("Product").Shapes("Button 1").DrawingObject
    If Not LockSheet And shp.Caption = "Unhide" Then
        If Unhide(InputBox("Enter password"), "London", "NY", "Paris") Then
            shp.Caption = "Hide"
        End If
    Else
        If Hide("Plovdiv", "London", "NY", "Paris") Then
            shp.Caption = "Unhide"
        End If
    End If
End Sub
 
Private Function Hide(pw As String, ParamArray sheetname() As Variant) As Boolean
On Error Resume Next
    For Each sht In sheetname
        With Sheets(sht)
            Select Case Err.Number
            Case 0
                .Range( _
                    "4:4,5:5,10:10,11:11,13:13,14:14,17:17,18:18,34:34,35:35,38:38," & _
                    "45:45,46:46,49:49,50:50,72:72,73:73,76:76,77:77,78:78,79:79" _
                    ).EntireRow.hidden = True
                
                Select Case Err.Number
                Case 0, 1004 'already hidden?
                Case Else: Debug.Print Err.Number, Err.Description
                End Select
                Err.Clear
                
                .Protect pw, DrawingObjects:=True, Contents:=True, Scenarios:=True _
                    , AllowFiltering:=True
                    
                Select Case Err.Number
                Case Is <> 0: Debug.Print Err.Description
                End Select
                Err.Clear
                
            Case Else
                MsgBox "Sheet " & sht & " not found!"
                Err.Clear
            End Select
        End With
    Next
    Hide = True
End Function

Private Function Unhide(pw As String, ParamArray sheetname() As Variant) As Boolean
On Error Resume Next
    For Each sht In sheetname
        With Sheets(sht)
            Select Case Err.Number
            Case 0
                .Unprotect Password:=pw
                .Cells.EntireRow.hidden = False
                If Err.Number <> 0 Then
                    MsgBox "Wrong password", vbExclamation
                    Exit Function
                End If
            Case Else
                MsgBox "Sheet " & sht & " not found!"
                Err.Clear
            End Select
        End With
    Next
    Unhide = True
End Function

**k0st4din** · 09-19-2014, 08:54 AM

I'm probably the biggest stupid person.
Video shows you where you can see exactly what I do and again I can not see where to write my password to unlock these sheets.
And somehow one of the selected sheets (Plovdiv) is not activated by the macro and not know why?
He shows me the wrong password - and I think that is normal, because nowhere in the macro will set this, and when I have to write the password to unlock - it (the macro does not find one) and everything stays locked and remained hidden rows
http://www.videoklipove.com/video/5043/howtomp4/
(Can comfortably watch videos, no spam)
1 - I open the workbook
2 - all required worksheets are locked and hidden lines (with the exception of Plovdiv -> I do not know why he does not want the macro to act on this sheet). - Super, super ............

The made button from you is great, the show / hide - I thank you infinitely.

And now come the problems (please watch the video)

**jonh** · 09-19-2014, 02:36 PM

The first parameter of hide and unhide is the password.
For unhide you enter the password via the input box.
For hide it is hard coded. In the code I posted above the password is Plovdiv. Which is also a sheet name so I think you are confusing the two.

so you should change this line

If Hide("Plovdiv", "London", "NY", "Paris") Then

to

If Hide("whatever your password is", "Plovdiv", "London", "NY", "Paris") Then

++edit

basically for both, it's password and then the list of sheet names. (I've no idea why I used Plovdid as password)

**k0st4din** · 09-20-2014, 01:31 AM

Hello to All
finally, finally found a solution to my query.
I want to thank you all for your support and responsiveness. For your big hearts and a desire to help us in need.
Be healthy and always helps.
And do not **** off a lot - because we do not understand this macro.

Thank you once again.

**robinho0d** · 02-07-2023, 03:45 AM

Hello, everyone
I didn't know whether to ask here or ask a brand new question, because what I was looking for corresponds to 90% of the macro made.
My query is (if you say I will start a new topic with the question and link to this topic) can I do exactly the same idea, but make the macro so that it works in the same way, but for each selected worksheet, can to hide different rows.
For example:
London - rows - 25,69,88,89,90,115 etc
Paris - rows - 74,254,255,256,284,293 etc
Thank you in advance and if necessary please let me know if I should make a new topic.

**Paul_Hossler** · 02-07-2023, 12:37 PM

It would be better to start a new topic instead of tagging along on a 9 year old thread

You can refer to this one, but my preference would be to make the new one self contained so that it's easier for people to see what you're looking to do

Thread: Two macro button but in a double action

Thread Tools

Display

Posting Permissions