How to create a fully working Toggle Button?

Add-in Express™ Support Service
That's what is more important than anything else

How to create a fully working Toggle Button?
 
Leon Lai Kan




Posts: 200
 Joined: 2018-12-20
Hello, Andrei

I wish to create a fully-working Toggle Button in the Excel Ribbon, like the one that we have in the Excel Menu Review > Show All Comments.

When we click on that button, all Comments are displayed.
When we click again, all Comments are hidden.

This is what I did:


Step 1:

I created 2 separate buttons on the Ribbon captioned Show Comments and Hide Comments respectively.

Here is a simplified code for Show Comments.
The code for Hide Comments is very similar (cel.Comment.Visible = False) 


Private Sub AdxRibbonButton2_OnClick(ByVal sender As System.Object, _
                                     ByVal control As AddinExpress.MSO.IRibbonControl,_
                                     ByVal pressed As System.Boolean) _
                                     Handles AdxRibbonButton2.OnClick

    Dim SelectedCmts As Excel.Range
    Dim cel As Excel.Range

    SelectedCmts = TryCast(ExcelApp.Selection, Excel.Range)._
                                 SpecialCells(Excel.XlCellType.xlCellTypeComments)

    For Each cel In SelectedCmts
        cel.Comment.Visible = True
    Next

End Sub



Step 2:

I now try to create a single Toggle Button that will replace the 2 separate buttons above.

So, I add a button captioned " Show / Hide all Comments"

I set ToggleButton = True.

I don't know how to proceed further...

red or refer me to the relevant place in the ADX manual for a solution?

I studied your ADX Manual Chapter Office Ribbon UI Components > Updating Ribbon Controls at Run Time but cannot apply the principles to my problem.

Thanks
Leon


red
red

By Trial and error, I found this solution which appears to work.
I am not using the PropertyChanging event, nor am I writing code to get the state of the Toggle Button.
So, I am not sure I am doing things the right way. 

Private Sub AdxRibbonButton4_OnClick(ByVal sender As System.Object, _
                                        ByVal control As AddinExpress.MSO.IRibbonControl, _
                                        ByVal pressed As System.Boolean) _
                                        Handles AdxRibbonButton4.OnClick

        If pressed Then
           Dim SelectedCmts As Excel.Range
            Dim cel As Excel.Range
            SelectedCmts = TryCast(ExcelApp.Selection, Excel.Range)._
                                 SpecialCells(Excel.XlCellType.xlCellTypeComments)
            For Each cel In SelectedCmts
                cel.Comment.Visible = True
            Next


        Else
            Dim SelectedCmts As Excel.Range
            Dim cel As Excel.Range
            SelectedCmts = TryCast(ExcelApp.Selection, Excel.Range)._
                               SpecialCells(Excel.XlCellType.xlCellTypeComments)
            For Each cel In SelectedCmts
                cel.Comment.Visible = False
            Next
        End If
Posted 04 May, 2019 05:39:33 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello Leon,

Leon Lai Kan writes:
By Trial and error, I found this solution which appears to work.


I'd try to use this way first of all. I believe that since it works, this is the way to follow.

Still, I'd like you to pay attention to releasing COM objects in your code. Specifically, you shouldn't use For Each (use For instead and release every member of the collection), you should release the object returned by ExcelApp.Selection whether it is a Range or any other object (e.g. Chart), you should release the object returned by cel.Comment as it is a COM object as well. I strongly suggest that you check section Releasing COM objects at https://www.add-in-express.com/docs/net-office-tips.php#releasing.


Andrei Smolin
Add-in Express Team Leader
Posted 08 May, 2019 05:56:41 Top
Leon Lai Kan




Posts: 200
 Joined: 2018-12-20
Hi, Andrei

Thanks a lot for your reply and your very informative explanation.

I was not aware how bad For Each could be.

I'll modify my code according to your suggestion.


Best Regards,
Leon
Posted 08 May, 2019 06:19:57 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
You are welcome!


Andrei Smolin
Add-in Express Team Leader
Posted 08 May, 2019 06:35:11 Top