Userform Combobox default value

Dave Edmonds · Jun 15th 2016

Hi, this is so simple but is killing me!
I have a combo box called "account_list" and I have a dynamic range called "accounts" set in the row source property in vba.
I would like to specify in the userform_initialize sub that one of the values in the list is default selected when the form loads.

I have tried quite a few ways and none seem to work.

Any ideas guys?

Dave

Dave Edmonds · Jun 15th 2016

Re: Userform Combobox default value

here is my whole sub routine

Code

Private Sub UserForm_Initialize()


Dim f_name As String
Dim l_name As String
Dim username As String
Dim u_name As String
Dim user_string As String
user_name = Environ("USERNAME")
PC_name = Environ("COMPUTERNAME")
Domain_name = Environ("USERDOMAIN")
timestamp = Now()
user_string = "ACCESS LOG:  " & user_name & "  :  " & PC_name & "  :  " & Domain_name & "  :  " & timestamp
'get windows user id'
username = Environ("USERNAME")
'split string into first name and last name'
f_name = Split(username, ".")(0)
'concatenate welcome message'
u_name = "Welcome " & f_name
'set welcome label to u_name'
Hello_Name_lbl = u_name


user_details = user_string


Account_form.BackColor = RGB(255, 224, 18)
welcome_label.BackColor = RGB(255, 224, 18)
Datacapture_frm.BackColor = RGB(255, 224, 18)
what_label.BackColor = RGB(255, 224, 18)
welcome_label.BackColor = RGB(255, 224, 18)
Account_form.Label1.BackColor = RGB(255, 224, 18)
Account_form.Label2.BackColor = RGB(255, 224, 18)
Account_form.label3.BackColor = RGB(255, 224, 18)
Account_form.Label4.BackColor = RGB(255, 224, 18)
Account_form.Label5.BackColor = RGB(255, 224, 18)
Account_form.Label6.BackColor = RGB(255, 224, 18)
Account_form.Label7.BackColor = RGB(255, 224, 18)
Account_form.Label8.BackColor = RGB(255, 224, 18)
data_box_1.Value = ""
data_box_2.Value = ""
data_box_3.Value = ""
Data_box_4.Value = ""
Data_box_5.Value = ""
Data_box_6.Value = ""
Data_box_7.Value = ""
Data_box_8.Value = ""
 
account_list.value = account_list.item (9)


'Call account_list_Change
Datacapture_frm.Visible = False
what_label.Visible = False
End Sub

Display More

**cytop** · Jun 15th 2016

Re: Userform Combobox default value

Don't use the Value property to set the selected item in a ComboBox (or ListBox - essentially the same thing with the same Properties and Methods), use the ListIndex property.

ListIndex is exactly what it says, the Index of the item in the list. It is 0 based, so if you really want the 9th item, then use 8 as the Listindex

Code

account_list.ListIndex = 8

Dave Edmonds · Jun 16th 2016

Re: Userform Combobox default value

Lovely thanks. Just out of interest why is it better than .value?

Dave

**cytop** · Jun 16th 2016

Re: Userform Combobox default value

The Value property is the text/value in the TextBox portion of the control - this may match an item in the list, but if the Style is set to '0 - fmStyleDropdownCombo', the user can enter something that does not match any item. You could set the Style to '1 - fmStyleDropdownList' which will only allow input that matches an item in the list.

When working with comboboxes, it is better to explicitly query the selection rather than relying on the Text/Value properties.

ListIndex - Returns the Index of the selected item. ListIndex returns -1 if nothing is selected or the user has typed a non-matching string in the textbox. Listindex is 0 based
List(x) - Returns the text of the item at Index x. cb.List(cb.ListIndex) returns the text of the selected item (but will error if the ListIndex is -1).
ListCount - Returns the number of items in the list.

You can also use

MatchFound - To determine if text entered in the textbox matches any item in the list
MatchRequired - Yes/No. If Yes then used to verify a valid selection has been made when the combobox loses focus. Focus is returned to the Combobox if not.
ListRows - To define how many items should be displayed in the dropdown (Nothing to do with your question really, but just putting it out there as it seems to be a very underused feature :))

Userform Combobox default value

Participate now!

Share