none
Visual Studio Excel AddIn - VB.Net - Help Selecting Range As All Currently Selected Cells RRS feed

  • Question

  • Hello - I am building an Excel Addin using Visual Studio and am having trouble translating my VBA code that works as an Excel macro to VB.Net code. One item I cannot figure out is how to run a macro via the addin based on which cells are selected in Excel. Please see my code below. This code currently works and executes on cell A1. Can you please help me change it to work on all selected cells (not A1)?


    Imports Microsoft.Office.Tools.Ribbon
    Imports Microsoft.Office.Interop.Excel

    Public Class Ribbon1

        Private Sub Ribbon1_Load(ByVal sender As System.Object, ByVal e As RibbonUIEventArgs) Handles MyBase.Load

        End Sub

        Private Sub BtnFontToggle_Click(sender As Object, e As RibbonControlEventArgs) Handles BtnFontToggle.Click

                Dim ActiveWorksheet As Microsoft.Office.Interop.Excel.Worksheet =
                Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1)

                Dim Worksheet As Microsoft.Office.Tools.Excel.Worksheet =
                Globals.Factory.GetVstoObject(ActiveWorksheet)

            Dim CurrentColor
            Dim Selection As Excel.Range = Worksheet.Range("A1")

    '******* Instead of selecting cell A1, I want the code to select all active cells (those that are currently highlighted)*****

            'What is the current font color?
            CurrentColor = Selection.Font.ColorIndex

            'Change font color based on current font color
            'Order Black, Blue, Green, Red
            If CurrentColor = 1 Then
                Selection.Font.ColorIndex = 5
            Else
                If CurrentColor = 5 Then
                    Selection.Font.ColorIndex = 10
                Else
                    If CurrentColor = 10 Then
                        Selection.Font.ColorIndex = 3
                    Else
                        If CurrentColor = 3 Then
                            Selection.Font.ColorIndex = 1
                        Else
                            Selection.Font.ColorIndex = 1
                        End If
                    End If
                End If
            End If
        End Sub
    End Class

    Saturday, June 2, 2018 5:39 PM

Answers

  • Hello MichaelVBA,

    I would suggest you use Application.ActiveCell to get current selected single cell. Or you could us Application.Selection to get current whole selected range. However, please note if the whole range cells contains different font color index, the Selection.Font.ColorIndex will return null.

    See below simply code for a reference.

            Dim ActiveApplication As Microsoft.Office.Interop.Excel.Application = Globals.ThisAddIn.Application
            Dim ActiveWorksheet As Microsoft.Office.Interop.Excel.Worksheet =
                Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1)
    
            Dim CurrentColor As Integer
            Dim Selection As Microsoft.Office.Interop.Excel.Range = ActiveApplication.ActiveCell
    
            CurrentColor = Selection.Font.ColorIndex
    

    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.

    • Proposed as answer by Terry Xu - MSFT Wednesday, June 6, 2018 6:17 AM
    • Marked as answer by MichaelVBA Friday, June 8, 2018 2:41 AM
    Monday, June 4, 2018 3:25 AM

All replies

  • Hello MichaelVBA,

    I would suggest you use Application.ActiveCell to get current selected single cell. Or you could us Application.Selection to get current whole selected range. However, please note if the whole range cells contains different font color index, the Selection.Font.ColorIndex will return null.

    See below simply code for a reference.

            Dim ActiveApplication As Microsoft.Office.Interop.Excel.Application = Globals.ThisAddIn.Application
            Dim ActiveWorksheet As Microsoft.Office.Interop.Excel.Worksheet =
                Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1)
    
            Dim CurrentColor As Integer
            Dim Selection As Microsoft.Office.Interop.Excel.Range = ActiveApplication.ActiveCell
    
            CurrentColor = Selection.Font.ColorIndex
    

    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.

    • Proposed as answer by Terry Xu - MSFT Wednesday, June 6, 2018 6:17 AM
    • Marked as answer by MichaelVBA Friday, June 8, 2018 2:41 AM
    Monday, June 4, 2018 3:25 AM
  • Hello MichaelVBA,

    Has your original issue been resolved? If it is, I would suggest you mark the helpful reply or provide your solution and mark it to close the thread. If not, please feel free to let us know the issue has any updates.

    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.

    Wednesday, June 6, 2018 6:18 AM