ComboBox: Add user entered text to combobox list 1

[MsBecca]

How do I add text that a user enters in a combobox to its already existing list?

My combo box drop-down list is populated with data contained in an Excel worksheet range of (IS1:IS255).
ROW SOURCE PROPERTY is set to this range.
How do I then add user entered text into my combo box list so that my range will increase to IS1:IS256, so the new entry will be added to my combo box. How is this done?

 

[SkipVought]

MsBecca,

Coupla ways to accomplish the objective.

Dynamic Ranges:

I like to use the OFFSET worksheet function in the Insert/Name/Define window

Type a range name in the Names in workbook textbox

In refersto:

=OFFSET(

click cell where LIST DATA begins -- mine is Pac3BOM_Indented_b!$A$2 -- whic gives you

=OFFSET(Pac3BOM_Indented_b!$A$2

then zero range offset for row/col

=OFFSET(Pac3BOM_Indented_b!$A$2,0,0,

then define the number or rows/cols in the range

=OFFSET(Pac3BOM_Indented_b!$A$2,0,0,counta(Pac3BOM_Indented_b!$A:$A)-1,1)

row range is the count of items in the column minus 1/col range is 1

So after you have defined your list range, then use that reference in the ListFillRange

Put the new value from the combobox at the end of the list

with YourListName
   Cells(.Rows.Count+.Row, .Column).Value = combobox1.value
end with

VOLA!



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MsBecca]

Skip Vought
Thanks for replying to my post, but I still cannot get the code to work. Where do I put the While....End Loop? Any help you provide me would be greatly appreciated.

Here is my current code w/your code added where I thought it should go:

Private Sub cmdEnterPrintClearRec_Click()

'Move to the next empty row within the Worksheet object

NextRow = _
Application.WorksheetFunction.CountA(Range("A:A&quot) + 1

'Declare variable for the various textbox entries on the form
Party = txtParty
Party1 = txtP1
Party2 = txtP2
Party3 = txtP3
Party4 = txtP4
Party5 = txtP5
AgrTitle = Me.cboAgrT.Text
EffDate = Me.txtEffDate.Text
ExpDate = Me.txtExpDate.Text
OptDate = Me.txtOptExDate.Text
Geography = Me.txtGeo
Products = Me.txtProd

' Instructs VB to input the values entered in the form into an Excel worksheets
Cells(NextRow, 1) = Party
Cells(NextRow, 2) = Party1
Cells(NextRow, 3) = Party2
Cells(NextRow, 4) = Party3
Cells(NextRow, 5) = Party4
Cells(NextRow, 6) = Party5
Cells(NextRow, 7) = AgrTitle

'Display an empty cell if user does not enter an Effective Date

On Error GoTo ErrMsg1
If Me.txtEffDate = "" Then
Cells(NextRow, 8) = ""

ElseIf IsDate(Me.txtEffDate) = False Then Err.Raise 11
EffDate = Me.txtEffDate
Cells(NextRow, 8) = EffDate
Else
Cells(NextRow, 8) = CDate(EffDate)
End If

'Display an empty cell if user does not enter an Expiration Date

On Error GoTo ErrMsg2
If Me.txtExpDate.Text = "" Then
Cells(NextRow, 9) = ""

ElseIf IsDate(Me.txtExpDate) = False Then Err.Raise 11
ExpDate = Me.txtEffDate
Cells(NextRow, 9) = ExpDate
Else
Cells(NextRow, 9) = CDate(ExpDate)
End If

'If user does not enter an Option Exercise Date, display an empty cell

On Error GoTo ErrMsg3
If Me.txtOptExDate.Text = "" Then
Cells(NextRow, 10) = ""

ElseIf IsDate(Me.txtOptExDate) = False Then Err.Raise 11
OptDate = Me.txtOptExDate
Cells(NextRow, 10) = OptDate
Else
Cells(NextRow, 10) = CDate(OptDate)
End If

Cells(NextRow, 11) = Geography
Cells(NextRow, 12) = Products

'Print User Form

UserForm1.PrintForm

'Add new value from combobox to the end of list

On Error Resume Next

With comboboxlist
Cells(.Rows.Count + .Row, .Column).Value = cboAgrT.Value
End With

'Clear the UserForm for new entries

Me.txtParty = ""
Me.txtP1 = ""
Me.txtP2 = ""
Me.txtP3 = ""
Me.txtP4 = ""
Me.txtP5 = ""
Me.txtEffDate = ""
Me.txtExpDate = ""
Me.txtOptExDate = ""
Me.txtGeo = ""
Me.txtProd = ""
Me.txtGeo = ""

'Set Focus to txtParty

Me.txtParty.SetFocus

Exit Sub

ErrMsg1:

Me.txtEffDate = InputBox("Please enter an EFFECTIVE DATE in proper format:" & vbCrLf & " DD-MM-YYYY or DD/MM/YYYY", "Effective Date&quot
Resume Next

ErrMsg2:
Me.txtExpDate = InputBox("Please enter an EXPIRATION DATE in proper format:" & vbCrLf & " DD-MM-YYYY or DD/MM/YYYY", "Expiration Date&quot
Resume Next

ErrMsg3:
Me.txtOptExDate = InputBox("Please enter an OPTION EXERCISE DATE" & vbCrLf & "in proper format: DD-MM-YYYY or DD/MM/YYYY", "Option Exercise Date&quot
Resume Next
End Sub

Please help me....I am getting desperate.
Thank You, MsBecca

 

[SkipVought]

I may have missled you...

with [YourListName]
   Cells(.Rows.Count+.Row, .Column).Value = combobox1.value
end with

This syntax is identical to

with Range("YourListName")

and this must be the list range that is referenced.

Sorry

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MsBecca]

Sorry Skip...Don't mean to be a pest, but I adjusted my code as you requested, now I am getting the following error message:

Run-time Error 1004 - Method 'Range' of object '_Global' failed

I've even tried putting the With...End Loop in the Combobox.Change Event and still get the same error.
I honestly and truly do not know what I am doing wrong.

Is there another way I can add user-entered text to combobox list instead of using OFFSET/With...End Loop?