+ Reply to Thread
Results 1 to 11 of 11

Error "ListIndex = -1"

Hybrid View

  1. #1
    Registered User
    Join Date
    07-01-2015
    Location
    Antwerp
    MS-Off Ver
    2019
    Posts
    72

    Error "ListIndex = -1"

    Hi, I have a userform with a combobox and listboxes, coded as shown below. See Excel file in attachment.
    The userform is linked to a named table in worksheet "Data". The code returns an error when the named table is empty, ie. when the ListIndex = -1, ie. when the DatabodyRange is 0.
    I cannot seem to figure out a way to make this code errorproof when the table does not contain any data/listrows. Do any of you know?

    Option Explicit
    Dim LObj As ListObject, inProcess As Boolean
    
    Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii = 44 Then KeyAscii = 46
    End Sub
    
    Private Sub ComboBox1_Change()
    Dim a, b
    
    If inProcess Then Exit Sub
    If ComboBox1.ListIndex > -1 Then Exit Sub
    
    a = Application.Transpose(LObj.DataBodyRange.Value)
    b = Filter(a, ComboBox1, True, vbTextCompare)
    ComboBox1.Clear: CBExit.SetFocus: ComboBox1.SetFocus
    If UBound(b) > -1 Then
      ComboBox1.List = b
      ComboBox1.DropDown
    End If
    End Sub
    
    Private Sub Add(lb As MSForms.ListBox)
    If ComboBox1 = "" Then Exit Sub
    On Error Resume Next
      lb = ComboBox1: If lb.ListIndex = -1 Then lb.AddItem ComboBox1
    On Error GoTo 0
    lb.ListIndex = -1
    
    If ComboBox1.ListIndex = -1 Then
      LObj.ListRows.Add.Range = ComboBox1
      LObj.Range.Sort LObj.Range(1), xlAscending, Header:=xlYes
      ComboBox1.AddItem ComboBox1
    End If
    End Sub
    
    Private Sub Remove(lb)
    Dim i&
    i = lb.ListIndex
    If i > -1 Then
      lb.ListIndex = -1
      lb.RemoveItem i
    End If
    End Sub
    
    Private Sub CBAddSender_Click(): Add ListBox1: End Sub
    Private Sub CBAddCC_Click(): Add ListBox2: End Sub
    Private Sub CBAddRecipient_Click(): Add ListBox3: End Sub
    
    Private Sub CBRemoveSender_Click(): Remove ListBox1: End Sub
    Private Sub CBRemoveCC_Click(): Remove ListBox2: End Sub
    Private Sub CBRemoveRecipient_Click(): Remove ListBox3: End Sub
    
    Private Sub CommandButton1_Click()
    Dim i&, sTxt$
    i = ComboBox1.ListIndex: If i = -1 Then Exit Sub
    inProcess = True
      sTxt = ComboBox1: ComboBox1.ListIndex = -1: ComboBox1.RemoveItem i
    inProcess = False
    i = Application.Match(sTxt, LObj.DataBodyRange, 0)
    LObj.ListRows(i).Delete
    End Sub
    
    Private Sub UserForm_Initialize()
    Set LObj = Range("Tabla1").ListObject
    ComboBox1.List = LObj.DataBodyRange.Value
    End Sub
    
    Private Sub CBExit_Click()
    Unload Me
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    09-03-2016
    Location
    Catania, Italy
    MS-Off Ver
    Excel 2019
    Posts
    182

    Re: Error "ListIndex = -1"

    Hello
    Try this
    Private Sub UserForm_Initialize()
    If Sheets("Data").Cells(Rows.Count, 2).End(xlUp).Row <= 3 Then Exit Sub
    Set LObj = Range("Tabla1").ListObject
    ComboBox1.List = LObj.DataBodyRange.Value
    End Sub
    Bye bye,
    Marius

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: Error "ListIndex = -1"

    use ListRows property to get number of rows.

    Private Sub UserForm_Initialize()
    Set LObj = Range("Tabla1").ListObject
    If LObj.ListRows.Count > 0 Then
        If LObj.DataBodyRange.Rows.Count > 1 Then
           ComboBox1.List = LObj.DataBodyRange.Value
        Else
            ComboBox1.AddItem LObj.DataBodyRange.Value
        End If
    Else
        ComboBox1.Clear
    End If
    End Sub
    Cheers
    Andy
    www.andypope.info

  4. #4
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

    Re: Error "ListIndex = -1"

    .List expects an array

    Private Sub UserForm_Initialize()
    Set LObj = Hoja1.ListObjects("Tabla1")
    If LObj.ListRows.Count < 1 Then
    MsgBox "No data in table", vbInformation
    Exit Sub
    End If
    With Me.ComboBox1
    If LObj.ListRows.Count = 1 Then
    .AddItem LObj.Range(2, 1)
    Else
    ary1 = LObj.DataBodyRange
    .List = ary1
    End If
    End With
    End Sub
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.

  5. #5
    Registered User
    Join Date
    07-01-2015
    Location
    Antwerp
    MS-Off Ver
    2019
    Posts
    72

    Re: Error "ListIndex = -1"

    Thanks for your answers guys but all of the above suggestions return Runtime errors 9 or 91 ((LObj.DataBodyRange.Value) = <Object variable or With block variable not set>) when typing something in the combobox.
    Any idea why?

  6. #6
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

    Re: Error "ListIndex = -1"

    Get rid of all the spurious code in your ComboBox1_Change event.

  7. #7
    Registered User
    Join Date
    07-01-2015
    Location
    Antwerp
    MS-Off Ver
    2019
    Posts
    72

    Re: Error "ListIndex = -1"

    Hi torachan, could you specify which code is spurious? I deleted the entire Combobox1_Change event and this returns Runtime error 9 - Subscript out of range ("vbTextCompare = 1"). My VBA knowledge is limited.

  8. #8
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

    Re: Error "ListIndex = -1"

    As you are just moving items from sheet to combobox then to listbox their location is always known, there is no need for search routines or "vbTextCompare" just use the ordinates.
    attached is what I think you are trying to achieve.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-01-2015
    Location
    Antwerp
    MS-Off Ver
    2019
    Posts
    72

    Re: Error "ListIndex = -1"

    Thanks for the reply Torachan but your code unfortunately removed 2 crucial features of the userform: being able to search in the combobox by typing the first letters (autocomplete search); and being able to type a new entry in the combobox which is then also added into the named table.

    The user only uses the userform. The data sheet is hidden and not meant for manual editing.

  10. #10
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

    Re: Error "ListIndex = -1"

    give this a try
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-01-2015
    Location
    Antwerp
    MS-Off Ver
    2019
    Posts
    72

    Re: Error "ListIndex = -1"

    Thanks Torachan!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Run-time error "Method "Add" of object "Attachments" failed" - HELP
    By Jyggalag in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-20-2022, 10:03 AM
  2. [SOLVED] Multiple UserForms: ComboBox "Remembers" Previously Selected ListIndex Value
    By Xtab in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-26-2018, 04:57 PM
  3. Replies: 35
    Last Post: 01-13-2016, 02:16 AM
  4. = EMBED("Forms.ComboBox.1","") error "Reference not valid."
    By CatharinaCatharina in forum Excel General
    Replies: 2
    Last Post: 12-11-2014, 09:58 AM
  5. [SOLVED] If Not ActiveSheet.Range("A1").Value Like "apple" Then MsgBox "Error"
    By HerryMarkowitz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-18-2014, 02:16 PM
  6. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM
  7. What is Error "Method "Paste" of object "_Worksheet" failed?
    By vat in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-17-2006, 04:10 PM

Tags for this Thread

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