So the .rowsource refers to a dynamic range name???

If it doesn't, then you may want it to.

Check how Debra Dalgleish does it:
http://contextures.com/xlNames01.html#Dynamic

This is completely untested. I didn't take the time to set up a test
environment.

Option Explicit
Private Sub CommandButton1_Click()

Dim strRange As String
Dim DestCell As Range

If txtbatch1 = vbNullString Then
MsgBox "No batch number", vbCritical
txtbatch1.SetFocus
Exit Sub
End If

strRange = txtbatch1.RowSource

If txtbatch1.ListIndex -1 Then
Set DestCell = Range(strRange).Cells(txtbatch1.ListIndex + 1, 1)
Else
Set DestCell = Range(strRange).Cells(Range(strRange).Rows.Count + 1, 1)
DestCell.Value = txtbatch1
End If

With DestCell
.Offset(0, 1) = IIf(txtDate1 < vbNullString, txtDate1, .Offset(0, 1))
.Offset(0, 2) = IIf(txtCust1 < vbNullString, txtCust1, .Offset(0, 2))
.Offset(0, 3) = IIf(txtBoard1 < vbNullString, _
txtBoard1, .Offset(0, 3))
.Offset(0, 4) = IIf(txtSerial1 < vbNullString, _
txtSerial1, .Offset(0, 4))
.Offset(0, 5) = IIf(txtQty1 < vbNullString, txtQty1, .Offset(0, 5))
.Offset(0, 16) = IIf(txtStatus1 < vbNullString, _
txtStatus1, .Offset(0, 16))
.Offset(0, 17) = IIf(txtNotes < vbNullString, _
txtNotes, .Offset(0, 17))
End With

'clear the data
Me.txtbatch1.Value = ""
Me.txtDate1.Value = ""
Me.txtCust1.Value = ""
Me.txtBoard1.Value = ""
Me.txtSerial1.Value = ""
Me.txtQty1.Value = ""
Me.txtStatus1.Value = ""
Me.txtNotes.Value = ""

Me.txtbatch1.SetFocus

End Sub

buckchow wrote:

I have attached my code to illustrate my problem. The current code
allows a user to click on the userform combobox (txtBatch1), select an
item, enter data in the other textboxes on the userform, and transfer
the data to the corresponding row on the worksheet that matched the
combobox selection. I also want the user to be able to enter a new
item into the combobox, enter data in the other textboxes, and transfer
all this information to the next available row in the worksheet.

Private Sub CommandButton1_Click()

Dim strRange As String

If txtBatch1 = vbNullString Then
MsgBox "No batch number", vbCritical
txtBatch1.SetFocus
Exit Sub

End If

strRange = txtBatch1.RowSource

If txtBatch1.ListIndex -1 Then

With Range(strRange).Cells(txtBatch1.ListIndex + 1, 1)

.Offset(0, 1) = IIf(txtDate1 < vbNullString, txtDate1, .Offset(0,
1))
.Offset(0, 2) = IIf(txtCust1 < vbNullString, txtCust1, .Offset(0,
2))
.Offset(0, 3) = IIf(txtBoard1 < vbNullString, txtBoard1,
.Offset(0, 3))
.Offset(0, 4) = IIf(txtSerial1 < vbNullString, txtSerial1,
.Offset(0, 4))
.Offset(0, 5) = IIf(txtQty1 < vbNullString, txtQty1, .Offset(0,
5))
.Offset(0, 16) = IIf(txtStatus1 < vbNullString, txtStatus1,
.Offset(0, 16))
.Offset(0, 17) = IIf(txtNotes < vbNullString, txtNotes, .Offset(0,
17))

End With
Else
With Range(strRange).Cells(Range(strRange).Rows.Count + 1, 1)
.Value = txtBatch1

End With

End If

txtBatch1 = vbNullString

'clear the data
Me.txtBatch1.Value = ""
Me.txtDate1.Value = ""
Me.txtCust1.Value = ""
Me.txtBoard1.Value = ""
Me.txtSerial1.Value = ""
Me.txtQty1.Value = ""
Me.txtStatus1.Value = ""
Me.txtNotes.Value = ""

Me.txtBatch1.SetFocus

End Sub

--
buckchow
------------------------------------------------------------------------
buckchow's Profile: http://www.officehelp.in/member.php?userid=5975
View this thread: http://www.officehelp.in/showthread.php?t=1323980

Posted from - http://www.officehelp.in

--

Dave Peterson