I think this is what you really wanted to do
Option Explicit
Private Sub btnExit_Click() ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Me.Hide
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim i As Integer
Dim labelCount As Integer
labelCount = 10
For i = 1 To labelCount
Controls("label" & i).Caption = Sheets("HMMWV 1A").Cells(2, i + 1).Value
Next
For i = 2 To Sheets.Count
Me.ComboBox1.AddItem Sheets(i).Name
Next i
Me.ListBox1.ColumnWidths = "5;55;160;80;80;80;80;80;80;80;80"
End Sub
Private Sub ComboBox1_change()
'Sheets(ComboBox1.Value).Activate
LoadComboBox1 ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
MsgBox "Now Make a Selection from the Listbox", vbInformation, "ListBox Selection"
End Sub
Private Sub LoadComboBox1() ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Dim LR As Long, LC As Long
Me.ListBox1.Clear
If Me.ComboBox1.ListIndex = -1 Then Exit Sub
With Sheets(Me.ComboBox1.Value)
LR = .Range("B" & Rows.Count).End(xlUp).Row
LC = .ListObjects(1).ListColumns.Count + 1
With .Range("A3", .Cells(LR, LC))
Me.ListBox1.ColumnCount = .Columns.Count
Me.ListBox1.List = .Value
End With
End With
End Sub
Private Sub ListBox1_Click()
Dim i As Long
For i = 1 To 10
Me.Controls("TextBox" & i) = Me.ListBox1.List(Me.ListBox1.ListIndex, i)
Next i
End Sub
Private Sub UpdateData_Click()
Dim rw As Long
If Me.ComboBox1.Value = "" Then
MsgBox "Select a worksheet from the dropdown", vbInformation, "Select Worksheet"
Exit Sub
End If
rw = ListBox1.ListIndex + 1
With Sheets(ComboBox1.Value).ListObjects(1)
.DataBodyRange(rw, 1).Resize(, 10) = Array(TextBox1, TextBox2, TextBox3, TextBox4, TextBox5, TextBox6, TextBox7, TextBox8, TextBox9, TextBox10)
Call ComboBox1_change
MsgBox "Data has been Updated!", vbInformation, "Update Data"
End With
'Unload Me
Reset
End Sub
Private Sub DeleteSelection_Click() ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Lots
Dim lngListBoxIndex As Long
Dim strRecName As String
If Me.ComboBox1.Value = "" Then
MsgBox "Select a Worksheet From the Dropdown", vbInformation, "Worksheet Selection"
Exit Sub
End If
With Me.ListBox1
For lngListBoxIndex = 0 To .ListCount - 1
If .Selected(lngListBoxIndex) Then
strRecName = .List(.ListIndex, 0)
If MsgBox("Are you sure you want to delete '" & .List(.ListIndex, 2) & "'?", vbQuestion + vbYesNo, "Delete Record") = vbYes Then
Application.ScreenUpdating = False
'--- >modified code in order for it to delete based on item selected in the ComboBox1
Sheets(ComboBox1.Value).ListObjects(1).DataBodyRange(lngListBoxIndex + 1, 1).EntireRow.Delete
Application.ScreenUpdating = True
LoadComboBox1
MsgBox "Record has Been Deleted", vbInformation, "Delete Record"
End If
Exit For
End If
Next lngListBoxIndex
End With
End Sub