Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 34 of 34

Thread: Two macro button but in a double action

  1. #21
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    236
    Location
    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.

  2. #22
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    236
    Location
    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.
    Attached Files Attached Files

  3. #23
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    236
    Location
    Friends I beseech you for any assistance.

  4. #24
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,375
    Location
    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.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #25
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    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

  6. #26
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    236
    Location
    Quote Originally Posted by Aussiebear View Post
    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

  7. #27
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    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.

  8. #28
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    236
    Location
    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
    Attached Files Attached Files

  9. #29
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    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

  10. #30
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    236
    Location
    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)

  11. #31
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    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)
    Last edited by jonh; 09-19-2014 at 02:52 PM.

  12. #32
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    236
    Location
    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.

  13. #33
    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.

  14. #34
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,449
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •