How Do I Get A ComboBox To Add Entry To It's List If Not In List
 

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

How Do I Get A ComboBox To Add Entry To It's List If Not In List
 

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

How Do I Get A ComboBox To Add Entry To It's List If Not In Li
 

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

How Do I Get A ComboBox To Add Entry To It's List If Not In List
 

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

How Do I Get A ComboBox To Add Entry To It's List If Not In List
 

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

How Do I Get A ComboBox To Add Entry To It's List If Not In List
 

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

How Do I Get A ComboBox To Add Entry To It's List If Not In List
 

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

How Do I Get A ComboBox To Add Entry To It's List If Not In List
 

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

How Do I Get A ComboBox To Add Entry To It's List If Not In List
 

In contrast, this worked perfectly for me: (using your posted definition
for lVendor)

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
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 = "lVendor"
ComboBox1.Value = sVal
End If
End Sub

I can't see anything about a broken thead from GB, but work with him if you
prefer.

--
Regards,
Tom Ogilvy


"Minitman" wrote in message
...
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

How Do I Get A ComboBox To Add Entry To It's List If Not In List
 

Hey Tom,

I tried this one and have two problems.

First: When I exit CB1 the code skips the first open space at the
bottom of the list and places the entry in the second open space.

Second: The variable sVal has the value of the entry before the if
statement but loses the value during execution of the if statement,
hence no entry in CB1 when completed.

Any suggestions?

-Minitman
On Mon, 3 Oct 2005 16:00:40 -0400, "Tom Ogilvy"
wrote:

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.

How Do I Get A ComboBox To Add Entry To It's List If Not In List
 

combobox1.Value = sVale

was a typo

combobox1.rowSource = "lVender"

should be lVendor

the code caused no spaces in the data, but then my definition of lVender
contained no spaces. I can't say what you have. As I said in the previous
post, the (revised) code worked perfectly for me.

--
Regards,
Tom Ogilvy



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

I tried this one and have two problems.

First: When I exit CB1 the code skips the first open space at the
bottom of the list and places the entry in the second open space.

Second: The variable sVal has the value of the entry before the if
statement but loses the value during execution of the if statement,
hence no entry in CB1 when completed.

Any suggestions?

-Minitman
On Mon, 3 Oct 2005 16:00:40 -0400, "Tom Ogilvy"
wrote:

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.

How Do I Get A ComboBox To Add Entry To It's List If Not In List
 

I guess you are using lVender, so you can ignore that. I used lVendor.

--
Regards,
Tom Ogilvy

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

I tried this one and have two problems.

First: When I exit CB1 the code skips the first open space at the
bottom of the list and places the entry in the second open space.

Second: The variable sVal has the value of the entry before the if
statement but loses the value during execution of the if statement,
hence no entry in CB1 when completed.

Any suggestions?

-Minitman
On Mon, 3 Oct 2005 16:00:40 -0400, "Tom Ogilvy"
wrote:

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.

How Do I Get A ComboBox To Add Entry To It's List If Not In List
 

If you have something in B1, then this definition

=OFFSET(List!$B$2,0,0,COUNTA(List!$B:$B),1)

will include a blank cell at the end of the range. since you set it up this
way, I assumed B1 was empty. if not, then you need to change the defintion
to
=OFFSET(List!$B$2,0,0,COUNTA(List!$B:$B)-1,1)

or to change the code to:

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
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
' change next line
rng.Offset(rng.Rows.Count-1, 0).Resize(1, 1).Value = _
ComboBox1.Value
ComboBox1.RowSource = ""
ComboBox1.RowSource = "lVendor"
ComboBox1.Value = sVal
End If
End Sub

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...
I guess you are using lVender, so you can ignore that. I used lVendor.

--
Regards,
Tom Ogilvy

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

I tried this one and have two problems.

First: When I exit CB1 the code skips the first open space at the
bottom of the list and places the entry in the second open space.

Second: The variable sVal has the value of the entry before the if
statement but loses the value during execution of the if statement,
hence no entry in CB1 when completed.

Any suggestions?

-Minitman
On Mon, 3 Oct 2005 16:00:40 -0400, "Tom Ogilvy"
wrote:

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.

How Do I Get A ComboBox To Add Entry To It's List If Not In List
 

Hey Tom,

Thanks for the tip on the OFFSET I was having problems with it and
couldn't figure out why, And yes there is a header row.

Thanks again for your help, it is appreciated.

-Minitman


On Mon, 3 Oct 2005 17:22:56 -0400, "Tom Ogilvy"
wrote:

If you have something in B1, then this definition

=OFFSET(List!$B$2,0,0,COUNTA(List!$B:$B),1)

will include a blank cell at the end of the range. since you set it up this
way, I assumed B1 was empty. if not, then you need to change the defintion
to
=OFFSET(List!$B$2,0,0,COUNTA(List!$B:$B)-1,1)

or to change the code to:

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
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
' change next line
rng.Offset(rng.Rows.Count-1, 0).Resize(1, 1).Value = _
ComboBox1.Value
ComboBox1.RowSource = ""
ComboBox1.RowSource = "lVendor"
ComboBox1.Value = sVal
End If
End Sub

How Do I Get A ComboBox To Add Entry To It's List If Not In Li
 

I Certainly do not think it is so much of a preference, but a merging of two
things/ideas. Minitman has gotten thus far with all of your help. I have
simply provided some pseudocode, to perform what he is trying to do. Now I
haven't reviewed the code you have helped him with, but basically he was
trying to get the actions of existing code (normally run by pressing a
button), to be performed "automatically" with the code that you have helped
him with. Thus, he does not have to rewrite everything, only make a call to
the appropriate function.

If I am not mistaken, adding:

Call ThisSubDoesSomething(passedVariables)

Just after the end if statement such that it reads:

end if
Call ThisSubDoesSomething(passedVariables)
End Sub
And it should perform the additional actions that you had programmed, plus
whatever it is that Mr. Ogilvy has helped you create.



"Tom Ogilvy" wrote:

In contrast, this worked perfectly for me: (using your posted definition
for lVendor)

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
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 = "lVendor"
ComboBox1.Value = sVal
End If
End Sub

I can't see anything about a broken thead from GB, but work with him if you
prefer.

--
Regards,
Tom Ogilvy


"Minitman" wrote in message
...
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

How Do I Get A ComboBox To Add Entry To It's List If Not In Li
 

Don't forget to give Tom credit for helping you out.

"Minitman" wrote:

Hey Tom,

Thanks for the tip on the OFFSET I was having problems with it and
couldn't figure out why, And yes there is a header row.

Thanks again for your help, it is appreciated.

-Minitman


On Mon, 3 Oct 2005 17:22:56 -0400, "Tom Ogilvy"
wrote:

If you have something in B1, then this definition

=OFFSET(List!$B$2,0,0,COUNTA(List!$B:$B),1)

will include a blank cell at the end of the range. since you set it up this
way, I assumed B1 was empty. if not, then you need to change the defintion
to
=OFFSET(List!$B$2,0,0,COUNTA(List!$B:$B)-1,1)

or to change the code to:

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
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
' change next line
rng.Offset(rng.Rows.Count-1, 0).Resize(1, 1).Value = _
ComboBox1.Value
ComboBox1.RowSource = ""
ComboBox1.RowSource = "lVendor"
ComboBox1.Value = sVal
End If
End Sub