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

[Minitman]

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

 

[Tom Ogilvy]

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.

 

[Guest]

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.

 

[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

 

[Tom Ogilvy]

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), . . .

 

[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

 

[Tom Ogilvy]

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.

 

[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

 

[Tom Ogilvy]

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.

 

[Minitman]

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

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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

 

[Minitman]

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

 

[Guest]

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.

 

[Guest]

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