How to set ComboBox default value?

[bmacd615]

I want the combobox cmbBottle to show the second item in its list when the userform shows. The code below achieves this. However, after executing cmbbottle.listindex=1, the program jumps out of the initialization and goes to Private Sub cmbBottle_change() and then executes others subs called under it. I don't want this.
Is there a way to set the combobox to a default, startup display, without triggering the cmbBottle_change event?
(I have looked under the Properties window of combobox in the form view mode and can't see how to set it's value there.)

Private Sub UserForm_Initialize()
cmbBottle.ListIndex = 1
.
.
.
.

Private Sub cmbBottle_change()
SelectCaseBottle
.
.
.

 

[dave3009]

I've was trying unsuccessfully to add items to a combobox on initialization. Seems you should set all of it on the event that triggers the userform rather than it's initialization.

Can that happen

Private Sub CommandButton1_Click()
With UserForm1
    .ComboBox1.RowSource = "RowSource"
    .ComboBox1.ListIndex = 3
    .Show
End With
End Sub

 

[bmacd615]

Good idea, but didn't work for me - yet. I will try a modification tomorrow.

 

[Scott.C.Lyerly]

What you've got going on here sounds like two different things:
1) you want to set the combobox to defaul to the second item
2) you want to avoid the _Change() event for the combobox.

The first one is easy. You need to set the text of the combobox. It's not like a listbox, where you can set the .Selection property. You've got to say "hey, cbo, here's the text I want you to display!".

To do that, set the .Text = to .List(1), as in:

Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .AddItem "Feb"
        .AddItem "Mar"
        .AddItem "Apr"
    End With
    Me.ComboBox1.Text = Me.ComboBox1.List(1)
End Sub

Okay, part number two, avoid the _Change() event. Yeah, can't say as I know a way to keep it from firing (tho wiser heads than mine might). So what you might want to do is set up a module-level variable, set it at the time of the form load, and read it when the _Change() event fires. Based on the value (I use a boolean in the example below) you can exit the _Change() sub without further action:

' Here's our module-level boolean variable.
Private mbCancel As Boolean

Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .AddItem "Feb"
        .AddItem "Mar"
        .AddItem "Apr"
    End With
    mbCancel = True     'here's where we set that variable.
    Me.ComboBox1.Text = Me.ComboBox1.List(1)
End Sub

Private Sub ComboBox1_Change()
    ' Here's where we check that variable.
    If mbCancel Then
        ' if it's TRUE, exit the sub...
        Exit Sub
    Else
        ' ... else keep going!
        MsgBox "This puppy fired!"
    End If
End Sub

Hope this helps.

 

[mjrofra]

What you've got going on here sounds like two different things:
1) you want to set the combobox to defaul to the second item
2) you want to avoid the _Change() event for the combobox.

The first one is easy. You need to set the text of the combobox. It's not like a listbox, where you can set the .Selection property. You've got to say "hey, cbo, here's the text I want you to display!".

To do that, set the .Text = to .List(1), as in:

Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .AddItem "Feb"
        .AddItem "Mar"
        .AddItem "Apr"
    End With
    Me.ComboBox1.Text = Me.ComboBox1.List(1)
End Sub

Hi Scott, hope you can help me too. I just found your reply to the default value for the combobox. I am interested in displaying always as the default value the last item in the list, but the list is a range that keeps growing. I use a name of a range in a table for the RowSource for this. So the items in the list change as I update the table. Is it possible to always display the value of the last item no matter how many items are in the RowSource? Could you please give me an idea about how to do that?

Thanks in advance,

Mauricio

 

[Scott.C.Lyerly]

Hi Mauricio-
You can always display the last item in the combobox by modifying the code in the preceding example just a bit to:

Me.ComboBox1.Text = Me.ComboBox1.List(Me.ComboBox1.ListCount - 1)

Assuming you always want the last item added to the combobox (in this case, the last item in the RowSource range) to be the default, then by using the ListCount property, you will get the total number of items in the combobox. Since items in a combobox are indexed as zero-based, you need to peel off one number from the total ListCount (hence the -1 at the end).

Make sense?

 

[mjrofra]

Great!! That´s it.

Thank you very much, Scott.

Though it didn´t work if I use RowSource, so I had to use .additem to fill the list in the combobox. But It works perfect.

This is how it goes in case anybody finds it helpful:

Private Sub UserForm_Initialize()
Dim cboItem As Range
For Each cboItem In Worksheets(1).Range("nameoftherange")
  With Me.combobox1
    .AddItem cboItem.Value
End With
Next cboItem
[COLOR="Blue"]Me.combobox1.Text = Me.combobox1.List(Me.combobox1.ListCount - 1)[/COLOR]
End Sub

 

[mikerickson]

When working with userforms, I find it useful to declare a module wide Boolean variable.

Dim ufEventsDisabled As Boolean

Private Sub cmbBottle_Click()
    If ufEventsDisabled Then Exit Sub
    MsgBox "Click"
End Sub

Private Sub UserForm_Initialize()
    With cmbBottle
        .List = Range("A1:A10").Value

        ufEventsDisabled = True
        .ListIndex = 1
        ufEventsDisabled = False

    End With
End Sub

That variable is a tool that can be used with all UF control proceedures.

 

[mjrofra]

When working with userforms, I find it useful to declare a module wide Boolean variable.

Dim ufEventsDisabled As Boolean

Private Sub cmbBottle_Click()
    If ufEventsDisabled Then Exit Sub
    MsgBox "Click"
End Sub

Private Sub UserForm_Initialize()
    With cmbBottle
        .List = Range("A1:A10").Value

        ufEventsDisabled = True
        .ListIndex = 1
        ufEventsDisabled = False

    End With
End Sub

That variable is a tool that can be used with all UF control proceedures.

Hi, Mike.

I do not completely understand what´s that about, but I already try it and it works!

I am new in vba and that´s advanced vba.

Could you please be kind and share a brief explanation about what does that Boolean variable do.

 

[mikerickson]

The problem in the OP was that VB code in the Initialize event was triggering the Click event. That wasn't what was desired. By setting ufEventsDisabled to True, this line in the Click routine prevents the rest of the Click code from running.

If ufEventsDisable Then Exit Sub

When I have several controls and several events, I put that line at the beginning of every event routine.
Setting ufEventsDisabled to True or False allows one to control when the Click routine runs. Similar to how Application.EnableEvents can be used to prevent cascading events in sheet event code.

Walkenbach pointed out that using this version keeps one from having to set the variable back to False. (But it won't stop multiple events or ambiguous ones (e.g. if .ListIndex already = 1 the instruction .ListIndex = 1 will not trigger an event.)

If ufEventsDisabled Then ufEventsDisabled = False: Exit Sub