Can Not Seem to Get If and ElesIf Statement to Work Correctly

[rorobear]

Hello Everyone,

I can not seem to get my If and Elseif statement to work correctly, and could really use some help. Here is what I’m trying to do:

If no selection is made from the down, a message appears:
MsgBox "Select a Worksheet From the Dropdown", vbInformation, " Worksheet Selection"
so User selects a worksheet
If no selection is made from the Listbox another message appears:
MsgBox "Now Make a Selection from the Listbox", vbInformation, "ListBox Selection"
so User selects form ListBox
And finally, once a selection is made from the ListBox, the code runs as intended.
The workbook is attached.



Private Sub DeleteSelection_Click()
Dim lngListBoxIndex As Long
Dim strRecName As String

If Me.ComboBox1.Value = "" Then
MsgBox "Select a Worksheet From the Dropdown", vbInformation, "Select Worksheet"
Exit Sub
End If

If Frm_ViewData.ListBox1.Selected(0) = True Then
MsgBox "Now Make a Selection from the Listbox."
Else: End If

With Me.ListBox1
For lngListBoxIndex = 0 To .ListCount - 1
If .Selected(lngListBoxIndex) = True Then
strRecName = .List(.ListIndex, 0)
If MsgBox("Are you sure you want to delete the record?", 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
End If
Exit For
End If
Next lngListBoxIndex
End With
Call ComboBox1_change '---> update ListBox after the row is deleted
MsgBox "Record has Been Deleted", vbInformation, "Delete Record"
End Sub

[Paul_Hossler]

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

[rorobear]

hi Paul,

This looks great, i especially like how you incorporated the selection name in the delete function. As for the pop msgbox (MsgBox "Now Make a Selection from the Listbox", vbInformation, "ListBox Selection"), if it were just one worksheet, cool but i think as more sheets are added and large data set get inputted, that might get tiresome. What if we were to put in a label that maybe appears across the top when the combobox change is made? again, thank you for the support.

roro

[Paul_Hossler]

hi Paul,
that might get tiresome


I left it in because you had it there; not really needed since the combobox shows the ws name

Here's something to think about

30528