Toggle Button UI Ribbon

[mmarzouk]

Hi,

I have added a toggle button to my custom Tab. The toggle is intended to Show/Hide a Column in the active sheet. The button works fine, but my problem is when the user saves and closes the spreadsheet. After re-opening the spreadsheet the toggle button is switched off (Not highlighted).

My approach was when the button was clicked, I save a value (On or Off) in a hidden spreadsheet. But have yet to reach an answer as how to use that when the spreadsheet is re-opened.

Hoping you guys could help out.

Regards,
Mahmoud

 

[Jerry Sullivan]

Hi Mahmoud and Welcome to MrExcel,

Here's an example. I'd suggest you try this in a blank test workbook first to ensure it works for you, then incorporate it with your existing code.

Add a blank sheet named "MyHiddenSheet" to the test workbook.

Insert this into the Workbook's customUI.xml

Note that "******" should be replaced with "o n L o a d" (case-sensitive with no spaces or quotes ).

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
	******="ToggleHideColumn" >
<ribbon>
<tabs>
<tab idMso="TabView">
<group id="Group1" label="Custom">
<toggleButton id="TbtnToggleHideColumn" 
		label="MyLabel"
		screentip="MyScreenTip" 
		size="large" 
		onAction="TbtnToggleHideColumnIsClicked" 
            getPressed="GetPressed" 
            imageMso="ColumnSettingsMenu"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>

Paste this into a Standard Code Module of your Workbook....

Public gbHiddenState As Boolean

Dim MyRibbon As IRibbonUI

'Callback for customUI.******
Sub ToggleHideColumn(ribbon As IRibbonUI)
   Set MyRibbon = ribbon
   
   '--read previously saved value of toggle
   gbHiddenState = ThisWorkbook.Sheets("MyHiddenSheet").Range("A1").Value
   
   '--call procedure to hide/unhide column
   Call ChangeColumnState
End Sub

'Callback for TbtnToggleHideColumn onAction
Sub TbtnToggleHideColumnIsClicked(control As IRibbonControl, pressed As Boolean)
  '--switch state of global variable
  gbHiddenState = Not gbHiddenState
  
  '--save new state to worksheet
  ThisWorkbook.Sheets("MyHiddenSheet").Range("A1").Value = gbHiddenState
  
  '--call procedure to hide/unhide column
  Call ChangeColumnState
End Sub

'Callback for TbtnToggleHideColumn getPressed
Sub GetPressed(control As IRibbonControl, ByRef returnedVal)
  returnedVal = gbHiddenState
End Sub

Private Sub ChangeColumnState()
   '--example hides/unhides Column B of the first sheet
   ThisWorkbook.Sheets(1).Columns(2).Hidden = gbHiddenState

End Sub

 

[mmarzouk]

Jerry,

I tried your code in a blank workbook and it worked perfectly. I still have to implement it in my workbook. Thanks for your help. Really appreciate it.

Regards,
Mahmoud

 

[mmarzouk]

Jerry,

Really appreciate your input. The code worked greatly for what I need. The only thing that I have an issue is the following. I have a check (IF statement) to see whether the user is within the correct 'Sheet' in the 'Workbook'. If he is not, a msgbox will pop-up and notify the user in which sheet this toggle button is functional. And then it would Exit Sub. But if button was on, it switches off and vise versa without executing the code. Below is my onaction code

Public AutoNumState As Boolean

Sub itemnum2(control As IRibbonControl, pressed As Boolean)


'Checks if user is within the BOM Worksheet
If InStr(ActiveSheet.Name, "Bill") > 0 Then
AutoNumState = Not AutoNumState
Else


Msgbox "Invalid Action: Open 'Bill of Materials' Worksheet to enable this feature", vbExclamation
Exit Sub
End If


Call autonum


End Sub

Mahmoud

 

[Jerry Sullivan]

Mahmoud,

Rather than providing an error message, perhaps a better user-interface would be to disable the Ribbon Button unless the user is on the correct sheet.

Here are modifications to the Xml and code in post #2. Once again, try this in the test workbook first before trying to adapt to your workbook.

Add these two properties to the toggleButton

		tag="MyButton"
		getEnabled="GetEnabledMacro"

Add these procedures to the Standard Code Module with the other Ribbon Code.

Sub GetEnabledMacro(control As IRibbonControl, ByRef Enabled)
'--only enable controls on sheets with names matching this pattern
 If ActiveSheet.Name Like "*Bill*" Then
   Enabled = True
 Else
   Enabled = False
 End If
End Sub

Sub RefreshRibbon()
 If MyRibbon Is Nothing Then
   MsgBox "Pointer to Ribbon was lost. " & vbCr & _
      "Save and restart your workbook to reset."
 Else
   MyRibbon.Invalidate
 End If
End Sub

Paste this code into the Sheet Code Module of the Sheet "Bill of Materials"....

Private Sub Worksheet_Deactivate()
   Call RefreshRibbon
End Sub

Private Sub Worksheet_Activate()
   Call RefreshRibbon
End Sub

If you find the "Pointer to Ribbon was lost..." message arising, consider implementing this workaround by RoryA....
http://www.mrexcel.com/forum/excel-questions/518629-how-preserve-regain-id-my-custom-ribbon-ui.html