Announcement

Collapse
No announcement yet.

Userform ComboBox Run-time Error '381'

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Userform ComboBox Run-time Error '381'



    Hello Everyone - I am getting an error when I enter incorrect data or entry data that isn't in the list into a combobox in one of my userform's "Run-time error '381': Could not get the List property. Invalid property array index". I have posted the code below. This ComboBox list is where all the UPC Codes are kept for my Liquor inventory list. I don't need this userform to crash everytime someone enters or scans in an incorrect barcode number or one that is not in the inventory UPC Barcode list. Any Help you can give would be greatly welcome!!

    Mike


    Code:
    Private Sub UserForm_Initialize()
        UpcBarCodeBox.List = Worksheets("Liquor & Wine Inventory").Range("A6").CurrentRegion.Value
        ComboBox2.List = Array(0, 1, 2, 3, 4)
    End Sub
    
    Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        If TextBox3 = vbNullString Then Exit Sub
                If IsNumeric(Me.TextBox3.Value) Then
            Me.TextBox3.Value = Format(Me.TextBox3.Value, "Currency")
        End If
    End Sub
    
    Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        With Me
            If IsEmpty(.TextBox4) Then Exit Sub
            .TextBox6.Value = (Val(.TextBox4.Value) + Val(.TextBox5.Value)) * 1
        End With
    End Sub
    
    Private Sub UpcBarCodeBox_Change()
        LiquorName.Value = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 1)
        TextBox1.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 2)
        TextBox2.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 4)
        TextBox3.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 5)
        TextBox4.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 9)
        ComboBox2.Value = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 11)
        TextBox7.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 12)
        TextBox8.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 13)
        TextBox9.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 14)
        TextBox10.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 15)
        TextBox11.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 17)
        TextBox12.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 18)
    End Sub
    
    Private Sub UpdateButton_Click()
            ActiveSheet.Range("AA3").Value = LiquorName.Text
            ActiveSheet.Range("AB3").Value = TextBox6.Text
            ActiveSheet.Range("AC3").Value = ComboBox2.Value
            ActiveSheet.Range("AD3").Value = TextBox7.Value
            ActiveSheet.Range("AE3").Value = TextBox8.Text
            ActiveSheet.Range("AF3").Value = TextBox9.Text
            ActiveSheet.Range("AG3").Value = TextBox10.Text
            ActiveSheet.Range("AH3").Value = TextBox11.Text
            ActiveSheet.Range("AI3").Value = TextBox12.Text
            
    Dim vVal
    Dim rRange As Range
    vVal = Range("AA3")
    Set rRange = Range("B5", Range("B65536").End(xlUp))
     
        If WorksheetFunction.CountIf(rRange, vVal) = 0 Then
            MsgBox "Cannot find " & Range("AA3")
        Else
            ' Bottles Purchased
            rRange.Find(What:=vVal, after:=Range("B5"), LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, 8) = Range("AB3").Value
            ' Number Of Open Bottles
            rRange.Find(What:=vVal, after:=Range("B5"), LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, 10) = Range("AC3").Value
            ' Open Bottle 1
            rRange.Find(What:=vVal, after:=Range("B5"), LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, 11) = Range("AD3").Value
            ' Open Bottle 2
            rRange.Find(What:=vVal, after:=Range("B5"), LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, 12) = Range("AE3").Value
            ' Open Bottle 3
            rRange.Find(What:=vVal, after:=Range("B5"), LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, 13) = Range("AF3").Value
            ' Open Bottle 4
            rRange.Find(What:=vVal, after:=Range("B5"), LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, 14) = Range("AG3").Value
            ' BackUp Bottles
            rRange.Find(What:=vVal, after:=Range("B5"), LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, 16) = Range("AH3").Value
            ' LiquorRoom Bottles
            rRange.Find(What:=vVal, after:=Range("B5"), LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, 17) = Range("AI3").Value
            ' Clear Pasted Cells
            ActiveSheet.Activate
            Range("AA3:AI3").Select
            Selection.Clear
            Range("B5").Select
            ' Clear UserForm
            LiquorInventory.LiquorName.Value = ""
            LiquorInventory.TextBox1.Value = ""
            LiquorInventory.TextBox2.Value = ""
            LiquorInventory.TextBox3.Value = ""
            LiquorInventory.TextBox4.Value = ""
            LiquorInventory.TextBox5.Value = ""
            LiquorInventory.TextBox6.Value = ""
            LiquorInventory.ComboBox2.Value = ""
            LiquorInventory.TextBox7.Value = ""
            LiquorInventory.TextBox8.Value = ""
            LiquorInventory.TextBox9.Value = ""
            LiquorInventory.TextBox10.Value = ""
            LiquorInventory.TextBox11.Value = ""
            LiquorInventory.TextBox12.Value = ""
            UpcBarCodeBox.SetFocus
        End If
            UpcBarCodeBox.SetFocus
    End Sub
    Private Sub ClearButton_Click()
        LiquorInventory.LiquorName.Value = ""
        LiquorInventory.TextBox1.Value = ""
        LiquorInventory.TextBox2.Value = ""
        LiquorInventory.TextBox3.Value = ""
        LiquorInventory.TextBox4.Value = ""
        LiquorInventory.TextBox5.Value = ""
        LiquorInventory.TextBox6.Value = ""
        LiquorInventory.ComboBox2.Value = ""
        LiquorInventory.TextBox7.Value = ""
        LiquorInventory.TextBox8.Value = ""
        LiquorInventory.TextBox9.Value = ""
        LiquorInventory.TextBox10.Value = ""
        LiquorInventory.TextBox11.Value = ""
        LiquorInventory.TextBox12.Value = ""
    End Sub
    
    Private Sub CancelButton_Click()
        Unload Me
    End Sub

  • #2
    Re: Userform ComboBox Run-time Error '381'

    Check the ComboBox's properties, probably MatchEntry is set to true
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: Userform ComboBox Run-time Error '381'

      Good Morning/Afternoon Roy..... No... The properties are "MatchRequired = False" and "MatchEntry = 1 - fmMatchEntryComplete" . I have tried all three choices in Match Entry and got the same results. Is there a way to set the Combobox CurrentRegion.Value to loop thru everything in the list before saying what ever UPC or entry was not found??? Now keep in mind that UpcBarCodeBox is NOT a sorted list. If you remember, the entire worksheet is sorted on Column "B" (Liquor Name). Thank You again for all your help!!

      Mike

      Comment


      • #4
        Re: Userform ComboBox Run-time Error '381'

        Can you provide the latest version that is producing the error, I've deleted the previous one.
        Hope that Helps

        Roy

        New users should read the Forum Rules before posting

        For free Excel tools & articles visit my web site

        RoyUK's Web Site

        royUK's Database Form

        Where to paste code from the Forum

        About me.

        Comment


        • #5
          Re: Userform ComboBox Run-time Error '381'

          You probably used 'rowsource'. empty that property.

          And please tidy up your code:

          Code:
          Private Sub UpcBarCodeBox_Change() 
          for j=1 to 12
          if j<5 or j>6 then me("TextBox" & j).text=UpcBarCodeBox.List(UpcBarCodeBox.ListIndex,choose(j,2,4,5,9,,,13,14,15,17,18))
          next
          End Sub
          Code:
          Private Sub UpdateButton_Click() 
              ActiveSheet.Range("AA3:AI3") =array(LiquorName.Text,TextBox6.Text,ComboBox2.Value,TextBox7.Value,TextBox8.Text,TextBox9.Text,TextBox10.Text,TextBox11.Text,TextBox12.Text)
          end sub

          Comment


          • #6
            Re: Userform ComboBox Run-time Error '381'

            snb - Rowsource is clear. And Thank You for the help on cleaning up my code. Since I am still learning VBA things like that don't occur to me. I just changed the code and tried it and it came up with an error. Run-time error '-2147024809 (80070057)': Could not get the List Property. Invalid argument.

            Mike

            Comment


            • #7


              Re: Userform ComboBox Run-time Error '381'

              You would be better to use the Exit event of the ComboBox, because ever change would currently try to run the eisting code. Also, you need to check if the entry exists in the list

              Code:
              Private Sub UpcBarCodeBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
              If Application.WorksheetFunction.Count(ActiveSheet.Range("A6").CurrentRegion.Columns(1), ActiveControl.Value) = 0 Then Exit Sub
                  LiquorName.Value = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 1)
                  TextBox1.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 2)
                  TextBox2.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 4)
                  TextBox3.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 5)
                  TextBox4.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 9)
                  ComboBox2.Value = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 11)
                  TextBox7.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 12)
                  TextBox8.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 13)
                  TextBox9.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 14)
                  TextBox10.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 15)
                  TextBox11.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 17)
                  TextBox12.Text = UpcBarCodeBox.List(UpcBarCodeBox.ListIndex, 18)
              End Sub
              Hope that Helps

              Roy

              New users should read the Forum Rules before posting

              For free Excel tools & articles visit my web site

              RoyUK's Web Site

              royUK's Database Form

              Where to paste code from the Forum

              About me.

              Comment

              Working...
              X