Form places number in database from combo box
 

Ok, thanks to Patrick I have my form working. Of coarse there is a but, the
combo box returns a number. Do I have to write a function in the macro to
lookup the correct text? I have used vlookup in excel but never in a macro.
Help please!
--
Though daily learning, I LOVE EXCEL!
Jennifer

Form places number in database from combo box
 

This is what it looks like thus far:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("ProduceData")

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

' check for an invoice number
If Trim(Me.txtInvoice.Value) = "" Then
Me.txtInvoice.SetFocus
MsgBox "Please enter an invoice number"
Exit Sub
End If

' check for a date
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter a date"
Exit Sub
End If
' copy the data to the database
ws.Cells(iRow, 1).Value = "=R[-1]C+1"
ws.Cells(iRow, 2).Value = Me.txtInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboVend.Value
ws.Cells(iRow, 5).Value = Me.cboRan.Value
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 10).Value = Me.txtRepakHrs.Value
ws.Cells(iRow, 11).Value = Me.txtRepakQty.Value
ws.Cells(iRow, 12).Value = "Purchase"
Me.txtInvoice.SetFocus

End Sub

"Jennifer" wrote:

Ok, thanks to Patrick I have my form working. Of coarse there is a but, the
combo box returns a number. Do I have to write a function in the macro to
lookup the correct text? I have used vlookup in excel but never in a macro.
Help please!
--
Though daily learning, I LOVE EXCEL!
Jennifer

Form places number in database from combo box
 

Assuming by form you mean Userform, you should change the BoundColumn
property of the combobox to 1 rather than zero and it will return what is
displayed in the combobox. This also assumes a single column combobox.

--
Regards,
Tom Ogilvy


"Jennifer" wrote in message
...
This is what it looks like thus far:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("ProduceData")

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

' check for an invoice number
If Trim(Me.txtInvoice.Value) = "" Then
Me.txtInvoice.SetFocus
MsgBox "Please enter an invoice number"
Exit Sub
End If

' check for a date
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter a date"
Exit Sub
End If
' copy the data to the database
ws.Cells(iRow, 1).Value = "=R[-1]C+1"
ws.Cells(iRow, 2).Value = Me.txtInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboVend.Value
ws.Cells(iRow, 5).Value = Me.cboRan.Value
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 10).Value = Me.txtRepakHrs.Value
ws.Cells(iRow, 11).Value = Me.txtRepakQty.Value
ws.Cells(iRow, 12).Value = "Purchase"
Me.txtInvoice.SetFocus

End Sub

"Jennifer" wrote:

Ok, thanks to Patrick I have my form working. Of coarse there is a but,
the
combo box returns a number. Do I have to write a function in the macro
to
lookup the correct text? I have used vlookup in excel but never in a
macro.
Help please!
--
Though daily learning, I LOVE EXCEL!
Jennifer