Prev: Strange probem with capitalization of object
Next: Set Chart Legend in VBA
From: GEdwards on 1 Apr 2010 12:26
Thanks Keith, works great.

I tested the MsgBox and there is a restriction of 22 lines of text allowed.
I guess I now need to find a scrollable MsgBox, and I would still like the
idea of having a ListBox I could pick from.


"ker_01" wrote:

> I believe that your listbox would have to be on a userform, or you would have
> to use a sheet form control or data validation list (it really all depends on
> how this will be used, and I don't have enough info to suggest which might be
> most appropriate).
>
> If you just need to view the list but not select one, then msgbox would work
> (although it may have a max string length, I've never checked). To get them
> listed on individual lines, I use chr(13) out of habit, but I suspect you
> could also use vbcrlf
>
> showThis = showThis & chr(13) & .FoundFiles(i)
>
> HTH,
> Keith
>
> "GEdwards" wrote:
>
> > I have modified the code below from a reply to a question in 2007 from
> > Patrick Kirk. I am using MS Office Excel 2003.
> >
> > For my use I would prefer the results within 2 possible scenarios;
> >
> > 1) a ListBox so that I may choose just see or select a file that was found
> > and proceed to work with it, such as open an XLS, if the file is an XLS file.
> > 2) a MsgBox, but each file found must be displayed on separate lines within
> > the MsgBox
> >
> > The difference for me too is that this IS NOT for a user form but rather a
> > macro that can be assigned to a button.
> >
> > Suggestions?
> >
> >
> > Sub findFile()
> > Dim showThis As String
> > Dim i As Integer
> >
> > Set Fs = Application.FileSearch
> > With Fs
> > 'Change below to give the name of the Directory you want to search
> > .LookIn = "C:\Fun Stuff\Excel\"
> > .SearchSubFolders = True
> > .Filename = "msgbox"
> > If .Execute() > 0 Then
> > For i = 1 To .FoundFiles.Count
> > showThis = showThis & " " & .FoundFiles(i)
> > 'ListBox1.AddItem (.FoundFiles(i))
> > Next i
> > MsgBox .FoundFiles.Count & showThis
> > Else
> > MsgBox "No files found."
> > End If
> > End With
> > End Sub
 | 
Pages: 1
Prev: Strange probem with capitalization of object
Next: Set Chart Legend in VBA