ComboBox input

[Guest]

Hi
The code seeks to establish whether data exists already on a given date and
selected vehicle registration.
It works ok when using the mouse to select any registration from a userform
combobox list but fails sometimes with a manual input. The reason being the
required string is entered and tested as a whole.

To shorten the typing I have added an index column to the control. But if
this index is not known, it will be necessary for the registration to be
typed in as per frmMatchEntryComplete.

A manual input ‘failure’ occurs if the target vehicle is index 10 = Y123
ABC.
The Do Until Loop causes an escape as soon as 1 is typed. Though the cursor
remains in the box for further input a message will have been triggered
before the second digit 0 can be entered.

1. How can I amend the code to suit both mouse and manual input?
2. After making a selection using the mouse, the input becomes selected.
This makes it easy to change. With a typed input neither an index nor
registration becomes selected. How can this be made similar? SelStart and
SelLength do not appear to work here.
3. With a mouse input it is easy to move the focus to the next control by
making the test if cbo1.textlength > 0 then go. How could this be done with
a manual input without having to manually tab?

I’d be grateful for advice.

T.I.A.

Geoff

To see how the code fails with manual input, put a default combobox on a
form and name it cbo1

On wksheet1 put the following data starting in cell A1

Date
10 May 2005
11 May 2005
12 May 2005
12 May 2005
12 May 2005
12 May 2005
13 May 2005

This starting in B1
Veh Reg
Y123 ABC
Y123 ABC
Y123 ABC
Y127 ABC
Y129 ABC
Y132 ABC
Y126 ABC

This starting in D1 (this is the database to populate the control)
Veh Reg
??
Y123 ABC
Y124 ABC
Y125 ABC
Y126 ABC
Y127 ABC
Y128 ABC
Y129 ABC
Y130 ABC
Y131 ABC
Y132 ABC

And finally this into the form module
Option Explicit
Dim VehReg() As String
Dim tbl As Range, rng As Range
Dim i As Integer
Dim cbo1cntr As Integer
Dim firstfind

Private Sub UserForm_Initialize()
With UserForm1
.Left = 250
.Top = 65
.startupposition = 0
End With
With cbo1
.ColumnCount = 2
.ColumnWidths = 15 & ";" & 30
.ListRows = 15
End With
Set tbl = Sheets(1).Range("D1").CurrentRegion.Offset(1, 0)
ReDim VehReg(0 To tbl.Rows.Count - 2, 1)
For i = 0 To tbl.Rows.Count - 2 'create index
VehReg(i, 0) = i
Next i
For i = 0 To tbl.Rows.Count - 2 'populate box
VehReg(i, 1) = Sheets(1).Cells(i + 2, 4)
Next i
cbo1.List() = VehReg
Range("A20").Select
End Sub

Private Sub cbo1_Change()
Set rng = Sheets(1).Range("A23") 'find date, example 12/05/2005
Set rng = Sheets(1).Columns(1).Find _
(what:=CDate("12 May 2005"), _
After:=rng, _
LookIn:=xlFormulas, _
Lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
firstfind = ""
If Not rng Is Nothing And Not cbo1.Value = "" And Not cbo1.Value _

cbo1.ListCount Then

Do Until rng.Offset(, 1).Value = cbo1.Column(1) Or rng.Address =
firstfind
If firstfind = "" Then firstfind = rng.Address
Set rng = Sheets(1).Columns(1).FindNext(After:=rng)
Loop
If rng.Offset(, 1).Value = cbo1.Column(1) Then
Range(rng.Address, Cells(rng.Row, rng.Column + 1)).Select
MsgBox "A vehicle record exists on that date " & vbCr & _
"Do something"
Else
Range("A20").Select
MsgBox "No record exists " & vbCr & _
"Do something else "
End If
Else
If Not cbo1cntr = 1 Then 'if index is greater than list
cbo1cntr = 1
Range("A20").Select
MsgBox "Index does not exist "
cbo1.Value = ""
End If
End If
End Sub

 

[STEVE BELL]

Geoff,

You may need to look at the combo box events.

The change event triggers when you either select an item in the list
or when you type a single character.

The exit event will trigger when you leave the combobox.

 

[Guest]

I am aware.
The prime question remains, how is it possible to prevent the do loop test
whist inputting single characters. How can I stop the test after the digit 1
and wait until 10?
Geoff

 

[STEVE BELL]

Again -

You appear to be using a combobox change event. This will fire each time
you enter a single character...

Use the combobox exit event to make the event wait until you are finished
with the combobox.

Or you can add an if then statement in the change event macro to check the
value of the combobox.
(or the length of the combobox value, or ....). And use this check to exit
the sub routine...

 

[Guest]

Again..
Believe me I have not posted this question at the first sign of a problem.
I have tried every logical test i can think of both in the change event and
the exit event. The whole thing hinges on the length of string to be tested
be it 1 or 2 characters of an index or the whole registration.
I was hoping for specifics rather than generalities in this case.

Geoff

 

[STEVE BELL]

Geoff,

Sorry if I wasn't any help. (I meant no offense).

I figured you knew how to add if statements to the code and make them work.

sounds like you are looking for
x = len(combobox1.value)

or
x = combobox1.listindex

 

[Guest]

Steve

This is just leading to frustration and not moving the issue on at all but
thank you for your attention.

Geoff