Userform vba to use first option in combobox if nothing selected

Skrej

Board Regular
Joined
May 31, 2013
Messages
135
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.

VBA Code:
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).

VBA Code:
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.
VBA Code:
Me.CboScience.Value = Me.CboScience.List(0)

I'd appreciate any insight or assistance.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
May 31, 2013
Messages
135
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,958
VBA Code:
Addme.Offset(0, 2).Value = Me.CboMath.List(WorksheetFunction.Max(0, CboMath.ListIndex))
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Solution

Skrej

Board Regular
Joined
May 31, 2013
Messages
135
Ah, okay. Thank you all very much.

It's working fine now.

Much obliged.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,347
Messages
5,624,136
Members
416,012
Latest member
rockermom59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top