Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Can't seem to get Combo Box on User Form
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 385
Default 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




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 385
Default 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







Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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









Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding items to a combo box on a user form Gazz_85[_2_] Excel Discussion (Misc queries) 1 July 9th 09 05:00 PM
linking a form combo box... results from the combo box to another Trey Excel Discussion (Misc queries) 1 July 15th 07 01:58 AM
Currency format for combo box in a user form Jennifer Excel Programming 4 April 16th 05 06:16 AM
Patrick -- 424 Combo Box User Form Jennifer Excel Programming 2 April 8th 05 07:31 AM
Run Time Error 424 Combo Box User Form Jennifer Excel Programming 3 April 7th 05 08:23 PM


All times are GMT +1. The time now is 04:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement