Greetings,

If the entry that I just made to the ComboBox is not in the list, I
have a ControlButton that I click on that will copy the entry to the
list and then resort the list. What I want to do is have the ComboBox
do this on exiting without having to click on the ControlButton.

Any one have any ideas as to how this can be done?

TIA

-Minitman

Where is the combobox located. What type of combobox - control toolbox or
Forms

The location may well answer the second, but not if it is a worksheet.

--
Regards,
Tom Ogilvy

"Minitman" wrote in message
...
Greetings,

If the entry that I just made to the ComboBox is not in the list, I
have a ControlButton that I click on that will copy the entry to the
list and then resort the list. What I want to do is have the ComboBox
do this on exiting without having to click on the ControlButton.

Any one have any ideas as to how this can be done?

TIA

-Minitman

Perhaps Mr. Ogilvy has a different idea in mind, but I would think that
irregardless of where the combobox was located, initiation of the
controlbutton commands can be called from an appropriate location. For
example, if the user has to close a nonmodal window that contains the data to
be updated in the combobox, then the combobox is to be updated by simply
calling the function that is the "controlbutton". If the combo box is in the
spreadsheet, and we are talking about closing the workbook, then in the
OnClose/ or OnExit function the list can be updated, although what good that
does is left to question. *smirk* as the file is closing we're updating a
list of items that will have to be populated later.

At anyrate, if your controlbutton is called Button1 and there is a control
function (OnClick_Button1) that performs action, and assuming that there is a
separate button on the same Form, like Close this Window (OnForm_Close), then
within OnForm_Close, call OnClick_Button1 and it will perform the actions you
have described/desired. An other option would be to perform the action of
calling the OnClick_Button1 function when the ComboBox loses focus. You
would want to perform some typical tests before calling the OnClick_Button1
function/sub-routine, to ensure that the data is "unique" or at least not
incomplete.

My two cents worth anyways.


"Tom Ogilvy" wrote:

Where is the combobox located. What type of combobox - control toolbox or
Forms

The location may well answer the second, but not if it is a worksheet.

--
Regards,
Tom Ogilvy

"Minitman" wrote in message
...
Greetings,

If the entry that I just made to the ComboBox is not in the list, I
have a ControlButton that I click on that will copy the entry to the
list and then resort the list. What I want to do is have the ComboBox
do this on exiting without having to click on the ControlButton.

Any one have any ideas as to how this can be done?

TIA

-Minitman

Hey Tom,

Thanks for the reply.

The ComboBox is located on a UserForm called UserForm1. The list is
located on a sheet called "List". Each ComboBox uses a named range
which is a column on "List" as the row source.

I was trying to keep the post short, I guess I succeeded too well.
<G

-Minitman


On Mon, 3 Oct 2005 12:27:01 -0400, "Tom Ogilvy"
wrote:

Where is the combobox located. What type of combobox - control toolbox or
Forms

The location may well answer the second, but not if it is a worksheet.

--
Regards,
Tom Ogilvy

"Minitman" wrote in message
.. .
Greetings,

If the entry that I just made to the ComboBox is not in the list, I
have a ControlButton that I click on that will copy the entry to the
list and then resort the list. What I want to do is have the ComboBox
do this on exiting without having to click on the ControlButton.

Any one have any ideas as to how this can be done?

TIA

-Minitman

Use the exit event, then code like this I would think

Dim rng as Range, sVal as String, res as Variant
set rng = Range(combobox1.Rowsource)
sVal = combobox1.Value
res = Application.Match(sVal,rng,0)
if iserror(res) then
rng.offset(rng.rows.count,0).Resize(1,1).Value = _
combobox1.value
combobox1.rowSource = ""
rng.resize(rng.rows.count+1,1).Name = "ABC"
combobox1.rowSource = "ABC"
combobox1.Value = sVale
end if

If the values in the range are numbers, you might have to do
Match(cdbl(sVal), . . .

--
Regards,
Tom Ogilvy



"Minitman" wrote in message
...
Hey Tom,

Thanks for the reply.

The ComboBox is located on a UserForm called UserForm1. The list is
located on a sheet called "List". Each ComboBox uses a named range
which is a column on "List" as the row source.

I was trying to keep the post short, I guess I succeeded too well.
<G

-Minitman


On Mon, 3 Oct 2005 12:27:01 -0400, "Tom Ogilvy"
wrote:

Where is the combobox located. What type of combobox - control toolbox
or
Forms

The location may well answer the second, but not if it is a worksheet.

--
Regards,
Tom Ogilvy

"Minitman" wrote in message
.. .
Greetings,

If the entry that I just made to the ComboBox is not in the list, I
have a ControlButton that I click on that will copy the entry to the
list and then resort the list. What I want to do is have the ComboBox
do this on exiting without having to click on the ControlButton.

Any one have any ideas as to how this can be done?

TIA

-Minitman

Hey Tom,

This looks good, I do have some questions, what does the resize do
and what is the "ABC" for?

The rowsource for CB1 (ComboBox1) is a named range called "lVender"
which has this code:
=OFFSET(List!$B$2,0,0,COUNTA(List!$B:$B),1)

Most of the ComboBoxes are text (all but 2) so the Match(cdbl(sVal)
will come in handy for those 2.

I am trying to understand the code so that I can modify it
intelligently.

Like why is the row source emptied and then given a different name
( rng.resize(rng.rows.count+1,1).Name = "ABC"
combobox1.rowSource = "ABC")

Thanks for the assistance.

-Minitman
On Mon, 3 Oct 2005 14:34:36 -0400, "Tom Ogilvy"
wrote:

Use the exit event, then code like this I would think

Dim rng as Range, sVal as String, res as Variant
set rng = Range(combobox1.Rowsource)
sVal = combobox1.Value
res = Application.Match(sVal,rng,0)
if iserror(res) then
rng.offset(rng.rows.count,0).Resize(1,1).Value = _
combobox1.value
combobox1.rowSource = ""
rng.resize(rng.rows.count+1,1).Name = "ABC"
combobox1.rowSource = "ABC"
combobox1.Value = sVale
end if

If the values in the range are numbers, you might have to do
Match(cdbl(sVal), . . .

--
Regards,
Tom Ogilvy



"Minitman" wrote in message
.. .
Hey Tom,

Thanks for the reply.

The ComboBox is located on a UserForm called UserForm1. The list is
located on a sheet called "List". Each ComboBox uses a named range
which is a column on "List" as the row source.

I was trying to keep the post short, I guess I succeeded too well.
<G

-Minitman


On Mon, 3 Oct 2005 12:27:01 -0400, "Tom Ogilvy"
wrote:

Where is the combobox located. What type of combobox - control toolbox
or
Forms

The location may well answer the second, but not if it is a worksheet.

--
Regards,
Tom Ogilvy

"Minitman" wrote in message
.. .
Greetings,

If the entry that I just made to the ComboBox is not in the list, I
have a ControlButton that I click on that will copy the entry to the
list and then resort the list. What I want to do is have the ComboBox
do this on exiting without having to click on the ControlButton.

Any one have any ideas as to how this can be done?

TIA

-Minitman

ABC was the name I gave it since you didn't share information like lVender
is the name or that lVender is a dynamic range.

Dim rng as Range, sVal as String, res as Variant
set rng = Range(combobox1.Rowsource)
sVal = combobox1.Value
res = Application.Match(sVal,rng,0)
if iserror(res) then
rng.offset(rng.rows.count,0).Resize(1,1).Value = _
combobox1.value
combobox1.rowSource = ""
combobox1.rowSource = "lVender"
combobox1.Value = sVale
end if

The easiest way to refresh the list for the combobox is to reassign the
rowsource.

--
Regards,
Tom Ogilvy


"Minitman" wrote in message
...
Hey Tom,

This looks good, I do have some questions, what does the resize do
and what is the "ABC" for?

The rowsource for CB1 (ComboBox1) is a named range called "lVender"
which has this code:
=OFFSET(List!$B$2,0,0,COUNTA(List!$B:$B),1)

Most of the ComboBoxes are text (all but 2) so the Match(cdbl(sVal)
will come in handy for those 2.

I am trying to understand the code so that I can modify it
intelligently.

Like why is the row source emptied and then given a different name
( rng.resize(rng.rows.count+1,1).Name = "ABC"
combobox1.rowSource = "ABC")

Thanks for the assistance.

-Minitman
On Mon, 3 Oct 2005 14:34:36 -0400, "Tom Ogilvy"
wrote:

Use the exit event, then code like this I would think

Dim rng as Range, sVal as String, res as Variant
set rng = Range(combobox1.Rowsource)
sVal = combobox1.Value
res = Application.Match(sVal,rng,0)
if iserror(res) then
rng.offset(rng.rows.count,0).Resize(1,1).Value = _
combobox1.value
combobox1.rowSource = ""
rng.resize(rng.rows.count+1,1).Name = "ABC"
combobox1.rowSource = "ABC"
combobox1.Value = sVale
end if

If the values in the range are numbers, you might have to do
Match(cdbl(sVal), . . .

--
Regards,
Tom Ogilvy



"Minitman" wrote in message
.. .
Hey Tom,

Thanks for the reply.

The ComboBox is located on a UserForm called UserForm1. The list is
located on a sheet called "List". Each ComboBox uses a named range
which is a column on "List" as the row source.

I was trying to keep the post short, I guess I succeeded too well.
<G

-Minitman


On Mon, 3 Oct 2005 12:27:01 -0400, "Tom Ogilvy"
wrote:

Where is the combobox located. What type of combobox - control
toolbox
or
Forms

The location may well answer the second, but not if it is a worksheet.

--
Regards,
Tom Ogilvy

"Minitman" wrote in message
.. .
Greetings,

If the entry that I just made to the ComboBox is not in the list, I
have a ControlButton that I click on that will copy the entry to the
list and then resort the list. What I want to do is have the
ComboBox
do this on exiting without having to click on the ControlButton.

Any one have any ideas as to how this can be done?

TIA

-Minitman

Hey Tom,

I tried it. It almost works.

I had to make a couple of changes:

set rng = Range(combobox1.Rowsource)

I had to change to

set rng = Range("lVender")

"lVender" is the name of the range as it appears in the CB1.RowSource.

I had to change this because the VBA errored out the original.

In this configuration, the code does sole odd things. First, there is
a blank cell at the bottom of the list and then the item that was
added appears. I do not need any empty spaces in a list.

The value in CB1 is empty and I need it to be what I entered.

It also needs to be sorted.

I have a button that adds and sorts but I am not sure how to I
corporate it into this code. (See GB's broken thread reply)

as always, any help is appreciated.

-Minitman
On Mon, 03 Oct 2005 14:34:33 -0500, Minitman
wrote:

Hey Tom,

This looks good, I do have some questions, what does the resize do
and what is the "ABC" for?

The rowsource for CB1 (ComboBox1) is a named range called "lVender"
which has this code:
=OFFSET(List!$B$2,0,0,COUNTA(List!$B:$B),1)

Most of the ComboBoxes are text (all but 2) so the Match(cdbl(sVal)
will come in handy for those 2.

I am trying to understand the code so that I can modify it
intelligently.

Like why is the row source emptied and then given a different name
( rng.resize(rng.rows.count+1,1).Name = "ABC"
combobox1.rowSource = "ABC")

Thanks for the assistance.

-Minitman
On Mon, 3 Oct 2005 14:34:36 -0400, "Tom Ogilvy"
wrote:

Use the exit event, then code like this I would think

Dim rng as Range, sVal as String, res as Variant
set rng = Range(combobox1.Rowsource)
sVal = combobox1.Value
res = Application.Match(sVal,rng,0)
if iserror(res) then
rng.offset(rng.rows.count,0).Resize(1,1).Value = _
combobox1.value
combobox1.rowSource = ""
rng.resize(rng.rows.count+1,1).Name = "ABC"
combobox1.rowSource = "ABC"
combobox1.Value = sVale
end if

If the values in the range are numbers, you might have to do
Match(cdbl(sVal), . . .

--
Regards,
Tom Ogilvy



"Minitman" wrote in message
. ..
Hey Tom,

Thanks for the reply.

The ComboBox is located on a UserForm called UserForm1. The list is
located on a sheet called "List". Each ComboBox uses a named range
which is a column on "List" as the row source.

I was trying to keep the post short, I guess I succeeded too well.
<G

-Minitman


On Mon, 3 Oct 2005 12:27:01 -0400, "Tom Ogilvy"
wrote:

Where is the combobox located. What type of combobox - control toolbox
or
Forms

The location may well answer the second, but not if it is a worksheet.

--
Regards,
Tom Ogilvy

"Minitman" wrote in message
.. .
Greetings,

If the entry that I just made to the ComboBox is not in the list, I
have a ControlButton that I click on that will copy the entry to the
list and then resort the list. What I want to do is have the ComboBox
do this on exiting without having to click on the ControlButton.

Any one have any ideas as to how this can be done?

TIA

-Minitman