+ Reply to Thread
Results 1 to 3 of 3

Wrong number of arguments

  1. #1
    Jennifer
    Guest

    Wrong number of arguments

    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

    --
    Though daily learning, I LOVE EXCEL!
    Jennifer

  2. #2
    Tushar Mehta
    Guest

    Re: Wrong number of arguments

    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

    In article <[email protected]>,
    [email protected] says...
    > 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
    >
    >


  3. #3
    Jennifer
    Guest

    Re: Wrong number of arguments

    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


    --
    Though daily learning, I LOVE EXCEL!
    Jennifer


    "Tushar Mehta" wrote:

    > 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
    >
    > In article <[email protected]>,
    > [email protected] says...
    > > 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
    > >
    > >

    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1