Userform ComboBox
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Userform ComboBox
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
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
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78676
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Userform ComboBox
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.
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Userform ComboBox
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?
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?
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78676
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Userform ComboBox
To select the item in the first combo box:
The following code will make the arrow keys stop at the top and bottom of the list:
This version will make the arrow keys cycle through the items:
Code: Select all
.ListIndex = 0
.SelStart = 0
.SelLength = Len(.Value)
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
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
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Userform ComboBox
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?
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?
Nathan
There's no place like home.....
There's no place like home.....
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Userform ComboBox
OOps..... Do I need to duplicate the code for each combobox, or can it be set to work on all comboboxes?
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78676
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Userform ComboBox
You need to duplicate the code.VegasNath wrote:OOps..... Do I need to duplicate the code for each combobox, or can it be set to work on all comboboxes?
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78676
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Userform ComboBox
You're overcomplicating things now.VegasNath wrote: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?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Userform ComboBox
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 wrote:You're overcomplicating things now.VegasNath wrote: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?
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78676
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Userform ComboBox
I don't know if it is possible; in any case, I don't know how to do that.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Userform ComboBox
Thankyou for all of your help, really appreciated!
Nathan
There's no place like home.....
There's no place like home.....
-
- Microsoft MVP
- Posts: 87
- Joined: 10 Mar 2011, 05:38
- Status: Microsoft MVP
- Location: New Jersey in the US
Re: Userform ComboBox
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.VegasNath wrote:OOps..... Do I need to duplicate the code for each combobox, or can it be set to work on all comboboxes?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78676
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Userform ComboBox
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.
I am uploading my UF template for reference.
You do not have the required permissions to view the files attached to this post.
Nathan
There's no place like home.....
There's no place like home.....