combo box will not save text typed

  • Thread starter Thread starter john q public
  • Start date Start date
J

john q public

Hi i have a combobox that is using the listfillrange from another shee
in the same workbook.

The problem i have is that when the customer name is not already in th
datasheet that i use for the listfillrange and i go ahead and manual
type their name in the combobox and then save the document. When
reopen the document it deletes the name i typed in. It will save th
name and display it if i selected it from the drop down but not if
type it in manualy.

Anyone have any suggestions as to how i can get it to keep the data i
there so when i re-open the workbook it does not disappear
 
When you enter text into a combobox that allows data entry, the list index of
the combobox is set to -1. During the process of doing whatever your form is
doing, if the list index is -1, the text in the combo box must be added to
the list fill range on the other worksheet. The range must also be adjusted
to allow the extra record. Obviously, the file must be saved, too.

Dale Preuss
 
Try looking at the samples in this file:
http://theofficeexperts.com/downloads/UserformTraining.zip

Here is the code used in the example for adding a value to a combobo
that is bound to a worksheet

Code
-------------------

'This procedure adds a new item to a ComboBox that is
'bound to a worksheet.
Private Sub CommandButton2_Click()
Dim SourceData As Range
Dim Found As Object

Set SourceData = Range("listrange")
Set Found = Nothing
Set Found = SourceData.Find(ComboBox2.Value)

'If the item is not found in the list...
If Found Is Nothing Then
'...redefine the name ListRange
SourceData.Resize(SourceData.Rows.Count + 1, 1).Name = "ListRange"
'Add the new item to the end of the list on the worksheet
SourceData.Offset(SourceData.Rows.Count, 0).Resize(1, 1).Value = ComboBox2.Value
'Reset the list displayed in the ComboBox
ComboBox2.RowSource = Range("listrange").Address(external:=True)
End If
End Sub
 
Back
Top