Consulting

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

Thread: Ribbon - toggle button problems

  1. #21
    Try this in the workbook that you sent me:
    1. open workbook
    2. go to sheet 1, press lock
    3. go to sheet 2, press lock
    4. go to sheet 3, press lock
    5. just switch between the sheets, go from sheet 1 to sheet 2 to sheet 3.
    The buttons state does not update ( one or more sheets have the two buttons at state false )

  2. #22
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    try this

    i cleand up the logic and code a little more

    if you make a manual protection change, i don't know of any way to update the ribbon, but if you switch out and back in it should update
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    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

  3. #23
    Hello
    Thanks a lot, now it works perfectly.
    I commented this line: Err.Clear '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    Because the Err.Clear removed the error number witch i used to allow specific people to lock or unlock the sheets.
    And with the line active the msgbox's did not appear if user was not found in the first sheet.
    But now it works perfectly, thank you very much

  4. #24
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    Try this version

    I added a Workbook_Open and cleanup up some other code

    I "think' it fixes those two issues
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    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

  5. #25
    Hello, works perfectly, thank you.

  6. #26
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    Quote Originally Posted by Rolly_Sefu View Post
    Hello
    I commented this line: Err.Clear '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    Yes, I used that to bypass the password checking since I was only interested in getting the buttons to work the way you wanted
    ---------------------------------------------------------------------------------------------------------------------

    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

  7. #27
    Hy, this is regarding to the same thread, same file.

    I need to modify the code a little bit:
    - when i click on the lock button, it will password protect all cells in the sheet.
    - when i click on the unlock button, i want to unprotect the sheet, unlock only column H, then protect the sheet and switch the toggle button to unlocked

    I modified the code for the column H part, but i just can't get the toggle button to stay on unlocked.
    The code below resets the toggle buttons
    If i remove the L1="Yes" from the end when i click on unprotect it will just turn on the lock toggle button.

    Option Explicit
    
    Public gobjRibbon As IRibbonUI
     
    Public Sub OnRibbonLoad(ribbon As IRibbonUI)
    Set gobjRibbon = ribbon
    gobjRibbon.Invalidate
    End Sub
    
    
    'Callback for customButton1 onAction
    Public Sub Pushed(Control As IRibbonControl, pressed As Boolean)
    Dim user As String, X As String, Y As String
    user = Environ("Username")
    On Error Resume Next
    X = Application.WorksheetFunction.VLookup(user, Sheets("Stuff").Range("Login"), 1, False)
    Select Case Control.ID
        Case "tbLocked"
            If Err.Number > 0 Then
                MsgBox "I'm sorry, you do NOT have access to LOCK this sheet !", vbCritical
            Else
                On Error GoTo 0
                If ActiveSheet.Name <> "WRS AR employees" Then
                    ActiveSheet.Protect Password:=pwd, UserInterfaceOnly:=True, DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
                    Columns("H:H").EntireColumn.Locked = True
                    ActiveSheet.Range("L1") = "No"
                Else
                    MsgBox "I'm sorry, you do NOT have access to LOCK this sheet !", vbCritical
                End If
            End If
        Case "tbUnlocked"
            If Err.Number > 0 Then
                MsgBox "I'm sorry, you do NOT have access to UNLOCK this sheet !", vbCritical
            Else
                On Error GoTo 0
                If ActiveSheet.Name <> "WRS AR employees" Then
                    ActiveSheet.Unprotect Password:=pwd
                    On Error Resume Next
                    Y = Application.WorksheetFunction.VLookup(user, Sheets("Stuff").Range("Access"), 1, False)
                    If Err.Number = 0 Then
                        Columns("H:H").EntireColumn.Locked = False
                        ActiveSheet.Protect Password:=pwd, UserInterfaceOnly:=True, DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
                        ActiveSheet.Range("L1") = "Yes"
                    End If
                    On Error GoTo 0
                Else
                    MsgBox "I'm sorry, you do NOT have access to UNLOCK this sheet !", vbCritical
                End If
            End If
    End Select
    gobjRibbon.Invalidate
    End Sub
    
    
    'Callback for customButton1 getPressed
    Public Sub UpOrDown(Control As IRibbonControl, ByRef returnedVal)
    If ActiveSheet.Range("L1") = "Yes" Then
        returnedVal = Not ActiveSheet.ProtectContents
        Exit Sub
    End If
    Select Case Control.ID
        Case "tbLocked"
            returnedVal = ActiveSheet.ProtectContents
        Case "tbUnlocked"
            returnedVal = Not ActiveSheet.ProtectContents
    End Select
    End Sub
    Any ideas ?
    Thank you.

  8. #28
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    It's a whole lot easier if you can post small sample workbook with enougth sheets to show problem
    ---------------------------------------------------------------------------------------------------------------------

    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

  9. #29
    Hello

    I have attached a file.

    So the modification to the previous version is that:

    1. on lock => all cells are locked ( lock toggle button ON )
    2. on Unlock => all cells are locked, EXCEPT cells in column H ( unlock toggle button ON )

    The problem is that with the modification i have made when i click on unlock, the 2 toggle buttons deselect

    Any ideas ?
    Thanks.
    Attached Files Attached Files

  10. #30
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    I didn't trace through the whole thing, but I think that the sheet lunie is always protected

    [OVT Locked] protects the entire sheet, including col H
    [OVT Unlocked] protects the entire sheet, except col H

    So "
    ActiveSheet.ProtectContents" is always true

    It might be better to just test if Col H is locked instead: "ActiveSheet.range("H:H").locked"


    I didn't understand the L1 part


    'Callback for customButton1 getPressed
    Public Sub UpOrDown(Control As IRibbonControl, ByRef returnedVal)
    
    'If ActiveSheet.Range("L1") = "Yes" Then
    '    returnedVal = Not ActiveSheet.ProtectContents
    '   Exit Sub
    'End If
    
    Select Case Control.ID
        Case "tbLocked"
            returnedVal = ActiveSheet.Range("H:H").Locked
        Case "tbUnlocked"
            returnedVal = Not ActiveSheet.Range("H:H").Locked
    End Select
    End Sub
    
    
    
    ---------------------------------------------------------------------------------------------------------------------

    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

  11. #31
    Hello,

    The L1 part was just a noob thinking ... did not think to check directly if column H is locked or not ( i removed all L1 lines )

    I tryed the lines "returnedVal = ActiveSheet.Range("H:H").Locked" and they work perfectly.
    Exactly what i needed.

    Many thanks.

  12. #32
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    Here's a more cleaned version if you want it

    I added button labels to make it a little more explainitory
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    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

  13. #33
    It's great, thank you

Posting Permissions

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