Toggle Button UI Ribbon

mmarzouk

New Member
Joined
Sep 22, 2014
Messages
15
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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 ).

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

New Member
Joined
Sep 22, 2014
Messages
15
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

New Member
Joined
Sep 22, 2014
Messages
15
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


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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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
HTML:
		tag="MyButton"
		getEnabled="GetEnabledMacro"


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

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

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,154
Members
410,775
Latest member
alal1030
Top