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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,110
Messages
5,835,439
Members
430,357
Latest member
RadStorm20

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
Top