Can't seem to get Combo Box on User Form
 

Hey there,
I have a user form I have been working with but I just can't seem to
get my Combo Boxes to work. No matter what I do I get an error
Run-Time Error 1004
Method Range of Object Worksheet Failed

The following is what I have so far: I put ******* where I am gettting
the error. I would appreciate any ideas. Oh yeah and this is very first
userform so go easy on me. I need lots of hand holding:)

Option Explicit

Private Sub btnClose_Click()
Unload Me
End Sub

Private Sub cboInvoice_Change()
ShowValue cboInvoice
End Sub

Private Sub cboCustomerID_Change()
ShowValue cboCustomerID
End Sub

Private Sub ShowValue(cbo As ComboBox)
If cbo.ListIndex = -1 Then Exit Sub
lblShow.Caption = cbo.Value

End Sub

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.cboInvoice.Value) = "" Then
Me.cboInvoice.SetFocus
MsgBox "Please enter or choose 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.cboInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboCustomerID.Value
ws.Cells(iRow, 5).Value = "None"
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 9).Value = Me.txtBoxSold.Value
ws.Cells(iRow, 10).Value = Me.txtPrice.Value
ws.Cells(iRow, 11).Value = Me.txtFrt.Value
ws.Cells(iRow, 13).Value = "0"
ws.Cells(iRow, 14).Value = "0"
ws.Cells(iRow, 12).Value = "Sale"

'select a produce item via button
If Me.optSnap.Value Then
ws.Cells(iRow, 6).Value = Me.optSnap.Caption
Else
ws.Cells(iRow, 6).Value = Me.optSno.Caption
End If

'clear data to enter new pallet
Me.txtPallet.Value = ""
Me.txtQty.Value = ""
Me.txtBoxSold.Value = ""
Me.txtPrice.Value = ""
Me.txtFrt.Value = ""
txtPallet.SetFocus
End Sub
Private Sub cmdNewInvoice_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.cboInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboCustomerID.Value
ws.Cells(iRow, 5).Value = "None"
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 9).Value = Me.txtBoxSold.Value
ws.Cells(iRow, 10).Value = Me.txtPrice.Value
ws.Cells(iRow, 11).Value = Me.txtFrt.Value
ws.Cells(iRow, 13).Value = "0"
ws.Cells(iRow, 14).Value = "0"
ws.Cells(iRow, 12).Value = "Sale"

'select a produce item via button
If Me.optSnap.Value Then
ws.Cells(iRow, 6).Value = Me.optSnap.Caption
Else
ws.Cells(iRow, 6).Value = Me.optSno.Caption
End If
'clear the data
Me.cboCustomerID = ""
Me.cboInvoice = ""
Me.txtPallet.Value = ""
Me.txtQty.Value = ""
Me.txtBoxSold.Value = ""
Me.txtPrice.Value = ""
Me.txtFrt.Value = ""
Me.cboInvoice.SetFocus
End Sub



Private Sub txtDate_Enter()
txtDate.Value = Format(Me.txtDate, "")
End Sub
Private Sub txtFrt_Enter()
txtFrt.Value = Format(Me.txtFrt.Value, "")
End Sub

Private Sub txtFrt_Exit(ByVal cancel As MSForms.ReturnBoolean)
txtFrt.Value = Format(Me.txtFrt.Value, "$#,###,###.00")
End Sub

Private Sub txtPrice_Enter()
'Places currency formatting on price
txtPrice.Value = Format(Me.txtPrice.Value, "")
End Sub
Private Sub txtPrice_Exit(ByVal cancel As MSForms.ReturnBoolean)
'as you exit then places currency formatting
txtPrice.Value = Format(Me.txtPrice.Value, "$#,###,###.00")
End Sub

Private Sub UserForm_Initialize()

Dim cItem As Range

With Me.cboCustomerID
*********For Each cItem In wksLookupLists.Range("CustomerIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboInvoice
For Each cItem In wksLookupLists.Range("InvoiceList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub

Can't seem to get Combo Box on User Form
 

Just had a quick look at the code, and I cannot see anywhere where
wsLookupLists is loaded with a value, although this would not give that
error in my view.

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
Hey there,
I have a user form I have been working with but I just can't seem to
get my Combo Boxes to work. No matter what I do I get an error
Run-Time Error 1004
Method Range of Object Worksheet Failed

The following is what I have so far: I put ******* where I am gettting
the error. I would appreciate any ideas. Oh yeah and this is very first
userform so go easy on me. I need lots of hand holding:)

Option Explicit

Private Sub btnClose_Click()
Unload Me
End Sub

Private Sub cboInvoice_Change()
ShowValue cboInvoice
End Sub

Private Sub cboCustomerID_Change()
ShowValue cboCustomerID
End Sub

Private Sub ShowValue(cbo As ComboBox)
If cbo.ListIndex = -1 Then Exit Sub
lblShow.Caption = cbo.Value

End Sub

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.cboInvoice.Value) = "" Then
Me.cboInvoice.SetFocus
MsgBox "Please enter or choose 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.cboInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboCustomerID.Value
ws.Cells(iRow, 5).Value = "None"
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 9).Value = Me.txtBoxSold.Value
ws.Cells(iRow, 10).Value = Me.txtPrice.Value
ws.Cells(iRow, 11).Value = Me.txtFrt.Value
ws.Cells(iRow, 13).Value = "0"
ws.Cells(iRow, 14).Value = "0"
ws.Cells(iRow, 12).Value = "Sale"

'select a produce item via button
If Me.optSnap.Value Then
ws.Cells(iRow, 6).Value = Me.optSnap.Caption
Else
ws.Cells(iRow, 6).Value = Me.optSno.Caption
End If

'clear data to enter new pallet
Me.txtPallet.Value = ""
Me.txtQty.Value = ""
Me.txtBoxSold.Value = ""
Me.txtPrice.Value = ""
Me.txtFrt.Value = ""
txtPallet.SetFocus
End Sub
Private Sub cmdNewInvoice_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.cboInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboCustomerID.Value
ws.Cells(iRow, 5).Value = "None"
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 9).Value = Me.txtBoxSold.Value
ws.Cells(iRow, 10).Value = Me.txtPrice.Value
ws.Cells(iRow, 11).Value = Me.txtFrt.Value
ws.Cells(iRow, 13).Value = "0"
ws.Cells(iRow, 14).Value = "0"
ws.Cells(iRow, 12).Value = "Sale"

'select a produce item via button
If Me.optSnap.Value Then
ws.Cells(iRow, 6).Value = Me.optSnap.Caption
Else
ws.Cells(iRow, 6).Value = Me.optSno.Caption
End If
'clear the data
Me.cboCustomerID = ""
Me.cboInvoice = ""
Me.txtPallet.Value = ""
Me.txtQty.Value = ""
Me.txtBoxSold.Value = ""
Me.txtPrice.Value = ""
Me.txtFrt.Value = ""
Me.cboInvoice.SetFocus
End Sub



Private Sub txtDate_Enter()
txtDate.Value = Format(Me.txtDate, "")
End Sub
Private Sub txtFrt_Enter()
txtFrt.Value = Format(Me.txtFrt.Value, "")
End Sub

Private Sub txtFrt_Exit(ByVal cancel As MSForms.ReturnBoolean)
txtFrt.Value = Format(Me.txtFrt.Value, "$#,###,###.00")
End Sub

Private Sub txtPrice_Enter()
'Places currency formatting on price
txtPrice.Value = Format(Me.txtPrice.Value, "")
End Sub
Private Sub txtPrice_Exit(ByVal cancel As MSForms.ReturnBoolean)
'as you exit then places currency formatting
txtPrice.Value = Format(Me.txtPrice.Value, "$#,###,###.00")
End Sub

Private Sub UserForm_Initialize()

Dim cItem As Range

With Me.cboCustomerID
*********For Each cItem In wksLookupLists.Range("CustomerIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboInvoice
For Each cItem In wksLookupLists.Range("InvoiceList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub

Can't seem to get Combo Box on User Form
 

Hi,
I am using this from another form I have and it works just fine. I didn't
build it so I am trying to use that code for this form and just make the
appropriate changes. I copied it over exactly. Could it be something I am
doing wrong in the properties portion of the form? When I look at the code in
the form that works I also don't see were the wslookuplist is defined. But it
works just fine. Maybe something I am doing wrong in naming my ranges? Just
guessing. Thank you for your help.

"Bob Phillips" wrote:

Just had a quick look at the code, and I cannot see anywhere where
wsLookupLists is loaded with a value, although this would not give that
error in my view.

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
Hey there,
I have a user form I have been working with but I just can't seem to
get my Combo Boxes to work. No matter what I do I get an error
Run-Time Error 1004
Method Range of Object Worksheet Failed

The following is what I have so far: I put ******* where I am gettting
the error. I would appreciate any ideas. Oh yeah and this is very first
userform so go easy on me. I need lots of hand holding:)

Option Explicit

Private Sub btnClose_Click()
Unload Me
End Sub

Private Sub cboInvoice_Change()
ShowValue cboInvoice
End Sub

Private Sub cboCustomerID_Change()
ShowValue cboCustomerID
End Sub

Private Sub ShowValue(cbo As ComboBox)
If cbo.ListIndex = -1 Then Exit Sub
lblShow.Caption = cbo.Value

End Sub

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.cboInvoice.Value) = "" Then
Me.cboInvoice.SetFocus
MsgBox "Please enter or choose 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.cboInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboCustomerID.Value
ws.Cells(iRow, 5).Value = "None"
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 9).Value = Me.txtBoxSold.Value
ws.Cells(iRow, 10).Value = Me.txtPrice.Value
ws.Cells(iRow, 11).Value = Me.txtFrt.Value
ws.Cells(iRow, 13).Value = "0"
ws.Cells(iRow, 14).Value = "0"
ws.Cells(iRow, 12).Value = "Sale"

'select a produce item via button
If Me.optSnap.Value Then
ws.Cells(iRow, 6).Value = Me.optSnap.Caption
Else
ws.Cells(iRow, 6).Value = Me.optSno.Caption
End If

'clear data to enter new pallet
Me.txtPallet.Value = ""
Me.txtQty.Value = ""
Me.txtBoxSold.Value = ""
Me.txtPrice.Value = ""
Me.txtFrt.Value = ""
txtPallet.SetFocus
End Sub
Private Sub cmdNewInvoice_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.cboInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboCustomerID.Value
ws.Cells(iRow, 5).Value = "None"
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 9).Value = Me.txtBoxSold.Value
ws.Cells(iRow, 10).Value = Me.txtPrice.Value
ws.Cells(iRow, 11).Value = Me.txtFrt.Value
ws.Cells(iRow, 13).Value = "0"
ws.Cells(iRow, 14).Value = "0"
ws.Cells(iRow, 12).Value = "Sale"

'select a produce item via button
If Me.optSnap.Value Then
ws.Cells(iRow, 6).Value = Me.optSnap.Caption
Else
ws.Cells(iRow, 6).Value = Me.optSno.Caption
End If
'clear the data
Me.cboCustomerID = ""
Me.cboInvoice = ""
Me.txtPallet.Value = ""
Me.txtQty.Value = ""
Me.txtBoxSold.Value = ""
Me.txtPrice.Value = ""
Me.txtFrt.Value = ""
Me.cboInvoice.SetFocus
End Sub



Private Sub txtDate_Enter()
txtDate.Value = Format(Me.txtDate, "")
End Sub
Private Sub txtFrt_Enter()
txtFrt.Value = Format(Me.txtFrt.Value, "")
End Sub

Private Sub txtFrt_Exit(ByVal cancel As MSForms.ReturnBoolean)
txtFrt.Value = Format(Me.txtFrt.Value, "$#,###,###.00")
End Sub

Private Sub txtPrice_Enter()
'Places currency formatting on price
txtPrice.Value = Format(Me.txtPrice.Value, "")
End Sub
Private Sub txtPrice_Exit(ByVal cancel As MSForms.ReturnBoolean)
'as you exit then places currency formatting
txtPrice.Value = Format(Me.txtPrice.Value, "$#,###,###.00")
End Sub

Private Sub UserForm_Initialize()

Dim cItem As Range

With Me.cboCustomerID
*********For Each cItem In wksLookupLists.Range("CustomerIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboInvoice
For Each cItem In wksLookupLists.Range("InvoiceList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub

Can't seem to get Combo Box on User Form
 

Well, maybe in the other form that variable gets loaded outside of the form
in a piece of code you are not now executing. Do a search in the VBE for it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jennifer" wrote in message
...
Hi,
I am using this from another form I have and it works just fine. I didn't
build it so I am trying to use that code for this form and just make the
appropriate changes. I copied it over exactly. Could it be something I am
doing wrong in the properties portion of the form? When I look at the code
in
the form that works I also don't see were the wslookuplist is defined. But
it
works just fine. Maybe something I am doing wrong in naming my ranges?
Just
guessing. Thank you for your help.

"Bob Phillips" wrote:

Just had a quick look at the code, and I cannot see anywhere where
wsLookupLists is loaded with a value, although this would not give that
error in my view.

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
Hey there,
I have a user form I have been working with but I just can't seem to
get my Combo Boxes to work. No matter what I do I get an error
Run-Time Error 1004
Method Range of Object Worksheet Failed

The following is what I have so far: I put ******* where I am gettting
the error. I would appreciate any ideas. Oh yeah and this is very
first
userform so go easy on me. I need lots of hand holding:)

Option Explicit

Private Sub btnClose_Click()
Unload Me
End Sub

Private Sub cboInvoice_Change()
ShowValue cboInvoice
End Sub

Private Sub cboCustomerID_Change()
ShowValue cboCustomerID
End Sub

Private Sub ShowValue(cbo As ComboBox)
If cbo.ListIndex = -1 Then Exit Sub
lblShow.Caption = cbo.Value

End Sub

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.cboInvoice.Value) = "" Then
Me.cboInvoice.SetFocus
MsgBox "Please enter or choose 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.cboInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboCustomerID.Value
ws.Cells(iRow, 5).Value = "None"
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 9).Value = Me.txtBoxSold.Value
ws.Cells(iRow, 10).Value = Me.txtPrice.Value
ws.Cells(iRow, 11).Value = Me.txtFrt.Value
ws.Cells(iRow, 13).Value = "0"
ws.Cells(iRow, 14).Value = "0"
ws.Cells(iRow, 12).Value = "Sale"

'select a produce item via button
If Me.optSnap.Value Then
ws.Cells(iRow, 6).Value = Me.optSnap.Caption
Else
ws.Cells(iRow, 6).Value = Me.optSno.Caption
End If

'clear data to enter new pallet
Me.txtPallet.Value = ""
Me.txtQty.Value = ""
Me.txtBoxSold.Value = ""
Me.txtPrice.Value = ""
Me.txtFrt.Value = ""
txtPallet.SetFocus
End Sub
Private Sub cmdNewInvoice_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.cboInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboCustomerID.Value
ws.Cells(iRow, 5).Value = "None"
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 9).Value = Me.txtBoxSold.Value
ws.Cells(iRow, 10).Value = Me.txtPrice.Value
ws.Cells(iRow, 11).Value = Me.txtFrt.Value
ws.Cells(iRow, 13).Value = "0"
ws.Cells(iRow, 14).Value = "0"
ws.Cells(iRow, 12).Value = "Sale"

'select a produce item via button
If Me.optSnap.Value Then
ws.Cells(iRow, 6).Value = Me.optSnap.Caption
Else
ws.Cells(iRow, 6).Value = Me.optSno.Caption
End If
'clear the data
Me.cboCustomerID = ""
Me.cboInvoice = ""
Me.txtPallet.Value = ""
Me.txtQty.Value = ""
Me.txtBoxSold.Value = ""
Me.txtPrice.Value = ""
Me.txtFrt.Value = ""
Me.cboInvoice.SetFocus
End Sub



Private Sub txtDate_Enter()
txtDate.Value = Format(Me.txtDate, "")
End Sub
Private Sub txtFrt_Enter()
txtFrt.Value = Format(Me.txtFrt.Value, "")
End Sub

Private Sub txtFrt_Exit(ByVal cancel As MSForms.ReturnBoolean)
txtFrt.Value = Format(Me.txtFrt.Value, "$#,###,###.00")
End Sub

Private Sub txtPrice_Enter()
'Places currency formatting on price
txtPrice.Value = Format(Me.txtPrice.Value, "")
End Sub
Private Sub txtPrice_Exit(ByVal cancel As MSForms.ReturnBoolean)
'as you exit then places currency formatting
txtPrice.Value = Format(Me.txtPrice.Value, "$#,###,###.00")
End Sub

Private Sub UserForm_Initialize()

Dim cItem As Range

With Me.cboCustomerID
*********For Each cItem In wksLookupLists.Range("CustomerIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboInvoice
For Each cItem In wksLookupLists.Range("InvoiceList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub

Can't seem to get Combo Box on User Form
 

Bob
When I look at the objects list there is a worksheet named
wksLookupLists(Lookups) What else am I looking for. Sorry but I need lots of
direction. I also looked in the macros window and don't see anything. Jennifer
"Bob Phillips" wrote:

Well, maybe in the other form that variable gets loaded outside of the form
in a piece of code you are not now executing. Do a search in the VBE for it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jennifer" wrote in message
...
Hi,
I am using this from another form I have and it works just fine. I didn't
build it so I am trying to use that code for this form and just make the
appropriate changes. I copied it over exactly. Could it be something I am
doing wrong in the properties portion of the form? When I look at the code
in
the form that works I also don't see were the wslookuplist is defined. But
it
works just fine. Maybe something I am doing wrong in naming my ranges?
Just
guessing. Thank you for your help.

"Bob Phillips" wrote:

Just had a quick look at the code, and I cannot see anywhere where
wsLookupLists is loaded with a value, although this would not give that
error in my view.

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
Hey there,
I have a user form I have been working with but I just can't seem to
get my Combo Boxes to work. No matter what I do I get an error
Run-Time Error 1004
Method Range of Object Worksheet Failed

The following is what I have so far: I put ******* where I am gettting
the error. I would appreciate any ideas. Oh yeah and this is very
first
userform so go easy on me. I need lots of hand holding:)

Option Explicit

Private Sub btnClose_Click()
Unload Me
End Sub

Private Sub cboInvoice_Change()
ShowValue cboInvoice
End Sub

Private Sub cboCustomerID_Change()
ShowValue cboCustomerID
End Sub

Private Sub ShowValue(cbo As ComboBox)
If cbo.ListIndex = -1 Then Exit Sub
lblShow.Caption = cbo.Value

End Sub

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.cboInvoice.Value) = "" Then
Me.cboInvoice.SetFocus
MsgBox "Please enter or choose 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.cboInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboCustomerID.Value
ws.Cells(iRow, 5).Value = "None"
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 9).Value = Me.txtBoxSold.Value
ws.Cells(iRow, 10).Value = Me.txtPrice.Value
ws.Cells(iRow, 11).Value = Me.txtFrt.Value
ws.Cells(iRow, 13).Value = "0"
ws.Cells(iRow, 14).Value = "0"
ws.Cells(iRow, 12).Value = "Sale"

'select a produce item via button
If Me.optSnap.Value Then
ws.Cells(iRow, 6).Value = Me.optSnap.Caption
Else
ws.Cells(iRow, 6).Value = Me.optSno.Caption
End If

'clear data to enter new pallet
Me.txtPallet.Value = ""
Me.txtQty.Value = ""
Me.txtBoxSold.Value = ""
Me.txtPrice.Value = ""
Me.txtFrt.Value = ""
txtPallet.SetFocus
End Sub
Private Sub cmdNewInvoice_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.cboInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboCustomerID.Value
ws.Cells(iRow, 5).Value = "None"
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 9).Value = Me.txtBoxSold.Value
ws.Cells(iRow, 10).Value = Me.txtPrice.Value
ws.Cells(iRow, 11).Value = Me.txtFrt.Value
ws.Cells(iRow, 13).Value = "0"
ws.Cells(iRow, 14).Value = "0"
ws.Cells(iRow, 12).Value = "Sale"

'select a produce item via button
If Me.optSnap.Value Then
ws.Cells(iRow, 6).Value = Me.optSnap.Caption
Else
ws.Cells(iRow, 6).Value = Me.optSno.Caption
End If
'clear the data
Me.cboCustomerID = ""
Me.cboInvoice = ""
Me.txtPallet.Value = ""
Me.txtQty.Value = ""
Me.txtBoxSold.Value = ""
Me.txtPrice.Value = ""
Me.txtFrt.Value = ""
Me.cboInvoice.SetFocus
End Sub



Private Sub txtDate_Enter()
txtDate.Value = Format(Me.txtDate, "")
End Sub
Private Sub txtFrt_Enter()
txtFrt.Value = Format(Me.txtFrt.Value, "")
End Sub

Private Sub txtFrt_Exit(ByVal cancel As MSForms.ReturnBoolean)
txtFrt.Value = Format(Me.txtFrt.Value, "$#,###,###.00")
End Sub

Private Sub txtPrice_Enter()
'Places currency formatting on price
txtPrice.Value = Format(Me.txtPrice.Value, "")
End Sub
Private Sub txtPrice_Exit(ByVal cancel As MSForms.ReturnBoolean)
'as you exit then places currency formatting
txtPrice.Value = Format(Me.txtPrice.Value, "$#,###,###.00")
End Sub

Private Sub UserForm_Initialize()

Dim cItem As Range

With Me.cboCustomerID
*********For Each cItem In wksLookupLists.Range("CustomerIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboInvoice
For Each cItem In wksLookupLists.Range("InvoiceList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub

Can't seem to get Combo Box on User Form
 

What I am suggesting is to use the Find function.

In the VB IDE, do a find (Ctrl-F) on the working project
Ensure that the Current Project button is selected
Type wsLookupLists in the editbox
Go look, until there are no more.

Hopefully you will discover where wsLookupLists is being used.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jennifer" wrote in message
...
Bob
When I look at the objects list there is a worksheet named
wksLookupLists(Lookups) What else am I looking for. Sorry but I need lots
of
direction. I also looked in the macros window and don't see anything.
Jennifer
"Bob Phillips" wrote:

Well, maybe in the other form that variable gets loaded outside of the
form
in a piece of code you are not now executing. Do a search in the VBE for
it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jennifer" wrote in message
...
Hi,
I am using this from another form I have and it works just fine. I
didn't
build it so I am trying to use that code for this form and just make
the
appropriate changes. I copied it over exactly. Could it be something I
am
doing wrong in the properties portion of the form? When I look at the
code
in
the form that works I also don't see were the wslookuplist is defined.
But
it
works just fine. Maybe something I am doing wrong in naming my ranges?
Just
guessing. Thank you for your help.

"Bob Phillips" wrote:

Just had a quick look at the code, and I cannot see anywhere where
wsLookupLists is loaded with a value, although this would not give
that
error in my view.

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
Hey there,
I have a user form I have been working with but I just can't seem
to
get my Combo Boxes to work. No matter what I do I get an error
Run-Time Error 1004
Method Range of Object Worksheet Failed

The following is what I have so far: I put ******* where I am
gettting
the error. I would appreciate any ideas. Oh yeah and this is very
first
userform so go easy on me. I need lots of hand holding:)

Option Explicit

Private Sub btnClose_Click()
Unload Me
End Sub

Private Sub cboInvoice_Change()
ShowValue cboInvoice
End Sub

Private Sub cboCustomerID_Change()
ShowValue cboCustomerID
End Sub

Private Sub ShowValue(cbo As ComboBox)
If cbo.ListIndex = -1 Then Exit Sub
lblShow.Caption = cbo.Value

End Sub

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.cboInvoice.Value) = "" Then
Me.cboInvoice.SetFocus
MsgBox "Please enter or choose 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.cboInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboCustomerID.Value
ws.Cells(iRow, 5).Value = "None"
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 9).Value = Me.txtBoxSold.Value
ws.Cells(iRow, 10).Value = Me.txtPrice.Value
ws.Cells(iRow, 11).Value = Me.txtFrt.Value
ws.Cells(iRow, 13).Value = "0"
ws.Cells(iRow, 14).Value = "0"
ws.Cells(iRow, 12).Value = "Sale"

'select a produce item via button
If Me.optSnap.Value Then
ws.Cells(iRow, 6).Value = Me.optSnap.Caption
Else
ws.Cells(iRow, 6).Value = Me.optSno.Caption
End If

'clear data to enter new pallet
Me.txtPallet.Value = ""
Me.txtQty.Value = ""
Me.txtBoxSold.Value = ""
Me.txtPrice.Value = ""
Me.txtFrt.Value = ""
txtPallet.SetFocus
End Sub
Private Sub cmdNewInvoice_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.cboInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboCustomerID.Value
ws.Cells(iRow, 5).Value = "None"
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 9).Value = Me.txtBoxSold.Value
ws.Cells(iRow, 10).Value = Me.txtPrice.Value
ws.Cells(iRow, 11).Value = Me.txtFrt.Value
ws.Cells(iRow, 13).Value = "0"
ws.Cells(iRow, 14).Value = "0"
ws.Cells(iRow, 12).Value = "Sale"

'select a produce item via button
If Me.optSnap.Value Then
ws.Cells(iRow, 6).Value = Me.optSnap.Caption
Else
ws.Cells(iRow, 6).Value = Me.optSno.Caption
End If
'clear the data
Me.cboCustomerID = ""
Me.cboInvoice = ""
Me.txtPallet.Value = ""
Me.txtQty.Value = ""
Me.txtBoxSold.Value = ""
Me.txtPrice.Value = ""
Me.txtFrt.Value = ""
Me.cboInvoice.SetFocus
End Sub



Private Sub txtDate_Enter()
txtDate.Value = Format(Me.txtDate, "")
End Sub
Private Sub txtFrt_Enter()
txtFrt.Value = Format(Me.txtFrt.Value, "")
End Sub

Private Sub txtFrt_Exit(ByVal cancel As MSForms.ReturnBoolean)
txtFrt.Value = Format(Me.txtFrt.Value, "$#,###,###.00")
End Sub

Private Sub txtPrice_Enter()
'Places currency formatting on price
txtPrice.Value = Format(Me.txtPrice.Value, "")
End Sub
Private Sub txtPrice_Exit(ByVal cancel As MSForms.ReturnBoolean)
'as you exit then places currency formatting
txtPrice.Value = Format(Me.txtPrice.Value, "$#,###,###.00")
End Sub

Private Sub UserForm_Initialize()

Dim cItem As Range

With Me.cboCustomerID
*********For Each cItem In wksLookupLists.Range("CustomerIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboInvoice
For Each cItem In wksLookupLists.Range("InvoiceList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub