none
Changing text on edit box on excel ribbon from VB.NET RRS feed

  • Question

  • Greeting community,

    This question is about customized single excel workbook, not about add-in that should work throughout all workbooks I open. If it is of any importance, it’s about VS2012 pro and corresponding Excel2010.

    For the purpose of question, let’s keep things simple. I opened new project of customized workbook type called ExcelWorkbook1, added new ribbon called Ribbon1, added new tab which took name tab2, added group, and one button called Button1, and edit box called EditBox1.

    Of course, when sub for click event is added, and you say that in it that text property of edit box should be set or get there is no problem, because event handler is in ribbons module.

    However, if you try to do something meaningful, because it’s about excel after all, it comes unsuccessful. I tried to fill edit box with address of selected cell. There is the code.

    Imports ExcelWorkbook1.Ribbon1
    Public Class Sheet1
    
        Private Sub Sheet1_SelectionChange(Target As Microsoft.Office.Interop.Excel.Range) Handles Me.SelectionChange
            Dim tb As New Ribbon1
            tb.EditBox1.Text = Target.Address
            MsgBox(Target.Address)
        End Sub
    
    
    End Class
    

    Note that I added msgbox line only to prove myself that event is actually fired. So VS doesn’t complain about anything, everything compiles, but edit box simply stays unchanged.

    Does anyone know how to change text or any other property on the excel ribbon using .NET?

    Thanks in advance.

    Monday, March 26, 2018 1:27 AM

Answers

  • Hello IvicaNesic,

    You could try to access the edit box at runtime using Globals Class.

    Here is the example.

    Private Sub Sheet1_SelectionChange(Target As Range) Handles Me.SelectionChange
            If Target.Text <> "" Then
                Globals.Ribbons.Ribbon1.EditBox1.Text = Target.Text
            End If
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by IvicaNesic Tuesday, March 27, 2018 7:57 PM
    Tuesday, March 27, 2018 2:18 AM

All replies

  • Hello IvicaNesic,

    You could try to access the edit box at runtime using Globals Class.

    Here is the example.

    Private Sub Sheet1_SelectionChange(Target As Range) Handles Me.SelectionChange
            If Target.Text <> "" Then
                Globals.Ribbons.Ribbon1.EditBox1.Text = Target.Text
            End If
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by IvicaNesic Tuesday, March 27, 2018 7:57 PM
    Tuesday, March 27, 2018 2:18 AM
  • Hi Terry,

    Thanks for replying. Yes it does the job, at least somewhere in the middle of it. You provided the solution.

    I was wondering what what’s wrong with my code. I couldn’t see it for hours until one friend of mine came by, and realized instantly. I forgot to initialize created instance, should have written: tb=globals.ribbons.ribbon1 or tb=globals.ribbons(0)

    Now for the sake of future readers of this thread, before moderator closes it, I’m obligated to warn them about something. If you have to update the ribbon all the time, and you don’t want to type globals.ribbon.ribbon1 every single time, you can create a public variable for your ribbon of its strong type in one module, but you cannot initialize it in workbook’s startup event, because ribbon itself is not initialized at that moment. Best place to initialize it is in its own load event. I hope this may save hours of trying for someone else.

    Thanks again Terry.

    Tuesday, March 27, 2018 7:59 PM