title: VB.NET Addin How do I select current worksheet
body: <p>I'm trying to create an Excel VSTO Addin with VS2015 for a handful of VBA macros that I have.  I could do this using VBA, but want to get my feet wet in VB.NET.  The problem I'm facing though is how to get the active worksheet and assign it to a variable.  Then, I need to pass in a range that I converted to text previous.  </p>
  
  <p>The macro essentially enables a user to compare two lists of data by selecting the ranges and then highlighting items that appear in both lists.</p>
  
  <p><a href="https://i.stack.imgur.com/xomFH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xomFH.png" alt="enter image description here"></a></p>
  
  <p>Here's the code that executes when a user tries to select the range (works fine)</p>
  
  <pre><code>    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
      Try
          Me.Hide()
          Dim R As Microsoft.Office.Interop.Excel.Range
          Dim A As String
          'note how the Application.InputBox brings up different settings to just InputBox
          R = Globals.ThisAddIn.Application.InputBox(Prompt:="Please select the reference range", Type:=8)
          A = R.Address
          Me.TextBox1.Text = A
          Me.Show()
          Me.TopMost = True
          Exit Sub
  
      Catch
          MsgBox("Please select a valid range!")
          Me.Show()
      End Try
  End Sub
  </code></pre>
  
  <p>Here's the code behind the "Search and Highlight" button, that should compare the two ranges and highlight duplicates.  However, when I run this it highlights cells in a different workbook, presumable the first one that I have open.  How do I make sure it's opening the current workbook that I'm operating in?</p>
  
  <pre><code>    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  
      Dim xlApp As New Excel.Application
      Dim ws As Excel.Worksheet
      Dim sheetName As String
      Dim cell_1 As Excel.Range
      Dim cell_2 As Excel.Range
      Dim rng_1 As Excel.Range
      Dim rng_2 As Excel.Range
  
      xlApp = GetObject(, "Excel.Application")
      ws = xlApp.ActiveSheet
      sheetName = ws.Name
  
      rng_1 = ws.Range(Me.TextBox2.Text)
      rng_2 = ws.Range(Me.TextBox1.Text)
  
      For Each cell_1 In rng_1
          For Each cell_2 In rng_2
              If cell_2.Text = cell_1.Text Then
                  cell_2.Interior.ColorIndex = 6
  
              End If
          Next cell_2
      Next cell_1
  
      Me.Close()
  
  End Sub
  </code></pre>