Can't seem to get Combo Box on User Form

2

2klever

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
 
B

Bob Phillips

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)


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
Click to expand...
 
G

Guest

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 said:
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)


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
Click to expand...
Click to expand...
 
B

Bob Phillips

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 said:
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 said:
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)


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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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)


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
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

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 said:
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 said:
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 said:
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
Click to expand...
code
in
the form that works I also don't see were the wslookuplist is defined.
Click to expand...
But
it
works just fine. Maybe something I am doing wrong in naming my ranges? Just
guessing. Thank you for your help.

:

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)


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Runtime Error 1004:Method 'Range' of object'_Worksheet' Failed 4
Excel VBA on server 0
Create a yes no message on a command button on click 0
User Form's 1
User form with VBA 10
How to insert row after data from user form 1
Userform VBA Complie Error: Method or data memeber not found 3
Help finding first empty cell in column 1

Top