Select Method from Combo Box

My Form uses the following code (shown below).
This code has been working well for me, but I'd like to change it to work in
a different way.

Currently, I scroll to find the item I want, and then click on it. If I try
to type the item I want, it selects the first item with the first matching
character in the list.

I would like to be able to type the value I want (to autofill with the
values in the list) and/or to scroll to the item I want with the keyboard,
and then press enter or click to select the item.

Will someone show me how?
Thanks,
Bernie

Option Explicit

Private Sub CombinedBankNames_Change()
MsgBox CombinedBankNames.Value
boxvalue = CombinedBankNames.Value
Call MoveBankData
End Sub

Private Sub UserForm_Initialize()
Dim myCell As Range
For Each myCell In Worksheets("BankData").Range("AR2:AR999").Cells
If myCell.Value = "" Then
'do nothing
Else
Me.CombinedBankNames.AddItem myCell.Value
End If
Next myCell
End Sub

If you sort your values in the combobox, your should get what you want.
The easiest is to sort then in the worksheet, then load them


> Option Explicit
>
> Private Sub CombinedBankNames_Change()
> MsgBox CombinedBankNames.Value
> boxvalue = CombinedBankNames.Value
> Call MoveBankData
> End Sub

Private Sub UserForm_Initialize()
Dim rng as Range, rng1 As Range
With Worksheets("BankData")
set rng = .Range("AR2:AR999")
rng.copy Destination:=.Range("IV2")
.Range("IV2:IV999").sort Key1:=.Range("IV2"), _
Header:=xlNo
set rng1 = .Range(.Range("IV2"),.Range("IV2").end(xldown))
Me.CombinedBankNames.List = rng1.Value
.Columns(256).Delete
End With
End Sub

--
Regards,
Tom Ogilvy



"bw" <iamnu@cableone.net> wrote in message
news:11grqkmkfrrad22@corp.supernews.com...
> My Form uses the following code (shown below).
> This code has been working well for me, but I'd like to change it to work
in
> a different way.
>
> Currently, I scroll to find the item I want, and then click on it. If I
try
> to type the item I want, it selects the first item with the first matching
> character in the list.
>
> I would like to be able to type the value I want (to autofill with the
> values in the list) and/or to scroll to the item I want with the keyboard,
> and then press enter or click to select the item.
>
> Will someone show me how?
> Thanks,
> Bernie
>
> Option Explicit
>
> Private Sub CombinedBankNames_Change()
> MsgBox CombinedBankNames.Value
> boxvalue = CombinedBankNames.Value
> Call MoveBankData
> End Sub
>
> Private Sub UserForm_Initialize()
> Dim myCell As Range
> For Each myCell In Worksheets("BankData").Range("AR2:AR999").Cells
> If myCell.Value = "" Then
> 'do nothing
> Else
> Me.CombinedBankNames.AddItem myCell.Value
> End If
> Next myCell
> End Sub
>
>

Thanks Tom, but the values are already sorted in the worksheet. When I type
in a "T" (for example), it immediately selects the first word in the list
that begins with a T, and I did not click on it, nor did I press the Enter
key.
It will only allow me to type one character.

Bernie

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:OhrlZEZqFHA.208@TK2MSFTNGP10.phx.gbl...
> If you sort your values in the combobox, your should get what you want.
> The easiest is to sort then in the worksheet, then load them
>
>
>> Option Explicit
>>
>> Private Sub CombinedBankNames_Change()
>> MsgBox CombinedBankNames.Value
>> boxvalue = CombinedBankNames.Value
>> Call MoveBankData
>> End Sub
>
> Private Sub UserForm_Initialize()
> Dim rng as Range, rng1 As Range
> With Worksheets("BankData")
> set rng = .Range("AR2:AR999")
> rng.copy Destination:=.Range("IV2")
> .Range("IV2:IV999").sort Key1:=.Range("IV2"), _
> Header:=xlNo
> set rng1 = .Range(.Range("IV2"),.Range("IV2").end(xldown))
> Me.CombinedBankNames.List = rng1.Value
> .Columns(256).Delete
> End With
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "bw" <iamnu@cableone.net> wrote in message
> news:11grqkmkfrrad22@corp.supernews.com...
>> My Form uses the following code (shown below).
>> This code has been working well for me, but I'd like to change it to work
> in
>> a different way.
>>
>> Currently, I scroll to find the item I want, and then click on it. If I
> try
>> to type the item I want, it selects the first item with the first
>> matching
>> character in the list.
>>
>> I would like to be able to type the value I want (to autofill with the
>> values in the list) and/or to scroll to the item I want with the
>> keyboard,
>> and then press enter or click to select the item.
>>
>> Will someone show me how?
>> Thanks,
>> Bernie
>>
>> Option Explicit
>>
>> Private Sub CombinedBankNames_Change()
>> MsgBox CombinedBankNames.Value
>> boxvalue = CombinedBankNames.Value
>> Call MoveBankData
>> End Sub
>>
>> Private Sub UserForm_Initialize()
>> Dim myCell As Range
>> For Each myCell In Worksheets("BankData").Range("AR2:AR999").Cells
>> If myCell.Value = "" Then
>> 'do nothing
>> Else
>> Me.CombinedBankNames.AddItem myCell.Value
>> End If
>> Next myCell
>> End Sub
>>
>>
>
>

Have you set the matchentry property to 1 (FmMatchEntryComplete). If the
data is sorted, according to the description, this should do exactly what
you want:

FmMatchEntryComplete
Value: 1
Extended matching. As each character is typed, the control searches for an
entry matching all characters entered (default).

further the help states:
The control initiates the Click event as soon as the user types a sequence
of characters that match exactly one entry in the list. As the user types,
the entry is compared with the current row in the list and with the next row
in the list. When the entry matches only the current row, the match is
unambiguous.



--

Regards,

Tom Ogilvy



"bw" <iamnu@cableone.net> wrote in message
news:11grsu6pql3etf6@corp.supernews.com...
> Thanks Tom, but the values are already sorted in the worksheet. When I
type
> in a "T" (for example), it immediately selects the first word in the list
> that begins with a T, and I did not click on it, nor did I press the Enter
> key.
> It will only allow me to type one character.
>
> Bernie
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:OhrlZEZqFHA.208@TK2MSFTNGP10.phx.gbl...
> > If you sort your values in the combobox, your should get what you want.
> > The easiest is to sort then in the worksheet, then load them
> >
> >
> >> Option Explicit
> >>
> >> Private Sub CombinedBankNames_Change()
> >> MsgBox CombinedBankNames.Value
> >> boxvalue = CombinedBankNames.Value
> >> Call MoveBankData
> >> End Sub
> >
> > Private Sub UserForm_Initialize()
> > Dim rng as Range, rng1 As Range
> > With Worksheets("BankData")
> > set rng = .Range("AR2:AR999")
> > rng.copy Destination:=.Range("IV2")
> > .Range("IV2:IV999").sort Key1:=.Range("IV2"), _
> > Header:=xlNo
> > set rng1 = .Range(.Range("IV2"),.Range("IV2").end(xldown))
> > Me.CombinedBankNames.List = rng1.Value
> > .Columns(256).Delete
> > End With
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "bw" <iamnu@cableone.net> wrote in message
> > news:11grqkmkfrrad22@corp.supernews.com...
> >> My Form uses the following code (shown below).
> >> This code has been working well for me, but I'd like to change it to
work
> > in
> >> a different way.
> >>
> >> Currently, I scroll to find the item I want, and then click on it. If
I
> > try
> >> to type the item I want, it selects the first item with the first
> >> matching
> >> character in the list.
> >>
> >> I would like to be able to type the value I want (to autofill with the
> >> values in the list) and/or to scroll to the item I want with the
> >> keyboard,
> >> and then press enter or click to select the item.
> >>
> >> Will someone show me how?
> >> Thanks,
> >> Bernie
> >>
> >> Option Explicit
> >>
> >> Private Sub CombinedBankNames_Change()
> >> MsgBox CombinedBankNames.Value
> >> boxvalue = CombinedBankNames.Value
> >> Call MoveBankData
> >> End Sub
> >>
> >> Private Sub UserForm_Initialize()
> >> Dim myCell As Range
> >> For Each myCell In Worksheets("BankData").Range("AR2:AR999").Cells
> >> If myCell.Value = "" Then
> >> 'do nothing
> >> Else
> >> Me.CombinedBankNames.AddItem myCell.Value
> >> End If
> >> Next myCell
> >> End Sub
> >>
> >>
> >
> >
>
>

Tom,

No, I did not set the FmMatchEntry Complete value. Hard as I try, I can't
see where/how to do this. "Help" is not providing what I need, and trying
to set it within my UserForm_Initialize procedure is not working either.
Where/how do I do this?
Thanks again,
Bernie

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:uhCNHfZqFHA.2416@TK2MSFTNGP10.phx.gbl...
> Have you set the matchentry property to 1 (FmMatchEntryComplete). If the
> data is sorted, according to the description, this should do exactly what
> you want:
>
> FmMatchEntryComplete
> Value: 1
> Extended matching. As each character is typed, the control searches for an
> entry matching all characters entered (default).
>
> further the help states:
> The control initiates the Click event as soon as the user types a sequence
> of characters that match exactly one entry in the list. As the user types,
> the entry is compared with the current row in the list and with the next
> row
> in the list. When the entry matches only the current row, the match is
> unambiguous.
>
>
>
> --
>
> Regards,
>
> Tom Ogilvy
>
>
>
> "bw" <iamnu@cableone.net> wrote in message
> news:11grsu6pql3etf6@corp.supernews.com...
>> Thanks Tom, but the values are already sorted in the worksheet. When I
> type
>> in a "T" (for example), it immediately selects the first word in the list
>> that begins with a T, and I did not click on it, nor did I press the
>> Enter
>> key.
>> It will only allow me to type one character.
>>
>> Bernie
>>
>> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
>> news:OhrlZEZqFHA.208@TK2MSFTNGP10.phx.gbl...
>> > If you sort your values in the combobox, your should get what you want.
>> > The easiest is to sort then in the worksheet, then load them
>> >
>> >
>> >> Option Explicit
>> >>
>> >> Private Sub CombinedBankNames_Change()
>> >> MsgBox CombinedBankNames.Value
>> >> boxvalue = CombinedBankNames.Value
>> >> Call MoveBankData
>> >> End Sub
>> >
>> > Private Sub UserForm_Initialize()
>> > Dim rng as Range, rng1 As Range
>> > With Worksheets("BankData")
>> > set rng = .Range("AR2:AR999")
>> > rng.copy Destination:=.Range("IV2")
>> > .Range("IV2:IV999").sort Key1:=.Range("IV2"), _
>> > Header:=xlNo
>> > set rng1 = .Range(.Range("IV2"),.Range("IV2").end(xldown))
>> > Me.CombinedBankNames.List = rng1.Value
>> > .Columns(256).Delete
>> > End With
>> > End Sub
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> >
>> > "bw" <iamnu@cableone.net> wrote in message
>> > news:11grqkmkfrrad22@corp.supernews.com...
>> >> My Form uses the following code (shown below).
>> >> This code has been working well for me, but I'd like to change it to
> work
>> > in
>> >> a different way.
>> >>
>> >> Currently, I scroll to find the item I want, and then click on it. If
> I
>> > try
>> >> to type the item I want, it selects the first item with the first
>> >> matching
>> >> character in the list.
>> >>
>> >> I would like to be able to type the value I want (to autofill with the
>> >> values in the list) and/or to scroll to the item I want with the
>> >> keyboard,
>> >> and then press enter or click to select the item.
>> >>
>> >> Will someone show me how?
>> >> Thanks,
>> >> Bernie
>> >>
>> >> Option Explicit
>> >>
>> >> Private Sub CombinedBankNames_Change()
>> >> MsgBox CombinedBankNames.Value
>> >> boxvalue = CombinedBankNames.Value
>> >> Call MoveBankData
>> >> End Sub
>> >>
>> >> Private Sub UserForm_Initialize()
>> >> Dim myCell As Range
>> >> For Each myCell In Worksheets("BankData").Range("AR2:AR999").Cells
>> >> If myCell.Value = "" Then
>> >> 'do nothing
>> >> Else
>> >> Me.CombinedBankNames.AddItem myCell.Value
>> >> End If
>> >> Next myCell
>> >> End Sub
>> >>
>> >>
>> >
>> >
>>
>>
>
>

in a userform

In the vbe, select the combobox and make sure the properties window is
visible (if not View=>Properties Window or hit F4)
find the matchentry property and select the value from the dropdown


if on the worksheet, in design view, right click on the combobox and select
properties.

assumes a combobox from the control toolbox toolbar.

--
Regards,
Tom Ogilvy


"bw" <iamnu@cableone.net> wrote in message
news:11gs7j98fq61n62@corp.supernews.com...
> Tom,
>
> No, I did not set the FmMatchEntry Complete value. Hard as I try, I can't
> see where/how to do this. "Help" is not providing what I need, and trying
> to set it within my UserForm_Initialize procedure is not working either.
> Where/how do I do this?
> Thanks again,
> Bernie
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:uhCNHfZqFHA.2416@TK2MSFTNGP10.phx.gbl...
> > Have you set the matchentry property to 1 (FmMatchEntryComplete). If
the
> > data is sorted, according to the description, this should do exactly
what
> > you want:
> >
> > FmMatchEntryComplete
> > Value: 1
> > Extended matching. As each character is typed, the control searches for
an
> > entry matching all characters entered (default).
> >
> > further the help states:
> > The control initiates the Click event as soon as the user types a
sequence
> > of characters that match exactly one entry in the list. As the user
types,
> > the entry is compared with the current row in the list and with the next
> > row
> > in the list. When the entry matches only the current row, the match is
> > unambiguous.
> >
> >
> >
> > --
> >
> > Regards,
> >
> > Tom Ogilvy
> >
> >
> >
> > "bw" <iamnu@cableone.net> wrote in message
> > news:11grsu6pql3etf6@corp.supernews.com...
> >> Thanks Tom, but the values are already sorted in the worksheet. When I
> > type
> >> in a "T" (for example), it immediately selects the first word in the
list
> >> that begins with a T, and I did not click on it, nor did I press the
> >> Enter
> >> key.
> >> It will only allow me to type one character.
> >>
> >> Bernie
> >>
> >> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> >> news:OhrlZEZqFHA.208@TK2MSFTNGP10.phx.gbl...
> >> > If you sort your values in the combobox, your should get what you
want.
> >> > The easiest is to sort then in the worksheet, then load them
> >> >
> >> >
> >> >> Option Explicit
> >> >>
> >> >> Private Sub CombinedBankNames_Change()
> >> >> MsgBox CombinedBankNames.Value
> >> >> boxvalue = CombinedBankNames.Value
> >> >> Call MoveBankData
> >> >> End Sub
> >> >
> >> > Private Sub UserForm_Initialize()
> >> > Dim rng as Range, rng1 As Range
> >> > With Worksheets("BankData")
> >> > set rng = .Range("AR2:AR999")
> >> > rng.copy Destination:=.Range("IV2")
> >> > .Range("IV2:IV999").sort Key1:=.Range("IV2"), _
> >> > Header:=xlNo
> >> > set rng1 = .Range(.Range("IV2"),.Range("IV2").end(xldown))
> >> > Me.CombinedBankNames.List = rng1.Value
> >> > .Columns(256).Delete
> >> > End With
> >> > End Sub
> >> >
> >> > --
> >> > Regards,
> >> > Tom Ogilvy
> >> >
> >> >
> >> >
> >> > "bw" <iamnu@cableone.net> wrote in message
> >> > news:11grqkmkfrrad22@corp.supernews.com...
> >> >> My Form uses the following code (shown below).
> >> >> This code has been working well for me, but I'd like to change it to
> > work
> >> > in
> >> >> a different way.
> >> >>
> >> >> Currently, I scroll to find the item I want, and then click on it.
If
> > I
> >> > try
> >> >> to type the item I want, it selects the first item with the first
> >> >> matching
> >> >> character in the list.
> >> >>
> >> >> I would like to be able to type the value I want (to autofill with
the
> >> >> values in the list) and/or to scroll to the item I want with the
> >> >> keyboard,
> >> >> and then press enter or click to select the item.
> >> >>
> >> >> Will someone show me how?
> >> >> Thanks,
> >> >> Bernie
> >> >>
> >> >> Option Explicit
> >> >>
> >> >> Private Sub CombinedBankNames_Change()
> >> >> MsgBox CombinedBankNames.Value
> >> >> boxvalue = CombinedBankNames.Value
> >> >> Call MoveBankData
> >> >> End Sub
> >> >>
> >> >> Private Sub UserForm_Initialize()
> >> >> Dim myCell As Range
> >> >> For Each myCell In Worksheets("BankData").Range("AR2:AR999").Cells
> >> >> If myCell.Value = "" Then
> >> >> 'do nothing
> >> >> Else
> >> >> Me.CombinedBankNames.AddItem myCell.Value
> >> >> End If
> >> >> Next myCell
> >> >> End Sub
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Well, I finally found what you were suggesting, but everything is as you
said it should be. I'm at a loss as to what is happening.
I was going to attach the Userform2.frm but since it is as you said, decided
that would be of no help to you.
Thanks for you help Tom. I don't want to waste any more of your
time/expertise.
Bernie


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:ujuq97aqFHA.3836@TK2MSFTNGP12.phx.gbl...
> in a userform
>
> In the vbe, select the combobox and make sure the properties window is
> visible (if not View=>Properties Window or hit F4)
> find the matchentry property and select the value from the dropdown
>
>
> if on the worksheet, in design view, right click on the combobox and
> select
> properties.
>
> assumes a combobox from the control toolbox toolbar.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "bw" <iamnu@cableone.net> wrote in message
> news:11gs7j98fq61n62@corp.supernews.com...
>> Tom,
>>
>> No, I did not set the FmMatchEntry Complete value. Hard as I try, I
>> can't
>> see where/how to do this. "Help" is not providing what I need, and
>> trying
>> to set it within my UserForm_Initialize procedure is not working either.
>> Where/how do I do this?
>> Thanks again,
>> Bernie
>>
>> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
>> news:uhCNHfZqFHA.2416@TK2MSFTNGP10.phx.gbl...
>> > Have you set the matchentry property to 1 (FmMatchEntryComplete). If
> the
>> > data is sorted, according to the description, this should do exactly
> what
>> > you want:
>> >
>> > FmMatchEntryComplete
>> > Value: 1
>> > Extended matching. As each character is typed, the control searches for
> an
>> > entry matching all characters entered (default).
>> >
>> > further the help states:
>> > The control initiates the Click event as soon as the user types a
> sequence
>> > of characters that match exactly one entry in the list. As the user
> types,
>> > the entry is compared with the current row in the list and with the
>> > next
>> > row
>> > in the list. When the entry matches only the current row, the match is
>> > unambiguous.
>> >
>> >
>> >
>> > --
>> >
>> > Regards,
>> >
>> > Tom Ogilvy
>> >
>> >
>> >
>> > "bw" <iamnu@cableone.net> wrote in message
>> > news:11grsu6pql3etf6@corp.supernews.com...
>> >> Thanks Tom, but the values are already sorted in the worksheet. When
>> >> I
>> > type
>> >> in a "T" (for example), it immediately selects the first word in the
> list
>> >> that begins with a T, and I did not click on it, nor did I press the
>> >> Enter
>> >> key.
>> >> It will only allow me to type one character.
>> >>
>> >> Bernie
>> >>
>> >> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
>> >> news:OhrlZEZqFHA.208@TK2MSFTNGP10.phx.gbl...
>> >> > If you sort your values in the combobox, your should get what you
> want.
>> >> > The easiest is to sort then in the worksheet, then load them
>> >> >
>> >> >
>> >> >> Option Explicit
>> >> >>
>> >> >> Private Sub CombinedBankNames_Change()
>> >> >> MsgBox CombinedBankNames.Value
>> >> >> boxvalue = CombinedBankNames.Value
>> >> >> Call MoveBankData
>> >> >> End Sub
>> >> >
>> >> > Private Sub UserForm_Initialize()
>> >> > Dim rng as Range, rng1 As Range
>> >> > With Worksheets("BankData")
>> >> > set rng = .Range("AR2:AR999")
>> >> > rng.copy Destination:=.Range("IV2")
>> >> > .Range("IV2:IV999").sort Key1:=.Range("IV2"), _
>> >> > Header:=xlNo
>> >> > set rng1 = .Range(.Range("IV2"),.Range("IV2").end(xldown))
>> >> > Me.CombinedBankNames.List = rng1.Value
>> >> > .Columns(256).Delete
>> >> > End With
>> >> > End Sub
>> >> >
>> >> > --
>> >> > Regards,
>> >> > Tom Ogilvy
>> >> >
>> >> >
>> >> >
>> >> > "bw" <iamnu@cableone.net> wrote in message
>> >> > news:11grqkmkfrrad22@corp.supernews.com...
>> >> >> My Form uses the following code (shown below).
>> >> >> This code has been working well for me, but I'd like to change it
>> >> >> to
>> > work
>> >> > in
>> >> >> a different way.
>> >> >>
>> >> >> Currently, I scroll to find the item I want, and then click on it.
> If
>> > I
>> >> > try
>> >> >> to type the item I want, it selects the first item with the first
>> >> >> matching
>> >> >> character in the list.
>> >> >>
>> >> >> I would like to be able to type the value I want (to autofill with
> the
>> >> >> values in the list) and/or to scroll to the item I want with the
>> >> >> keyboard,
>> >> >> and then press enter or click to select the item.
>> >> >>
>> >> >> Will someone show me how?
>> >> >> Thanks,
>> >> >> Bernie
>> >> >>
>> >> >> Option Explicit
>> >> >>
>> >> >> Private Sub CombinedBankNames_Change()
>> >> >> MsgBox CombinedBankNames.Value
>> >> >> boxvalue = CombinedBankNames.Value
>> >> >> Call MoveBankData
>> >> >> End Sub
>> >> >>
>> >> >> Private Sub UserForm_Initialize()
>> >> >> Dim myCell As Range
>> >> >> For Each myCell In Worksheets("BankData").Range("AR2:AR999").Cells
>> >> >> If myCell.Value = "" Then
>> >> >> 'do nothing
>> >> >> Else
>> >> >> Me.CombinedBankNames.AddItem myCell.Value
>> >> >> End If
>> >> >> Next myCell
>> >> >> End Sub
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>