CODEWITHSUNDEEP
excelvbatogglebutton
Subh
Subh

Reputation: 21

ToggleButton status

I am using Excel Ribbon toggleButton. Based on it's on/off status I do some processing in the Excel cell in the SheetSelectionChange event. I am not able to get the status of the toggleButton from the cell without accessing my settings file which is an unnecessary overhead. Let me present a snippet of the code.

Global variable could be an option. But can I have something better?

Private Sub mExcel_SheetSelectionChange(ByVal theSheet As Object, ByVal Target As Range)
'++
' Call the C++ Code.
'--
'If (isbtnpressed) Then  ** <-- This is where i want to check the ToglgeButton status
' I don't want to read the settings file for every cell click. **
    Dim flagVal As Boolean
    'IsValidData goes to C++ for validation
    If IsValidData(Target.Application.ActiveCell) Then
        LoadMyListForm Target.Application.ActiveCell
    End If
'End If

End Sub

onAction Events (two)

Public Sub IxlRibbonGetButtonStateByTag(theControl As IRibbonControl, ByRef isbtnpressed)
'++
'Get ShowOnClick button state, callback for getPressed
'
' Arguments:
'    theControl - The ribbon control that fired the OnAction request.
'    IsPressed - Button status
'--
On Error Resume Next
isbtnpressed = (ReadFromSettingsFile (theControl.Tag, "0") <> "0")

End Sub

And

Public Sub IxlRibbonToggleSettingByTag(control As IRibbonControl, IsPressed As Boolean)
'++
'callback for onAction
'
' Arguments:
'    theControl - The ribbon control that fired the OnAction request.
'    IsPressed - Button status
'--
On Error Resume Next

If IsPressed Then
    WriteToSettingsFile control.Tag, "1"
Else
    WriteToSettingsFile control.Tag, "0"
End If

End Sub

the UI XML looks like

            <toggleButton id="tb_IxlToggleButton"
                label="My Label"
                tag="MyLabel"
                getPressed="IxlRibbonGetButtonStateByTag"
                onAction="IxlRibbonToggleSettingByTag"
                imageMso="ControlToggleButton" />
                <button id="MyLabel"
                    tag="MyLabel"
                    onAction="IxlRibbonToggleSettingByTag"/>

I have gone ahead and used global variable as the way out as has been suggested in the comment section by @Pᴇʜ

Upvotes: 0

Views: 446

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

If Public variable not wished, a convenient way would be to keep and reed its status in Registry. The Toggle button can also be set when the application starts, according to the memorized status:

  1. Create some constants at the module level:

    Public const MyApp as string = "MyApp Name"
Public const MyAppSett as string = "MyApp Settings"
Public const TglVal as string = "Toggle_Val"

  2. Transform your event code in:

    Public Sub IxlRibbonToggleSettingByTag(control As IRibbonControl, IsPressed As Boolean)
 SaveSetting MyApp, MyAppSett, TglVal, cStr(IsPressed)
End Sub

  3. Create a function to rapidly return the status (reading in Registry):

    Function TglStatus() As Boolean
    Dim strToggle As String
    strToggle = GetSetting(MyApp, MyAppSett, TglVal, "Nothing...")
   If strToggle <> "Nothing..." Then
        TglStatus = CBool(strToggle)
   Else
        TglStatus = False 'for the case before memorizing of something...
   End If
End Function

  4. And use it like this:

    If TglStatus Then
     'do something for true
Else
     'do something else for false
End If

Upvotes: 1

Related Questions