I have a combobox on a userform that is linked to a named range t
provide a list for users to select. The form works well in feedin
data from the form to a row in the worksheet. My problem is that
can't get the userform to feed data to the worksheet when the use
inputs new data into the combobox. If new data is added to th
combobox I would like this data to be fed to the next empty row belo
the row already populated on the worksheet

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

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

Maybe you could use some of the techniques that Debra Dalgleish used:
http://contextures.com/xlUserForm01.html

She used textboxes, but the way she wrote to the worksheet should be pretty much
the same.

buckchow wrote:

I have a combobox on a userform that is linked to a named range to
provide a list for users to select. The form works well in feeding
data from the form to a row in the worksheet. My problem is that I
can't get the userform to feed data to the worksheet when the user
inputs new data into the combobox. If new data is added to the
combobox I would like this data to be fed to the next empty row below
the row already populated on the worksheet.

--
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

I previously reviewed the website but without the ability to add ne
data to the textbox there isn't a clear connection to the process wit
a combobox. if I use one of the items already listed in the combobo
everything works fine and all the data from the userform is copied t
the worksheet correctly. It's just when I try adding a new item to th
combobox that nothing gets copied over to the worksheet

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

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

Debra has this in her code:

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

This'll work to find the next available row.

And she has these lines in the code:
'check for a part number
If Trim(Me.txtPart.Value) = "" Then
Me.txtPart.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtPart.Value

You could change it to:
'check for a part number
If Trim(Me.Combobox1.Value) = "" Then
Me.txtPart.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.combobox1.Value

======
If this doesn't help, you may want to post more of your code.






buckchow wrote:

I previously reviewed the website but without the ability to add new
data to the textbox there isn't a clear connection to the process with
a combobox. if I use one of the items already listed in the combobox
everything works fine and all the data from the userform is copied to
the worksheet correctly. It's just when I try adding a new item to the
combobox that nothing gets copied over to the worksheet.

--
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

I have attached my code to illustrate my problem. The current cod
allows a user to click on the userform combobox (txtBatch1), select a
item, enter data in the other textboxes on the userform, and transfe
the data to the corresponding row on the worksheet that matched th
combobox selection. I also want the user to be able to enter a ne
item into the combobox, enter data in the other textboxes, and transfe
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 Su

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

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

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

It works!!!!!! The only problem is that it enters the new data at ro
65000 in the worksheet instead of the next empty row from the top

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

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

Also, if I enter another new item in the combox the data overrides th
previous new item saved to the worksheet

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

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

What did you define for the rowsource?

Did you you a dynamic name?

buckchow wrote:

It works!!!!!! The only problem is that it enters the new data at row
65000 in the worksheet instead of the next empty row from the top.

--
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

The rowsource is defined with a named range for the combobox

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

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

What does it refer to?

Did you make it dynamic so that it grows and contracts with your data?

buckchow wrote:

The rowsource is defined with a named range for the combobox.

--
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

Yes, it's a dynamic named range. It appears that I need to add a lin
to have the new data entered in the next available row and to set up
nullstring to prevent overwriting

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

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

I would have thought that this line:

Set DestCell = Range(strRange).Cells(Range(strRange).Rows.Count + 1, 1)

put the next entry directly under the last used entry in that strRange.

Maybe you could add some:

Debug.print range(strrange).address
and
debug.print destcell.address

to see what is really being used.


buckchow wrote:

Yes, it's a dynamic named range. It appears that I need to add a line
to have the new data entered in the next available row and to set up a
nullstring to prevent overwriting.

--
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

My mistake Dave. I thought I had set it up as a dynamic range but i
fact it was just a named range. After changing it to a dynamic rang
things seem to be more the norm. The weird thing though is the firs
time a new number is added to the combobox it puts the data one ro
down from the next blank row on the spreadsheet. Additional ne
entries after the initial entry are added correctly in the next empt
row

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

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

After you create the name, use Edit|Goto and type that name.

My bet is that your range is too large.


buckchow wrote:

My mistake Dave. I thought I had set it up as a dynamic range but in
fact it was just a named range. After changing it to a dynamic range
things seem to be more the norm. The weird thing though is the first
time a new number is added to the combobox it puts the data one row
down from the next blank row on the spreadsheet. Additional new
entries after the initial entry are added correctly in the next empty
row.

--
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

The dynamic range shown below is the one I'm using but it doesn't sho
up in the Edit / GoTo box although it does show up in the Insert / Nam
/ Define box.

=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A),1

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

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

If there's something in A1 (a header is common), then the count is one more than
you want:

So you can just subtract that header row:
=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A)-1,1)

Or you can do something not as pretty:
=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A2:$A65536),1)

(I like the top and make sure that there's a header.)

And that's the way dynamic ranges work--they don't show up. But you can still
type it in the Edit|Goto dialog.

buckchow wrote:

The dynamic range shown below is the one I'm using but it doesn't show
up in the Edit / GoTo box although it does show up in the Insert / Name
/ Define box.

=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A),1)

--
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

I think you did, too!

Glad you got it working.

buckchow wrote:

Thanks for all your help Dave! Your suggestion worked perfectly and now
my form is working the way I envisioned. As complicated as this project
was, with 3 different forms entering data into the same worksheet, you
were the only one who hung in there.

Thanks again,
Don

--
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