Keep the previous toggle button state the next time you open it

[Nam Vũ]

Sorry my english is not good. I have a question to ask. I have made a Ribbon like the image below with a toggle button (specifically the "Phạm vi nổi bật"), I want when I close Excel and reopen, this button will keep the state of the previous activation (If the previous time is enabled, the next time you open Excel this button will also be enabled and vice versa)

Below is the code that I wrote:

public void BTN_ReadingLayout(IRibbonControl control, bool pressed)
        {
            Excel.Application xlApp = (Excel.Application)ExcelDnaUtil.Application;

            try
            {
                foreach (Workbook wb in xlApp.Workbooks)
                {
                    foreach (Worksheet ws in wb.Worksheets)
                    {
                        Range rng = ws.Range[ws.Cells[1, 1], ws.Cells[ws.Rows.Count, ws.Columns.Count]];

                        if (pressed)
                        {
                            Excel.FormatConditions formatConditions = rng.FormatConditions;

                            if (Properties.Settings.Default.EC_ReadingLayout_Option == 1)
                            {
                                Excel.FormatCondition formatCondition = formatConditions.Add(XlFormatConditionType.xlExpression, Formula1: "=ROW()=CELL(\"ROW\")");
                                formatCondition.Interior.Color = Properties.Settings.Default.EC_ReadingLayout_Color;
                            }
                            else if (Properties.Settings.Default.EC_ReadingLayout_Option == 2)
                            {
                                Excel.FormatCondition formatCondition = formatConditions.Add(XlFormatConditionType.xlExpression, Formula1: "=COLUMN()=CELL(\"COL\")");
                                formatCondition.Interior.Color = Properties.Settings.Default.EC_ReadingLayout_Color;
                            }
                            else if (Properties.Settings.Default.EC_ReadingLayout_Option == 3)
                            {
                                Excel.FormatCondition formatCondition = formatConditions.Add(XlFormatConditionType.xlExpression, Formula1: "=OR(ROW()=CELL(\"ROW\"),COLUMN()=CELL(\"COL\"))");
                                formatCondition.Interior.Color = Properties.Settings.Default.EC_ReadingLayout_Color;
                            }
                            else
                            {

                            }

                            ws.SelectionChange += new DocEvents_SelectionChangeEventHandler(Worksheet_SelectionChange_ReadingLayout);
                        }
                        else
                        {
                            rng.FormatConditions.Delete();
                            ws.SelectionChange -= new DocEvents_SelectionChangeEventHandler(Worksheet_SelectionChange_ReadingLayout);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, xltitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void Worksheet_SelectionChange_ReadingLayout(Range target)
        {
            Excel.Application xlApp = (Excel.Application)ExcelDnaUtil.Application;
            xlApp.Calculation = XlCalculation.xlCalculationAutomatic;
            if (Properties.Settings.Default.EC_ReadingLayout_Option == 4)
            {
                target.Worksheet.Cells.FormatConditions.Delete();
                target.FormatConditions.Add(XlFormatConditionType.xlExpression, Formula1: "TRUE");
                target.FormatConditions[1].Interior.Color = Properties.Settings.Default.EC_ReadingLayout_Color;
            }
            else
            {
                target.Calculate();
            }    
        }

and XML Ribbon code:

<toggleButton id='buttonReadingLayout' label='Phạm vi nổi bật' onAction='BTN_ReadingLayout' getImage='GetImage' size='large' screentip='Phạm vi nổi bật' supertip='Tự động làm nổi bật phạm vi đang được chọn'/>

[Govert van Drimmelen]

I asked ChatGPT to help with this one, and the answer seemed reasonable (though I didn't check the code).

I post my question and the reply below.

Write back if you have any follow-up questions that I can ask the chat bot.

-Govert

----------

Q: I am developing an Office add-in in C#, with a Ribbon extension that has a toggleButton (not using VSTO). How should the toggleButton markup and handlers look, if I want the toggle state to be persistent between Excel sessions?

A: To create a persistent toggleButton in a C# Office add-in with a Ribbon extension, you'll need to save the toggle state in a persistent storage, like the Windows Registry or a configuration file. In this example, we'll use the Windows Registry to store the toggle state.

Here's the XML markup for your Ribbon:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon>
    <tabs>
      <tab id="MyTab" label="My Tab">
        <group id="MyGroup" label="My Group">
          <toggleButton id="myToggleButton" label="Toggle Button" onAction="OnToggleButtonAction" getPressed="GetToggleButtonPressed" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Now, create a C# class that implements the IRibbonExtensibility interface and the required callback methods:

using System;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;
using Microsoft.Office.Core;
using Microsoft.Win32;

namespace MyOfficeAddin
{
    [ComVisible(true)]
    public class Ribbon : IRibbonExtensibility
    {
        private IRibbonUI ribbon;

        public Ribbon()
        {
        }

        public string GetCustomUI(string ribbonID)
        {
            return GetResourceText("MyOfficeAddin.Ribbon.xml");
        }

        public void OnToggleButtonAction(IRibbonControl control, bool isPressed)
        {
            SaveToggleButtonState(isPressed);
        }

        public bool GetToggleButtonPressed(IRibbonControl control)
        {
            return GetToggleButtonState();
        }

        private static string GetResourceText(string resourceName)
        {
            Assembly asm = Assembly.GetExecutingAssembly();
            string[] resourceNames = asm.GetManifestResourceNames();
            for (int i = 0; i < resourceNames.Length; ++i)
            {
                if (string.Compare(resourceName, resourceNames[i], StringComparison.OrdinalIgnoreCase) == 0)
                {
                    using (StreamReader resourceReader = new StreamReader(asm.GetManifestResourceStream(resourceNames[i])))
                    {
                        if (resourceReader != null)
                        {
                            return resourceReader.ReadToEnd();
                        }
                    }
                }
            }
            return null;
        }

        private void SaveToggleButtonState(bool isPressed)
        {
            RegistryKey key = Registry.CurrentUser.CreateSubKey(@"SOFTWARE\MyCompany\MyOfficeAddin");
            key.SetValue("ToggleState", isPressed, RegistryValueKind.DWord);
            key.Close();
        }

        private bool GetToggleButtonState()
        {
            RegistryKey key = Registry.CurrentUser.OpenSubKey(@"SOFTWARE\MyCompany\MyOfficeAddin");
            if (key == null)
            {
                return false; // Default state
            }

            object stateValue = key.GetValue("ToggleState");
            key.Close();

            if (stateValue == null)
            {
                return false; // Default state
            }

            return ((int)stateValue) == 1;
        }
    }
}

In this example, the SaveToggleButtonState method saves the toggle state to the Windows Registry, and the GetToggleButtonState method reads the state from the Registry. The GetToggleButtonPressed callback initializes the toggleButton state when the Ribbon is loaded, and the OnToggleButtonAction callback saves the new state when the button is toggled. This will ensure the state is persistent between Excel sessions.

[Nam Vũ]

But I use Excel DNA to create this add-in, can you help me?

Vào lúc 18:09:41 UTC+7 ngày Thứ Bảy, 15 tháng 4, 2023, Govert van Drimmelen đã viết:

[Govert van Drimmelen]

What I take from the ChatGPT answer:

• You can use the Windows Registry to persist the state.
• To internally manage the toggleButton state, you add a callback for ‘getPressed’ and implement a method in your ribbon implementation class to process it.
• You either read the state from the registry every time that getPressed is called, or you can keep a local variable with the value of this underlying flag.
• When the button is pressed, you need to record the new state inside your onAction handler.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/464992c5-2609-4128-9bdb-3130d1ddd74fn%40googlegroups.com.

[Nam Vũ]

I have used the Registry and the results are correct, but the BTN_ReadingLayout method doesn't work automatically. I guess maybe the pressed variable is undefined. Can you help me with this situation?

Vào lúc 23:44:12 UTC+7 ngày Thứ Bảy, 15 tháng 4, 2023, Govert van Drimmelen đã viết: