Grey out/Disable "Unhide All" control of right click menu

[Momentman]

I created a userform to Unhide multiple sheets in excel, and i added the Macro to display the userform to the right click menu of the sheet tabs

What i want to have is the "Unhide All" button greyed out when there are no hidden sheets and enabled/active when there are hidden sheets. A context sensitive control. The challenge is what event to use to trap the "Right clicking of the sheet tabs" to know to run the macro to add the "UnhideAll" control to the right click menu

Thanks for always helping out

[/IMG]

 

[wwbwb]

Will this work for you. You can change the mymacro to whatever you need ran.

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Dim btn As CommandBarButton
Dim wrksts As Worksheet
Dim shtcnt As Integer

shtcnt = 0

For Each wrksts In Application.ActiveWorkbook.Sheets
If wrksts.Visible Then shtcnt = shtcnt + 1
Next

If ActiveWorkbook.Sheets.Count - shtcnt > 0 Then
 On Error Resume Next
  With Application
   .CommandBars("Cell").Controls("MyMacro").Delete
   Set btn = .CommandBars("Cell").Controls.Add(Temporary:=True)
  End With
 
  With btn
   .Caption = "MyMacro"
   .Style = msoButtonCaption
   .OnAction = "MyMacro"
  End With
 On Error GoTo 0
    
Else
 
 On Error Resume Next
  With Application
   .CommandBars("Cell").Controls("MyMacro").Delete
   Set btn = .CommandBars("Cell").Controls.Add(Temporary:=True)
  End With
 
  With btn
   .Caption = "MyMacro"
   .Style = msoButtonCaption
   .Enabled = False
  End With
 On Error GoTo 0
End If

End Sub

 

[Momentman]

Will this work for you. You can change the mymacro to whatever you need ran.

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Dim btn As CommandBarButton
Dim wrksts As Worksheet
Dim shtcnt As Integer

shtcnt = 0

For Each wrksts In Application.ActiveWorkbook.Sheets
If wrksts.Visible Then shtcnt = shtcnt + 1
Next

If ActiveWorkbook.Sheets.Count - shtcnt > 0 Then
 On Error Resume Next
  With Application
   .CommandBars("Cell").Controls("MyMacro").Delete
   Set btn = .CommandBars("Cell").Controls.Add(Temporary:=True)
  End With
 
  With btn
   .Caption = "MyMacro"
   .Style = msoButtonCaption
   .OnAction = "MyMacro"
  End With
 On Error GoTo 0
    
Else
 
 On Error Resume Next
  With Application
   .CommandBars("Cell").Controls("MyMacro").Delete
   Set btn = .CommandBars("Cell").Controls.Add(Temporary:=True)
  End With
 
  With btn
   .Caption = "MyMacro"
   .Style = msoButtonCaption
   .Enabled = False
  End With
 On Error GoTo 0
End If

End Sub

Thanks for your code.

The general idea is same as mine, the challenge however is that when you right click the Sheet tabs, this event does not fire, except you right click somewhere within the spreadsheet, which is not what i want.

 

[wwbwb]

Ok, try this. I'm not "great" at this, but maybe it will set you in a direction you hadn't seen yet.

As you can see it needs a change on any sheet to run first, but it seems to work.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim btn As CommandBarButton
Dim wrksts As Worksheet
Dim shtcnt As Integer

shtcnt = 0

For Each wrksts In Application.ActiveWorkbook.Sheets
If wrksts.Visible Then shtcnt = shtcnt + 1
Next

If ActiveWorkbook.Sheets.Count - shtcnt > 0 Then
 On Error Resume Next
  With Application
   .CommandBars("Ply").Controls("Unhide All").Delete
   Set btn = .CommandBars("Ply").Controls.Add(Temporary:=True)
  End With
 
  With btn
   .Caption = "Unhide All"
   .Style = msoButtonCaption
   .OnAction = "Unhide_All"
  End With
 On Error GoTo 0
  
Else
 
 On Error Resume Next
  With Application
   .CommandBars("Ply").Controls("Unhide All").Delete
   Set btn = .CommandBars("Ply").Controls.Add(Temporary:=True)
  End With
 
  With btn
   .Caption = "Unhide All"
   .Style = msoButtonCaption
   .Enabled = False
  End With
 On Error GoTo 0
End If

End Sub

Also, add this to the end of your Unhide code, so the button is unenabled every time it is run.

 On Error Resume Next
  With Application
   .CommandBars("Ply").Controls("Unhide All").Enabled = False
  End With

If the sheets are hidden by vba, perhaps adding the following after the code hides the sheet could help too.

  With Application
   .CommandBars("Ply").Controls("Unhide All").Enabled = True
  End With

 

[Jaafar Tribak]

There is no such event .. Short of using a loop or a Windows Timer both of which are best avoided , you could sink the CommandBars OnUpdate event to detect when the Sheets Tabs context menu is right-clicked.

See if the following works for you

Code in the ThisWorkbook Module:

Option Explicit

Private WithEvents cmbrs As CommandBars

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare Function GetActiveWindow Lib "user32" () As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

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

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

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


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

Private Sub cmbrs_OnUpdate()
    Call MonitorSheetTabsRightClick
End Sub

Private Sub MonitorSheetTabsRightClick()
    Dim sBuf As String * 256
    Dim lRet As Long
    
    With Application.CommandBars.FindControl(ID:=2020): .Enabled = Not .Enabled: End With
    
    If ActiveWorkbook Is ThisWorkbook Then
        lRet = GetClassName(GetActiveWindow, sBuf, 256)
        With Application.CommandBars("Ply").Controls("Un&hide All")
            If Left(sBuf, lRet) = "Net UI Tool Window" Then
                .Visible = True
                If AreSheetsProtected Then
                    .Enabled = True
                Else
                    .Enabled = False
                End If
            End If
        End With
    End If
    
End Sub

Private Sub AddToPlyMenu()
    Call DeleteFromPlyMenu
    With Application.CommandBars("Ply").Controls.Add(Type:=msoControlButton, Temporary:=True, Before:=5)
        .CAPTION = "Un&hide All"
        .BeginGroup = True
        .OnAction = Me.CodeName & ".Unhide_All_Sheets"
        .Enabled = IIf(AreSheetsProtected, True, False)
    End With
End Sub

Private Sub DeleteFromPlyMenu()
    On Error Resume Next
    Application.CommandBars("Ply").Reset
End Sub

Private Function AreSheetsProtected() As Boolean
    Dim sh As Worksheet
    
    For Each sh In Me.Worksheets
        If sh.ProtectContents Then
            AreSheetsProtected = True
            Exit For
        End If
    Next
End Function

Private Sub Unhide_All_Sheets()
    Dim sh As Worksheet
    
    For Each sh In Me.Worksheets
        If sh.ProtectContents Then
            sh.Unprotect 'password
        End If
    Next
End Sub

 

[mikerickson]

I would use the SheetActivate event. Everytime a sheet is activated, like when a sheet is hidden or unhidden, loop through the sheets, if all of them are visible, set the control's Enabled to False.