entering new data thru combobox

[buckchow]

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

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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]

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

 

[buckchow]

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

 

[Dave Peterson]

What did you define for the rowsource?

Did you you a dynamic name?

 

[buckchow]

The rowsource is defined with a named range for the combobox

 

[Dave Peterson]

What does it refer to?

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

 

[buckchow]

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

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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

My bet is that your range is too large.

 

[buckchow]

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

 

[Dave Peterson]

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]

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

Thanks again,
Do

 

[Dave Peterson]

I think you did, too!

Glad you got it working.

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