Userform vba to use first option in combobox if nothing selected

[Skrej]

I'm using a userform to enter and track some student data. As part of that userform, there's a section to add new students.

To add new students, my code so far requires a first and last name via text boxes, then has a number of combo boxes to add additional data. The text boxes with names are required, but it's not necessary to choose from all or even any of the combo boxes before the form adds a new student to the spreadsheet.

However, what I would like to happen is that if the user doesn't make a selection from the combo boxes, it will select the first list option which is a series of dashes as fillers versus just writing blank cells. If the user does decide to make selections in some or all of the combo boxes, then those value will be written instead of the filler dashes.

Here's the code that works okay, except for writing blank cells if no options are selected from combo boxes.

Private Sub cmdAdd_Click()

'dimention the variable
Dim DataSH As Worksheet
Dim Addme As Range
'set the variable
Set DataSH = Sheet1
'error handler
On Error GoTo errHandler:
'set variable for the destination
Set Addme = DataSH.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
If Me.TxtSurname = "" Or Me.TxtFirstname = "" Then 
MsgBox "First and last names are required. "
Exit Sub
End If
'send the values to the database
With DataSH

'add the unique reference ID then all other values
Addme.Offset(0, -1) = DataSH.Range("C6").Value + 1
Addme.Value = Me.TxtSurname
Addme.Offset(0, 1).Value = Me.TxtFirstname
Addme.Offset(0, 2).Value = Me.CboMath
Addme.Offset(0, 3).Value = Me.CboScience
Addme.Offset(0, 4).Value = Me.CboLang
Addme.Offset(0, 5).Value = Me.CboSocial
Addme.Offset(0, 6).Value = Me.CboPMath
Addme.Offset(0, 7).Value = Me.CboPScience
Addme.Offset(0, 8).Value = Me.CboPLang
Addme.Offset(0, 9).Value = Me.CboPSocial


End With
'sort the data by "Lastname"
DataSH.Select
With DataSH
.Range("B9:L10000").Sort Key1:=Range("C9"), Order1:=xlAscending, Header:=xlGuess
End With
'clear the values after entry
Clear
'communicate with the user
MsgBox "Student successfully added"
'return to interface sheet sheet
Sheet2.Select
'reset the form
On Error GoTo 0
Exit Sub
errHandler:
'if error occurs then show me exactly where the error occurs
MsgBox "Error " & Err.Number & _
" (" & Err.Description & ")in procedure cmdClear_Click of Form StudentDB"
End Sub

I tried adding the following (just with one cbox to see if it worked) to add filler dashes, but this wouldn't accept the new value if chosen from cbox, and instead just writes the filler dashes (first list option).

If Me.CboMath.Value = "" Then
Me.CboMath.Value = Me.CboMath.List(0)
Else
Addme.Offset(0, 2).Value = Me.CboMath
End If

I also tried setting the various cboxes to their 1st list option with code like this, but again, this isn't written over by selecting from the drop down options.

Me.CboScience.Value = Me.CboScience.List(0)

I'd appreciate any insight or assistance.

 

[Rick Rothstein]

When you initialize the UserForm, set the ListIndex property for the ComboBox equal to 0... that will preselect the first item in the list so that it will be the default.

 

[Skrej]

When you initialize the UserForm, set the ListIndex property for the ComboBox equal to 0... that will preselect the first item in the list so that it will be the default.

Thank you for the suggestion, but I guess I don't know how to that. Could you explain?

I tried doing this in the sub that calls the userform (when a button is pressed). Since that didn't work, I'm guessing that's not the right place or where you mean by initializing the userform.

I tried setting the listindex with this:
CboScience.Value = CboScience.List(0), which just gave me an object required error.

 

[mikerickson]

Addme.Offset(0, 2).Value = Me.CboMath.List(WorksheetFunction.Max(0, CboMath.ListIndex))

 

[Rick Rothstein]

Double-click the UserForm (not one of its controls) in the VBA editor to bring up the UserForm's code window. At the top of that window are two drop downs... select "UserForm" from the left drop down and after you do that, then select "Initialize" from the right drop down. This will create the event sub for the Initialize event... inside that sub, put this code line...

CboScience.ListIndex = 0

That's it. Now bring up the UserForm and that ComboBox will automatically be defaulted to the first item in its list.

 

[Skrej]

Ah, okay. Thank you all very much.

It's working fine now.

Much obliged.