Requiring Listbox Selection for userform

Question

Private Sub CommandButton1_Click()
whichSheet = ListBox1.Value
Dim n As Integer
Do
n = n + 1
ListBox1.AddItem Sheets(n).Name
Loop Until n = Worksheets.Count

Worksheets(whichSheet).Activate
Dim lastrow
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

lastrow = lastrow + 1
Cells(lastrow, 1) = TextBox1
answer = MsgBox("Are you sure you want to add the record?", vbYesNo + vbQuestion, "Add Record")
If answer = vbYes Then
Cells(lastrow, 1) = TextBox1.Text
Cells(lastrow, 2) = TextBox2.Text
Cells(lastrow, 3) = TextBox3.Value
Cells(lastrow, 4) = TextBox4.Text
Cells(lastrow, 5) = TextBox5.Text
Cells(lastrow, 6) = TextBox6.Text
Else
    Cells(lastrow, 1) = ""
    Exit Sub
End If

End Sub


Private Sub UserForm_Initialize()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ListBox1.AddItem (ws.Name)
    Next ws
End Sub

Hello guys, I am using the code above which works perfectly for my userform. The only issue I'm having is that when someone doesn't pick a choice from the listbox1 and submits the info, the "Runtime Error" window pops up. I would like to stop that from happening by making a message box appear that tells users to make a choice--->click ok on the message box---> and then resume. If the user doesn't select an option still the same procedure should occur every time. If you have any ideas, I would love to try them out. Thanks.

Answer 1

My preferred way of handling this situation is to disable CommandButton1 if nothing in the ListBox1 is selected. Or, in other words, enable the button when something is selected.

Private Sub UserForm_Initialize()
Dim ws As Worksheet
    CommandButton1.Enabled = False  ' <--- here.
    For Each ws In ThisWorkbook.Worksheets
        ListBox1.AddItem (ws.Name)
    Next ws
End Sub 

I don't have Excel open at the moment, but you want a corresponding listbox event to enable/disable the button. The following is an example and untested.

Private Sub ListBox1_Change()
    CommandButton1.Enabled = ListBox1.ListIndex <> -1
End Sub

Another approach is to check to see if something has been selected when you enter the CommandButton1_Click routine and handle it there. But I prefer to prevent bad user input in the first place - less complicated.