title: Using one datagridview across multiple tabs in tabcontrol
body: <p>I have a datagridview (datagridview1) on TabPage1 of TabControl1 which is one my 'Records' form. Currently I populate the datagridview with a loop that executes on the page load event and loads the data in from an excel file that it is an included resource. In the loop I also have tabs created and named for each sheet and sheet name that the excel file has. The two sheets I have in the excel file current are name "2012" and "2013". the first tab in the TabControl is named "2013" and the second tab dynamically created and named "2012" because I start the loop by setting the current year sheet to the first tab and the data in the 2013 sheet is read into the datagridview via my loop. What I need help doing is using the same datagridview on each new tab, but updating it to show the respective year's (sheet) data.</p>
  
  <p>Here's my code for Records Form</p>
  
  <pre><code>Imports System.Data.OleDb
  Imports Microsoft.Office.Interop.Excel
  
  Public Class Records
  
  Dim excel_app As Excel.Application
  Dim workbook As Excel.Workbook
  Dim sheet_name As String
  Dim sheet As Excel.Worksheet
  Dim ColumnCount, RowCount, TotalCellCount As Long
  Dim yearstamp As String = _
      DateTime.Now.ToString("yyyy")
  Dim exeDir As New IO.FileInfo(Reflection.Assembly.GetExecutingAssembly.FullName)
  Dim xlPath = IO.Path.Combine(exeDir.DirectoryName, "Records.xlsx")
  
  Sub PopulateDataGridView()
      'Loop through each column
      Dim cIndex As Integer = 0
      Dim rIndex As Integer = 0
      While cIndex &lt; DataGridView1.ColumnCount
  
          'Loop through and populate each row in column
          rIndex = 0
          While rIndex &lt; DataGridView1.RowCount
  
              If cIndex = 0 Then
                  'Set row header titles
                  DataGridView1.Rows.Item(rIndex).HeaderCell.Value = sheet.Range("A1").Offset(rIndex + 1, cIndex).Value()
  
                  DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
              End If
              If cIndex &gt; 0 Then
                  DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
              End If
  
              'Set column header title
              DataGridView1.Columns(cIndex).HeaderText = sheet.Range("A1").Offset(0, cIndex + 1).Value
  
              'Change last cell (Result) color Red or Green to represent positive gain or negative loss
              If rIndex = RowCount - 2 Then
                  If DataGridView1.Rows(rIndex).Cells(cIndex).Value &lt; 0 Then
                      DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Red
                      DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
                  End If
                  If DataGridView1.Rows(rIndex).Cells(cIndex).Value &gt; 0 Then
                      DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Green
                      DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
                  End If
                  If DataGridView1.Rows(rIndex).Cells(cIndex).Value = 0 Then
                      DataGridView1.Rows(rIndex).Cells(cIndex).Value = "Broke Even"
                  End If
  
              End If
  
              rIndex = rIndex + 1
  
          End While
  
          'Make column unsortable
          DataGridView1.Columns(cIndex).SortMode = DataGridViewColumnSortMode.NotSortable
  
  
          cIndex = cIndex + 1
  
      End While
  End Sub
  
  Private Sub Records_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      ' Get the Excel application object.
      excel_app = New Excel.Application
  
      ' Make Excel visible (optional).
      excel_app.Visible = False
  
      ' Open the workbook.
      workbook = excel_app.Workbooks.Open(xlPath)
  
      sheet_name = yearstamp
  
  
      'Adds tabs (if needed) and names each tab after respective excel file sheet
      Dim name As String
      For w As Integer = 1 To workbook.Sheets.Count
          name = workbook.Sheets(w).name
          If Not (TabControl1.TabPages.Count = workbook.Sheets.Count) Then
              TabPage1.Text = sheet_name
              ' Create the new tab page
              Dim tab As New TabPage(name)
              ' Add the tabpage to the existing TabCrontrol
              TabControl1.TabPages.Add(tab)
          End If
  
      Next w
  
  
      sheet = excel_app.Worksheets(sheet_name)
  
      ColumnCount = sheet.Range("A1").CurrentRegion.Columns.Count
      RowCount = sheet.Range("A1").CurrentRegion.Rows.Count
  
      DataGridView1.ColumnCount = ColumnCount - 1
      DataGridView1.RowCount = RowCount - 1
      DataGridView1.ColumnHeadersVisible = True
      DataGridView1.RowHeadersVisible = True
      TotalCellCount = DataGridView1.ColumnCount * DataGridView1.RowCount
  
      DataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.DisableResizing
      DataGridView1.AllowUserToResizeColumns = False
      DataGridView1.AllowUserToResizeRows = False
      DataGridView1.ReadOnly = True
  
      DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
  
      'Calls Loop to populate the datagridview
      PopulateDataGridView()
  
      DataGridView1.AutoResizeColumns()
  
      'Resize all Row Headers so user can see Row Titles without resizing
      DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)
  
  End Sub
  
  Private Sub dataGridView1_CellPainting(ByVal sender As System.Object, ByVal e As DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting
      If e.ColumnIndex &gt; -1 And e.RowIndex &gt; -1 Then
  
          If DataGridView1.Item(e.ColumnIndex, e.RowIndex).Value = " $0.00" Then
              DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.ForeColor = Color.White
              DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.BackColor = DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.ForeColor
              DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.SelectionForeColor = SystemColors.Highlight
              DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.SelectionBackColor = DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.SelectionForeColor
          End If
  
      End If
  
  
  
  End Sub
  </code></pre>
  
  <p>I'm editing this only to append the solution in case anyone else needs some guidance. The code below allows my datagridview to be carried to whichever tab is active and also updates the datagridview cells to show the data from the corresponding excel sheet's values. I hope this is able to help!</p>
  
  <pre><code>Imports System.Data.OleDb
  Imports Microsoft.Office.Interop.Excel
  
  Public Class Records
  
  Dim excel_app As Excel.Application
  Dim workbook As Excel.Workbook
  Dim sheet_name As String
  Dim sheet As Excel.Worksheet
  Dim ColumnCount, RowCount, TotalCellCount As Long
  Dim yearstamp As String = _
      DateTime.Now.ToString("yyyy")
  Dim exeDir As New IO.FileInfo(Reflection.Assembly.GetExecutingAssembly.FullName)
  Dim xlPath = IO.Path.Combine(exeDir.DirectoryName, "Records.xlsx")
  
  Sub PopulateDataGridView()
      'Loop through each column
      Dim cIndex As Integer = 0
      Dim rIndex As Integer = 0
      While cIndex &lt; DataGridView1.ColumnCount
  
          'Loop through and populate each row in column
          rIndex = 0
          While rIndex &lt; DataGridView1.RowCount
  
              If cIndex = 0 Then
                  'Set row header titles
                  DataGridView1.Rows.Item(rIndex).HeaderCell.Value = sheet.Range("A1").Offset(rIndex + 1, cIndex).Value()
  
                  DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
              End If
              If cIndex &gt; 0 Then
                  DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
              End If
  
              'Set column header title
              DataGridView1.Columns(cIndex).HeaderText = sheet.Range("A1").Offset(0, cIndex + 1).Value
  
              'Change last cell (Result) color Red or Green to represent positive gain or negative loss
              If rIndex = RowCount - 2 Then
                  If DataGridView1.Rows(rIndex).Cells(cIndex).Value &lt; 0 Then
                      DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Red
                      DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
                  End If
                  If DataGridView1.Rows(rIndex).Cells(cIndex).Value &gt; 0 Then
                      DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Green
                      DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
                  End If
                  If DataGridView1.Rows(rIndex).Cells(cIndex).Value = 0 Then
                      DataGridView1.Rows(rIndex).Cells(cIndex).Value = "Broke Even"
                  End If
  
              End If
  
              rIndex = rIndex + 1
  
          End While
  
          'Make column unsortable
          DataGridView1.Columns(cIndex).SortMode = DataGridViewColumnSortMode.NotSortable
  
  
          cIndex = cIndex + 1
  
      End While
  End Sub
  
  Private Sub Records_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      ' Get the Excel application object.
      excel_app = New Excel.Application
  
      ' Make Excel visible (optional).
      excel_app.Visible = False
  
      ' Open the workbook.
      workbook = excel_app.Workbooks.Open(xlPath)
  
      sheet_name = yearstamp
  
  
      'Adds tabs (if needed) and names each tab after respective excel file sheet
      Dim name As String
      For w As Integer = 1 To workbook.Sheets.Count
          name = workbook.Sheets(w).name
          If Not (TabControl1.TabPages.Count = workbook.Sheets.Count) Then
              TabPage1.Text = sheet_name
              ' Create the new tab page
              Dim tab As New TabPage(name)
              ' Add the tabpage to the existing TabCrontrol
              TabControl1.TabPages.Add(tab)
          End If
  
      Next w
  
  
      sheet = excel_app.Worksheets(sheet_name)
  
      ColumnCount = sheet.Range("A1").CurrentRegion.Columns.Count
      RowCount = sheet.Range("A1").CurrentRegion.Rows.Count
  
      DataGridView1.ColumnCount = ColumnCount - 1
      DataGridView1.RowCount = RowCount - 1
      DataGridView1.ColumnHeadersVisible = True
      DataGridView1.RowHeadersVisible = True
      TotalCellCount = DataGridView1.ColumnCount * DataGridView1.RowCount
  
      DataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.DisableResizing
      DataGridView1.AllowUserToResizeColumns = False
      DataGridView1.AllowUserToResizeRows = False
      DataGridView1.ReadOnly = True
  
      DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
  
      'Calls Loop to populate the datagridview
      PopulateDataGridView()
  
      DataGridView1.AutoResizeColumns()
  
      'Resize all Row Headers so user can see Row Titles without resizing
      DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)
  
  End Sub
  
  Private Sub dataGridView1_CellPainting(ByVal sender As System.Object, ByVal e As DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting
      If e.ColumnIndex &gt; -1 And e.RowIndex &gt; -1 Then
  
          If DataGridView1.Item(e.ColumnIndex, e.RowIndex).Value = " $0.00" Then
              DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.ForeColor = Color.White
              DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.BackColor = DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.ForeColor
              DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.SelectionForeColor = SystemColors.Highlight
              DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.SelectionBackColor = DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.SelectionForeColor
          End If
  
      End If
  
  End Sub
  
  Private Sub TabControl1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TabControl1.SelectedIndexChanged
      Dim tab As TabPage = Me.TabControl1.SelectedTab
      If ((Not tab Is Nothing) AndAlso (Not tab.Controls.Contains(Me.DataGridView1))) Then
          tab.Controls.Add(Me.DataGridView1)
      End If
      sheet_name = TabControl1.SelectedTab.Text
      sheet = excel_app.Worksheets(sheet_name)
      PopulateDataGridView()
  End Sub
  </code></pre>