Results 1 to 11 of 11

Error "ListIndex = -1"

Threaded 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

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