troubleshooting Question

Excel VBA: How to add items to a multi-select listbox with two columns instead of one?

Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America asked on
VBAMicrosoft ExcelMicrosoft Office
1 Comment1 Solution79 ViewsLast Modified:
Excel VBA form:  How to add items to a multi-select listbox with two columns instead of one?

Please note the following code:
I get the yellow highlight on line item with Run-time error '13'.  type mismatch.
please note image attachments that show this as well.

All I'm doing is looping through the collection and adding to the two columns - the fullfilename found in variable sFile and the filename only in sFileOnly...

Me.ListBox_FilesFound(lCounter - 1, 0) = sFile

Private Sub FillListBox_FilesFound()
On Error GoTo Err_Proc

  Dim sFileName As String
  Dim sFilePath As String
  Dim lCounterFileTotal As Long
  Dim lCounter As Long
  Dim sFileOnly As String
  Dim colFiles As New Collection
  Dim vFile As Variant
  Dim sFile As String
  
  'setting up listbox:
  Me.ListBox_FilesFound.ColumnCount = 2
  Me.ListBox_FilesFound.ColumnWidths = "0;100"
  Me.ListBox_FilesFound.Clear
  
  sFileName = GetCurrentFileName

  'determine path
  sFilePath = FilePathOnly_feo(sFileName)
  txtFilePath = sFilePath
  
  RecursiveDir colFiles, sFilePath, "*.xls*", True
  
  lCounterFileTotal = colFiles.Count
  lCounter = 0
  'loops through all files found in RecursiveDir
  'but only through filenames that have states.
  For Each vFile In colFiles
  
    sFile = vFile
    lCounter = lCounter + 1
    sFileOnly = FileNameOnly_feo(sFile)
    
    Me.ListBox_FilesFound.AddItem
    Me.ListBox_FilesFound(lCounter - 1, 0) = sFile
    Me.ListBox_FilesFound(lCounter - 1, 0) = sFileOnly
    
    If IsNameOfUSStates(sFileOnly) = True Then
      ListBox_FilesFound(lCounter - 1, 0).Selected = True
      DoEvents
    End If
    
  Next vFile
  'lCounter = 0
  
Exit_Proc:
  Exit Sub
  
Err_Proc:
  Call LogError_feo(Err, Err.Description, "fAdjustFiles @ FillListBox_FilesFound")
  Resume Exit_Proc
End Sub
ee-error1.PNG
ee-error2.PNG
ASKER CERTIFIED SOLUTION
stephenlecomptejr

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros