Userform ComboBox

[VegasNath]

Hi,
I have been trying for the last hour or so to create a basic userform without success. I have created the design which is as follows:

A few text boxes, 4 ComboBoxes and 1 command button. I would like to hard code the lists into the code, not use worksheet lists. The uf will be called by a sub routine where the user should select 4 options before selecting the continue (command button). I would like the 4 selected text options to then be made available to the calling sub routine. In addition, I would like to tab from cb1 through to cb4 and then to the command button, and disable the userform close event.

I would be grateful if somebody would provide a sample of the code structure that would be required to make this happen? TIA

[HansV]

See the attached simplified example.

To set the tab order of controls, select View | Tab Order in the Visual Basic Editor (while looking at the userform, of course). Or set the TabIndex of each control, starting at 0 for the control that should have focus when the form is opened.

The combo boxes are populated in the UserForm_Initialize event.
The user is prevented from closing the form by clicking the x in the upper right corner by the UserForm_QueryClose event.

UserFormDemo.xlsm

[VegasNath]

Thankyou very much Hans!

I have added commands like ".ListIndex = 0" so that the first list item is visible as default. If the user accepts the default in combobox1 and then uses the tab key to go to combobox2, the default item in cb2 is highlighted. Is it possible for the default in cb1 can be highlighted when the userform is first shown?

Also, the up down arrows work to scroll through each list (great) but if the user gets to the end of the list continually pressing the down arrow, it jumps to the next list. is there a way to stop that so that tab must be used to move to the next list?

[HansV]

To select the item in the first combo box:

Code: Select all

    .ListIndex = 0
    .SelStart = 0
    .SelLength = Len(.Value)

The following code will make the arrow keys stop at the top and bottom of the list:

Code: Select all

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  Select Case KeyCode
    Case vbKeyUp
      If Me.ComboBox1.ListIndex = 0 Then
        KeyCode = 0
      End If
    Case vbKeyDown
      If Me.ComboBox1.ListIndex = Me.ComboBox1.ListCount - 1 Then
        KeyCode = 0
      End If
    End Select
End Sub

This version will make the arrow keys cycle through the items:

Code: Select all

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  Select Case KeyCode
    Case vbKeyUp
      If Me.ComboBox1.ListIndex = 0 Then
        Me.ComboBox1.ListIndex = Me.ComboBox1.ListCount - 1
        KeyCode = 0
      End If
    Case vbKeyDown
      If Me.ComboBox1.ListIndex = Me.ComboBox1.ListCount - 1 Then
        Me.ComboBox1.ListIndex = 0
        KeyCode = 0
      End If
    End Select
End Sub

[VegasNath]

Great, thanks very much. Last question (promise)

Is it possible to always maintain the selected text as the user 'arrows' through the list? So that whichever combobox is active, its text remains highlighted?

[VegasNath]

OOps..... Do I need to duplicate the code for each combobox, or can it be set to work on all comboboxes?

[HansV]

OOps..... Do I need to duplicate the code for each combobox, or can it be set to work on all comboboxes?

You need to duplicate the code.

[HansV]

Great, thanks very much. Last question (promise)

Is it possible to always maintain the selected text as the user 'arrows' through the list? So that whichever combobox is active, its text remains highlighted?

You're overcomplicating things now.

[VegasNath]

Great, thanks very much. Last question (promise)

Is it possible to always maintain the selected text as the user 'arrows' through the list? So that whichever combobox is active, its text remains highlighted?

You're overcomplicating things now.

The reason that I ask is that one of the userforms that I am planning on building will have quite a lot of comboboxes, so as a visual aid to the user. I'm thoughtful like that....

[HansV]

I don't know if it is possible; in any case, I don't know how to do that.

[VegasNath]

Thankyou for all of your help, really appreciated!

[Rick Rothstein]

OOps..... Do I need to duplicate the code for each combobox, or can it be set to work on all comboboxes?

Not wanting to contradict Hans, but there is a way to have all ComboBoxes on your UserForm serviced by the same set of event handlers. I have attached a workbook that contains an example showing how to highlight the text in the active ComboBox using one set of event handlers. The code, as written, will service all of the ComboBoxes that are placed on the UserForm itself, although it is possible to filter the ComboBoxes so a subset of the entire group is serviced and the rest are not if need be. The key code is in the Class Module (that is where the event handlers are coded) and the Initialize event for the UserForm (and the locally global array declared there as well). Try it out and see if it does what you want.

[HansV]

Thanks for the demo, Rick, and welcome to Eileen's Lounge!

[VegasNath]

Hello Rick, welcome to Eileen's Lounge & many thanks for your contribution! This works perfectly and will serve as my first ever class module (I have never really understood their use before). This will come in very handy!.

I am uploading my UF template for reference.

UserForm Demo 3.xls