Review tab: Protect workbook structure except for hide/unhide sheets

O

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,040
Office Version
  1. 2019
Platform
  1. Windows
Hello,

In excel 2016 would it be possible to use all the functionality of a workbook ->protect ->structure yet keep the hide and unhide of sheets unprotected for the end-user.


Thank you and will appreciate.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
omairhe said:
Hello,

In excel 2016 would it be possible to use all the functionality of a workbook ->protect ->structure yet keep the hide and unhide of sheets unprotected for the end-user.


Thank you and will appreciate.
Click to expand...
If you can have vba code, you could for example hide and/or unhide sheets from a button or something.
 
Upvote 0
Jaafar Tribak said:
If you can have vba code, you could for example hide and/or unhide sheets from a button or something.
Click to expand...


Best case scenario is for the hide or unhide to stay highlighted/clickable in context menu when right clicking the sheet tab. I believe there is an add-in for what I am looking for but I am unenthused to give that a try. I was hoping if a code can do that somehow.

Here's the link for the add-in------> http://www.lockxls.com/help.asp?hid=ht_unhide
 
Upvote 0
omairhe said:
Best case scenario is for the hide or unhide to stay highlighted/clickable in context menu when right clicking the sheet tab. I believe there is an add-in for what I am looking for but I am unenthused to give that a try. I was hoping if a code can do that somehow.

Here's the link for the add-in------> http://www.lockxls.com/help.asp?hid=ht_unhide
Click to expand...

I may be wrong but I suspect that the add-in does something similar to what I suggested ...I don't think the addin allows the hide and unhide in context menu to stay enabled when right clicking the sheet tab while the workbook structure is locked.
 
Upvote 0
One thing you can do is add 2 custom menus to the Tab right-Click context menu via vba that will hide/unide the worksheets.

Place this in the ThisWorkbook Module :
Code: 
Option Explicit

Private Sub Workbook_Open()
    Call AddCutomMenus
End Sub

Private Sub Workbook_Activate()
    Call AddCutomMenus
End Sub

Private Sub Workbook_Deactivate()
    Call RemoveCutomMenus
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call RemoveCutomMenus
End Sub

Private Sub AddCutomMenus()
    Dim i As Long
    Call RemoveCutomMenus
    For i = 1 To 2
        With Application.CommandBars("Ply").Controls.Add(Temporary:=True)
            .Caption = IIf(i = 1, "Hide", "UnHide")
            .Style = msoButtonCaption
            .OnAction = "'" & Me.CodeName & ".Hide_UnHide_Macro " & Chr(34) & IIf(i = 1, False, True) & Chr(34) & "'"
        End With
    Next i
End Sub

Private Sub RemoveCutomMenus()
    On Error Resume Next
        With Application.CommandBars("Ply")
            .Controls("Hide").Delete
            .Controls("UnHide").Delete
        End With
    On Error GoTo 0
End Sub

Private Sub Hide_UnHide_Macro(ByVal Show As Boolean)

    Const PASSWORD As String = [COLOR=#0000ff][B]"YourPassword"[/B][/COLOR]
    
    On Error GoTo Xit
  
    With ThisWorkbook
        If .ProtectStructure Then
            .Unprotect PASSWORD
            If Show Then
                Application.CommandBars.FindControl(, 891).Execute
            Else
                Application.CommandBars.FindControl(, 890).Execute
            End If
        End If
Xit:
        .Protect PASSWORD, True
    End With
End Sub

Try doing the same with the Home>Format>Hide & Unhide menu.
 
Last edited:
Upvote 0
Jaafar Tribak said:
One thing you can do is add 2 custom menus to the Tab right-Click context menu via vba that will hide/unide the worksheets.

Place this in the ThisWorkbook Module :
Code: 
Option Explicit

Private Sub Workbook_Open()
    Call AddCutomMenus
End Sub

Private Sub Workbook_Activate()
    Call AddCutomMenus
End Sub

Private Sub Workbook_Deactivate()
    Call RemoveCutomMenus
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call RemoveCutomMenus
End Sub

Private Sub AddCutomMenus()
    Dim i As Long
    Call RemoveCutomMenus
    For i = 1 To 2
        With Application.CommandBars("Ply").Controls.Add(Temporary:=True)
            .Caption = IIf(i = 1, "Hide", "UnHide")
            .Style = msoButtonCaption
            .OnAction = "'" & Me.CodeName & ".Hide_UnHide_Macro " & Chr(34) & IIf(i = 1, False, True) & Chr(34) & "'"
        End With
    Next i
End Sub

Private Sub RemoveCutomMenus()
    On Error Resume Next
        With Application.CommandBars("Ply")
            .Controls("Hide").Delete
            .Controls("UnHide").Delete
        End With
    On Error GoTo 0
End Sub

Private Sub Hide_UnHide_Macro(ByVal Show As Boolean)

    Const PASSWORD As String = [COLOR=#0000ff][B]"YourPassword"[/B][/COLOR]
    
    On Error GoTo Xit
  
    With ThisWorkbook
        If .ProtectStructure Then
            .Unprotect PASSWORD
            If Show Then
                Application.CommandBars.FindControl(, 891).Execute
            Else
                Application.CommandBars.FindControl(, 890).Execute
            End If
        End If
Xit:
        .Protect PASSWORD, True
    End With
End Sub

Try doing the same with the Home>Format>Hide & Unhide menu.
Click to expand...

Awesome. One last thing.. If it is possible. Could the "Hide" and "UnHide" created from your stated code above be the only items that show up on right clicking tab sheet? and

Insert...
Delete
Rename
Move or Copy...
View Code
Protect Sheet
Tab Color...
Hide
Unhide...
Select All Sheets

are all set to invisible/cut?

Hence on right clicking sheet tab

Hide
UnHide

Shows up only.
 
Upvote 0
Or perhaps if it were possible for excel vba to detect mouse right click on the sheet tab and disable the default context menu and bring about a new user made context menu window which looks and feel identical to excel's built-in context menu window but with only hide and unhide options?

Code: 
[COLOR=#0A0101][FONT=Consolas]<code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Private</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Sub</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Workbook_BeforeClose(Cancel </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">As</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Boolean</code><code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">)</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb spaces" style="color: rgb(221, 0, 85); white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">    </code><code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Application.CommandBars(</code><code class="vb string" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 255) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">"Ply"</code><code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">).Enabled = </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">True</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">End</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Sub</code>[/FONT][/COLOR]

[COLOR=#0A0101][FONT=Consolas]<code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Private</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Sub</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Workbook_Open()</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb spaces" style="color: rgb(221, 0, 85); white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">    </code><code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Application.CommandBars(</code><code class="vb string" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 255) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">"Ply"</code><code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">).Enabled = </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">False</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">End</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Sub</code>[/FONT][/COLOR]

Thank you.
Your original code will suffice too...
 
Last edited:
Upvote 0
omairhe said:
Or perhaps if it were possible for excel vba to detect mouse right click on the sheet tab and disable the default context menu and bring about a new user made context menu window which looks and feel identical to excel's built-in context menu window but with only hide and unhide options?

Code: 
[COLOR=#0A0101][FONT=Consolas]<code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Private</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Sub</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Workbook_BeforeClose(Cancel </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">As</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Boolean</code><code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">)</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb spaces" style="color: rgb(221, 0, 85); white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">    </code><code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Application.CommandBars(</code><code class="vb string" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 255) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">"Ply"</code><code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">).Enabled = </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">True</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">End</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Sub</code>[/FONT][/COLOR]

[COLOR=#0A0101][FONT=Consolas]<code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Private</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Sub</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Workbook_Open()</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb spaces" style="color: rgb(221, 0, 85); white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">    </code><code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Application.CommandBars(</code><code class="vb string" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 255) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">"Ply"</code><code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">).Enabled = </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">False</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">End</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Sub</code>[/FONT][/COLOR]

Thank you.
Your original code will suffice too...
Click to expand...

That is going to be more difficult as there is no native event that detects mouse right click on the sheet tab.. It can be done but it will require a windows timer or a CBT hook both of which can have a performance hit on the application.

The standard way to go is via using the custom ui editor .. Take a look ath the kinks below:

https://www.rondebruin.nl/win/s2/win001.htm

https://social.msdn.microsoft.com/F...ommand-in-excel-2010-using-vba?forum=exceldev

The only other thing I can think of that avoids the need to use the Ribbon XML is to use MSAcessibility .. I'll give this a shot and post back.
 
Upvote 0
Ok- Here is a code that detects the mouse right click on the sheet tabs of the protected workbook, disables the default context menu and displays a custom popup with only hide & unhide options and this without the need to use a timer or a continuous do-loop :

Put this code in the ThisWorkbook Module:
Code: 
Option Explicit

Private WithEvents cmbrs As CommandBars

Private Type POINTAPI
    X As Long
    Y As Long
End Type

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  Win64 Then
        Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "oleacc" (ByVal arg1 As LongPtr, ppacc As Any, pvarChild As Variant) As Long
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    Private Declare PtrSafe Function WindowFromAccessibleObject Lib "oleacc" (ByVal pacc As IAccessible, phwnd As LongPtr) As Long
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
    Private Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function AccessibleObjectFromPoint Lib "oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
    Private Declare Function WindowFromAccessibleObject Lib "oleacc" (ByVal pacc As IAccessible, phwnd As Long) As Long
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private Sub Workbook_Open()
    Set cmbrs = Application.CommandBars
    Call MonitorSheetTabsRightClick
End Sub

Private Sub Workbook_Activate()
    Set cmbrs = Application.CommandBars
    Call MonitorSheetTabsRightClick
End Sub

Private Sub Workbook_Deactivate()
    Call RemoveCustomMenu
    Set cmbrs = Nothing
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set cmbrs = Application.CommandBars
    Call MonitorSheetTabsRightClick
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call RemoveCustomMenu
    Set cmbrs = Nothing
End Sub

Private Sub cmbrs_OnUpdate()
    Call MonitorSheetTabsRightClick
End Sub

Private Sub MonitorSheetTabsRightClick()
    Dim oIA As IAccessible
    Dim tCursPos As POINTAPI
        
    With Application.CommandBars.FindControl(ID:=2040)
        .Enabled = Not .Enabled
    End With

    If ActiveWorkbook Is ThisWorkbook Then
        If ThisWorkbook.ProtectStructure Then
            Application.CommandBars("Ply").Enabled = False
            GetCursorPos tCursPos
            [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
                [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  Win64 Then
                    Dim lngPtr As LongPtr
                    CopyMemory lngPtr, tCursPos, LenB(tCursPos)
                    Call AccessibleObjectFromPoint(lngPtr, oIA, 0)
                [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
                    Call AccessibleObjectFromPoint(tCursPos.X, tCursPos.Y, oIA, 0)
                [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
            [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
                Call AccessibleObjectFromPoint(tCursPos.X, tCursPos.Y, oIA, 0)
            [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
            On Error GoTo Xit
            If GetAsyncKeyState(VBA.vbKeyRButton) Then
                If (Sheets(oIA.accName(0&)).Name = ActiveSheet.Name) = True Then
                    Call DisplayPopup
                    DoEvents
                End If
            End If
        End If
    End If
Xit:
End Sub

Private Sub RemoveCustomMenu()
    On Error Resume Next
        Application.CommandBars("MyPopup").Delete
    On Error GoTo 0
    If Not ActiveWorkbook Is ThisWorkbook Then Application.CommandBars("Ply").Enabled = True
End Sub

Private Sub DisplayPopup()
    Dim i As Long
    
    Call RemoveCustomMenu
    
    With Application.CommandBars.Add("MyPopup", msoBarPopup, False, True)
        For i = 1 To 2
            With .Controls.Add(Type:=msoControlButton)
                .OnAction = "'" & Me.CodeName & ".Hide_UnHide_Macro " & Chr(34) & IIf(i = 1, False, True) & Chr(34) & "'"
                .Caption = IIf(i = 1, "Hide Sheet", "UnHide Sheet(s)")
                .FaceId = IIf(i = 1, 375, 374)
            End With
        Next i
    End With
    Application.CommandBars("MyPopup").ShowPopup
End Sub

Private Sub Hide_UnHide_Macro(ByVal Show As Boolean)
    Const PASSWORD As String = "[COLOR=#0000ff][B]YourPassword[/B][/COLOR]"
    
    On Error GoTo Xit
    With ThisWorkbook
        If .ProtectStructure Then
            .Unprotect PASSWORD
            If Show Then
                Application.CommandBars.FindControl(, 891).Execute
            Else
                Application.CommandBars.FindControl(, 890).Execute
            End If
        End If
Xit:
        .Protect PASSWORD, True
    End With
End Sub

 
Upvote 0
You r so kind...
Thanks again.. Would you mind stating how to change the "+" and "_" to blank space completely. Just for the option sake..
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA to run in Protected Workbook
Replies
5
Views
196
bobsan42
bobsan42
S
  • Solved
Hide Columns and password protect without locking cell
Replies
1
Views
396
Sergio
S
M
  • Question
Need multiple buttons to hide/show rows on another tab
Replies
8
Views
390
skillilea
S
R
  • Solved
Hide / Unhide Rows in protected sheets
Replies
1
Views
1K
rlv01
R
A
  • Solved
Activating hidden Sheets using If statement.
2 3
Replies
23
Views
689
kevin9999
K

Forum statistics

Threads
1,214,884
Messages
6,122,082
Members
449,064
Latest member
MattDRT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top