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

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
3,963
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

Well-known Member
Joined
Oct 20, 2003
Messages
513
Will this work for you. You can change the mymacro to whatever you need ran.

Code:
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

Well-known Member
Joined
Jan 11, 2012
Messages
3,963
Will this work for you. You can change the mymacro to whatever you need ran.

Code:
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

Well-known Member
Joined
Oct 20, 2003
Messages
513
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.

Code:
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.

Code:
 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.

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

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,491
Office Version
2016
Platform
Windows
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:
Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,774
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.
 

Forum statistics

Threads
1,081,526
Messages
5,359,279
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top