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

Capture.JPG