Hi luckydead,

Since we are not defining any column in the DataGridView, the inserted column is considered as 0 Index.

So you need to check the condition with Cell value i.e. the Text of the ButtonColumn.

Please refer below modified code.

Namespace

Imports ClosedXML.Excel

Code

UserControl1

Private Sub UserControl1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim ds As DataSet = GetDataTableFromExcel()
    For Each dt As DataTable In ds.Tables
        Dim dgv As DataGridView = New DataGridView()
        dgv.DefaultCellStyle.BackColor = Color.White
        dgv.DefaultCellStyle.ForeColor = Color.Black
        dgv.DefaultCellStyle.SelectionBackColor = Color.FromArgb(0, 177, 89)
        dgv.DefaultCellStyle.SelectionForeColor = Color.White
        dgv.RowHeadersVisible = True
        dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        dgv.Anchor = AnchorStyles.Top And AnchorStyles.Left And AnchorStyles.Right
        dgv.ScrollBars = ScrollBars.Both
        dgv.Dock = DockStyle.Fill
        dgv.DataSource = dt
        dgv.AllowUserToAddRows = True
        AddHandler dgv.CellBeginEdit, AddressOf DataGridView_CellBeginEdit
        AddHandler dgv.EditingControlShowing, AddressOf OnEditingControlShowing
        AddHandler dgv.CellContentClick, AddressOf DataGridView_CellContentClick
        Dim tabPage As TabPage = New TabPage(dt.TableName)
        tabPage.Controls.Add(dgv)
        TabControl1.TabPages.Add(tabPage)
        Dim buttonColumn As DataGridViewButtonColumn = New DataGridViewButtonColumn() With {
            .HeaderText = "Delete",
            .Width = 10,
            .Name = "buttonDelete",
            .Text = "Delete",
            .UseColumnTextForButtonValue = True
        }

        dgv.Columns.Insert(2, buttonColumn)
    Next
End Sub

Private Sub OnEditingControlShowing(ByVal sender As Object, ByVal e As DataGridViewEditingControlShowingEventArgs)
    AddHandler CType(e.Control, DataGridViewTextBoxEditingControl).KeyUp, New KeyEventHandler(AddressOf OnTextBoxKeyUp)
End Sub

Private Sub DataGridView_CellContentClick(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs)
    Dim dgv As DataGridView = TryCast(sender, DataGridView)
    Dim row As DataGridViewRow = dgv.Rows(e.RowIndex)
    If dgv.CurrentCell.EditedFormattedValue = "Delete" Then
        If MessageBox.Show(String.Format("Do you want to delete WorkCenter: {0}? ", row.Cells(1).Value), "Confirmation", MessageBoxButtons.YesNo) = DialogResult.Yes Then
            MessageBox.Show("Deleted")
        End If
    End If
End Sub

Private Sub OnTextBoxKeyUp(ByVal sender As Object, ByVal e As KeyEventArgs)
    Dim index As Integer = CType((sender), DataGridViewTextBoxEditingControl).EditingControlRowIndex
    Dim dgv As DataGridView = CType((sender), DataGridViewTextBoxEditingControl).EditingControlDataGridView
    If Not String.IsNullOrEmpty(CType((sender), DataGridViewTextBoxEditingControl).EditingControlFormattedValue.ToString()) Then
        If index > 0 Then
            dgv.Rows(index).Cells(2).Value = dgv.Rows(index - 1).Cells(2).Value
        End If
    End If
End Sub

Private Sub DataGridView_CellBeginEdit(ByVal sender As Object, ByVal e As DataGridViewCellCancelEventArgs)
    If e.ColumnIndex = 2 Then
        e.Cancel = True
    End If
End Sub

Private Function GetDataTableFromExcel() As DataSet
    Dim ds As DataSet = New DataSet()
    Dim filePath As String = My.Application.Info.DirectoryPath & "\Lines\Lines.xlsx"
    Using workBook As XLWorkbook = New XLWorkbook(filePath)

        For Each workSheet As IXLWorksheet In workBook.Worksheets
            Dim firstRow As Boolean = True
            Dim dt As DataTable = New DataTable()
            Dim worksheet1 As IXLWorksheet = workBook.Worksheet(workSheet.Name)
            dt.TableName = workSheet.Name
            For Each row As IXLRow In workSheet.Rows()

                If firstRow Then

                    For Each cell As IXLCell In row.Cells()
                        dt.Columns.Add(cell.Value.ToString())
                    Next

                    firstRow = False
                Else
                    dt.Rows.Add()
                    Dim i As Integer = 0
                    For Each cell As IXLCell In row.Cells()
                        For k As Integer = 0 To dt.Columns.Count - 1
                            If dt.Columns(k).ColumnName.ToLower() <> cell.Value.ToString().ToLower() Then
                                dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                            Else
                                Exit For
                            End If
                        Next

                        i += 1
                    Next
                End If
            Next

            ds.Tables.Add(dt)
        Next

        Return ds
    End Using
End Function

Default(Form1)

Public Sub AddUserControl2(userControl As UserControl)
    userControl.Dock = DockStyle.Fill
    Panel1.Controls.Clear()
    Panel1.Controls.Add(userControl)
    userControl.BringToFront()
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    AddUserControl2(New UserControl1())
End Sub