Getting input from combobox to update sheet

[John G.]

In a userform I'm have a Combobox (CB) that holds names. When the routine is
first run list for the CB is empty. The user then inputs a name and this is
to be stored for subsequent uses of the workbook. Any other time the form is
opened, the ListIndex = 0 will show the user's name for confirmation.

The following is another option that I'm contemplating but I need to get the
single entry done first: "If another user needs to, they can input their
name and it is then added to the top of the list, so that the next time the
form is opened this new entry will be the default name shown on the form and
the previous entries would show in the dropdown as secondary names."

It is my understanding that manual input into a combobox can be stored back
to a sheet, preferably the list for the combobox.

Can't seem to find a way to start this that works. I tried using CB_Exit to
allow for input but it doesn't copy the input back to the named range on the
sheet. Also, I don't seem to getting the new entry into the value property
after it's typed in.

--------------------------------------------------
' CBName is the combobox and Cardholder is the named range

Private Sub CBName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim CHName As String
CHName = Sheets("Carddata").Range("Cardholder").Cells(1, 1).Value
If CHName = "" Then
CHName = Me.CBName.Value
End If
End Sub
----------------------------------------------------

 

[Jacob Skaria]

Use the Before Update event of the control to store data; like the below. Try
and feedback..

Private Sub CBName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If WorksheetFunction.CountIf(Sheets("Carddata"). _
Range("A:A"), ComboBox1.Text) = 0 Then
lngRow = Sheets("Carddata").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Carddata").Range("A" & lngRow) = CBName.Text
End If
End Sub

If this post helps click Yes

 

[jamescox]

You've got a bit of a misconception of how assignments work.

You Dim'ed CHName as a string and then assigned the value of that
string to the value of a cell:

CHName = Sheets("Carddata").Range("Cardholder").Cells(1, 1).Value

later you conditionally assigned a new value to CHName

CHName = Me.CBName.Value

which overwrote the original assignment of the CHName - but heres's the
significant part - just because the original assignment of CHName's
value was to the value of a cell in the Cardholder named range, VBA
doesn't back-propagate a change in CHName's value to the cell value in
the named range.

To accomplish that, you would need something like

Sheets("Carddata").Range("Cardholder").Cells(1).Value =
Me.CBName.Value

As an aside, note that I replaced your .Cells(1,1) with .Cells(1). If
your names are to be stored in a single column named range Cardholder,
you can either use the .Cells(1) or Cells(1,1) notation, BUT the second
name in the named range would need to have a reference of .Cells(2) or
..Cells(2,1). Since the ,1 isn't doing anything for you (except perhaps
confusing you should you try to use .Cells(1,2) to get the value of the
second name in the Cardholder named range) I'd recommend the simpler
notation.

Also, note that if you want the most-recently entered name to be the
first choice, you are going to have to do some management of the names
in the Cardholder named range - that is, you are going to have to shift
any other names already in the Cardholder named range down before adding
the newest one to .Cells(1) - unless you work out some way to load the
names into the combobox from the bottom of the Cardholder named range up
to the top - in which case the newest name could be at the bottom of the
Cardholder range.

Hope this helps some...

 

[Jacob Skaria]

Private Sub CBName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If WorksheetFunction.CountIf(Sheets("Carddata"). _
Range("A:A"), CBName.Text) = 0 Then
lngRow = Sheets("Carddata").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Carddata").Range("A" & lngRow) = CBName.Text
End If
End Sub


If this post helps click Yes

 

[OssieMac]

Hi John,

With the following code you need to initially name one cell only as
Cardholder. The code assumes this cell to be Cell A1 on worksheet Carddata.

As the names are added in the combo box, if not already existing, they are
added to the top of the list and the named range for the combo is redefined
with the expanded range.

The RowSource property for the combo needs to be set to Cardholder. (Can't
do this until after cell is named Cardholder.)

The MatchRequired property for the combo needs to be false.

Do a Find and Replace on the following code to change the sub name from
ComboBox1 to match the name of your combo box.

Private Sub ComboBox1_AfterUpdate()
Dim comboEntry As Variant
Dim rngTofind As Range
Dim lngRows As Long

'Save the Combo box entry to a variable
comboEntry = ComboBox1.Value

'Test if combo box entry exists in Rowsource
With Sheets("Carddata")
Set rngTofind = .Range("Cardholder") _
.Find(What:=comboEntry, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
End With

'If entry not found then add it to named range
If rngTofind Is Nothing Then
lngRows = ComboBox1.ListCount + 1

With Sheets("Carddata")
'Move existing Cardholder range down 1 cell
'to allow new entry to be at top of range.
.Range("Cardholder").Cut _
Destination:=.Range("A2")

'Redefine Cardholder range to include
'additional cell for new combo box entry.
.Range(.Cells(1, 1), _
.Cells(lngRows, 1)) _
.Name = "Cardholder"

'Populate the first cell in the named range
'with the new combo box entry.
.Cells(1, 1) = comboEntry
End With

'Update the Rowsource for the combo box
ComboBox1.RowSource = ("Carddata!Cardholder")
End If

End Sub

 

[John G.]

Thanks Jacob and James for the suggestions and advice...I'll let you when I
get it working.

 

[John G.]

OssieMac...This worked with just one small change. Kept getting a "NULL"
error when I got to CBName.Value. Changed it to CBName.Text and it worked
great including the inserting of new names.

Thanks

 

[OssieMac]

Hi again John,

I am assuming that it is this line giving you the problem.
comboEntry = ComboBox1.Value
I tested the code in xl2007 and xl2002 and no problems. However, the code
also works with comboEntry = ComboBox1.Text.

Having said that, I have found another problem. If you define the name for
the first cell and leave the first cell empty then the empty cell gets pushed
down to allow the next new entry and it is continually pushed down and this
gives you a blank line at the bottom of the RowSource. The following modified
code tests for a blank first cell and if blank it does not push the list
down. (I have changed Value to Text).

Don't forget to use Find/Replace for ComboBox1.

Private Sub ComboBox1_AfterUpdate()
Dim comboEntry As Variant
Dim rngTofind As Range
Dim lngRows As Long

'Save the Combo box entry to a variable
comboEntry = ComboBox1.Text

'Test if combo box entry exists in Rowsource
With Sheets("Carddata")
Set rngTofind = .Range("Cardholder") _
.Find(What:=comboEntry, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
End With

'If entry not found then add it to named range
If rngTofind Is Nothing Then
lngRows = ComboBox1.ListCount + 1

With Sheets("Carddata")
'Move existing Cardholder range down 1 cell
'to allow new entry to be at top of range.
'(Move only if 1st cell in not blank.)
If .Range("Cardholder").Cells(1, 1) <> "" Then
.Range("Cardholder").Cut _
Destination:=.Range("A2")

'Redefine Cardholder range to include
'additional cell for new combo box entry.
.Range(.Cells(1, 1), _
.Cells(lngRows, 1)) _
.Name = "Cardholder"
End If

'Populate the first cell in the named range
'with the new combo box entry.
.Cells(1, 1) = comboEntry
End With

'Update the Rowsource for the combo box
ComboBox1.RowSource = ("Carddata!Cardholder")
End If

End Sub

 

[John G.]

Thx OssieMac...I noticed that when I was ran it. Really appreciate the
update. JG