Wrong number of arguments

[Guest]

The following runs like a champ until i try to increase the ranges. In the
sub how do I increase the data input. I also think I have to add something to
the source range. But i'm just not sure how. Thanks guys!
You will notice i have added in sub 1stData ***txtProdID.Text =
..List(.ListIndex, 2)***I guess what i am asking is when i click update how do
i get it to add that as well, if changes have been made. Oooodles and oodles
of thanks.

Option Explicit
Private source As Range
Private index As Long
Private market As String
****************************
Private Sub btnUpdate_Click()
Dim pointer As String
If txtDataID = "" Then Exit Sub
If Trim(txtBoxes.Text) = "" Then Exit Sub
If Not IsNumeric(txtBoxes.Text) Then Exit Sub
pointer = lstData.ListIndex
For index = 2 To source.Rows.Count
If source.Cells(index, 1) = txtDataID.Text Then
source.Cells(index, 4) = Trim(txtBoxes.Text)
Exit For
End If
Next
LoadData
lstData.ListIndex = pointer
End Sub
***************************
Private Sub lstData_Click()
With lstData
txtDataID.Text = .List(.ListIndex, 0)
txtProdID.Text = .List(.ListIndex, 2)
txtBoxes.Text = .List(.ListIndex, 3)
End With
End Sub
*****************************
Private Sub UserForm_Initialize()
With Worksheets("Database")
Set source = .Range(.Range("A1"), .Range("D1").End(xlDown))
End With
LoadMarkets
End Sub
*********************************
Private Sub LoadMarkets()
Dim markets As New Scripting.Dictionary
For index = 2 To source.Rows.Count
market = source.Cells(index, "k").Value
If Not markets.Exists(market) Then
markets.Add market, market
cmbMarket.AddItem market
End If
Next
End Sub

 

[Tushar Mehta]

You are setting a global variable for some reason and nothing ties it
to the lstData listbox (combobox?). What you need to do in the
initialize routine is something like in my test:

Me.lstData.RowSource = ActiveSheet.Range("a1:e4").Address

Of course, in your case you should use
..Range(.Range("A1"), .Range("D1").End(xlDown)) instead of the specific
range I used.

Then, the following will work just fine

Private Sub lstData_Change()
With Me.lstData
MsgBox .ListIndex & vbNewLine _
& .List(.ListIndex, 3)
End With
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

Yes it should be tied to a combo box. Sorry if i did not include that. This
is the whole string: Not sure how to get the uptdate button to work.
*************
Option Explicit
Private source As Range
Private index As Long
Private market As String
****************
Private Sub btnUpdate_Click()
Dim pointer As String
If txtDataID = "" Then Exit Sub
If Trim(txtBoxes.Text) = "" Then Exit Sub
If Not IsNumeric(txtBoxes.Text) Then Exit Sub
pointer = lstData.ListIndex
For index = 2 To source.Rows.Count
If source.Cells(index, 1) = txtDataID.Text Then
source.Cells(index, 4) = Trim(txtBoxes.Text)
Exit For
End If
Next
LoadData
lstData.ListIndex = pointer
End Sub
******************
Private Sub cboClose_Click()
Unload Me
End Sub
****************** 'combo box
Private Sub cmbMarket_Change()
LoadData
End Sub
*****************
Private Sub lstData_Click()
With lstData
txtDataID.Text = .List(.ListIndex, 0)
txtProdID.Text = .List(.ListIndex, 3)
txtDate.Text = .List(.ListIndex, 2)
txtBoxes.Text = .List(.ListIndex, 4)
txtBoxTrans.Text = .List(.ListIndex, 5)
txtRtn.Text = .List(.ListIndex, 6)
txtWaste.Text = .List(.ListIndex, 7)
txtWt.Text = .List(.ListIndex, 8)
txtPrice.Text = .List(.ListIndex, 9)
End With
End Sub
******************
Private Sub UserForm_Initialize()
With Worksheets("Database")
Set source = .Range(.Range("A1"), .Range("D1").End(xlDown))
End With
LoadMarkets
End Sub
Private Sub LoadMarkets()
Dim markets As New Scripting.Dictionary
For index = 2 To source.Rows.Count
market = source.Cells(index, "k").Value
If Not markets.Exists(market) Then
markets.Add market, market
cmbMarket.AddItem market
End If
Next
End Sub
***************
Private Sub LoadData()
txtDataID = ""
txtBoxes = ""
With lstData
.Clear
market = cmbMarket.Value
For index = 2 To source.Rows.Count
If market = source.Cells(index, 11) Then
.AddItem source.Cells(index, 1) ' ID
.List(.ListCount - 1, 2) = source.Cells(index, 2) 'Date
.List(.ListCount - 1, 3) = source.Cells(index, 3) ' Fruit
.List(.ListCount - 1, 4) = source.Cells(index, 5) 'Boxes
.List(.ListCount - 1, 5) = source.Cells(index, 6) 'Boxes
.List(.ListCount - 1, 6) = source.Cells(index, 7) 'Boxes
.List(.ListCount - 1, 7) = source.Cells(index, 8) 'Boxes
.List(.ListCount - 1, 8) = source.Cells(index, 9) 'Boxes
.List(.ListCount - 1, 9) = source.Cells(index, 10) 'Boxes
End If
Next
End With
End Sub