Solved: Dynamic Listbox and Hyperlinking the information inside of it.

[Treygor]

I have dynamically created some listboxes with the following code and I want to know if it is possible to hyperlink the information that is displayed in the Listbox. I run into the problem where I do not know how to use, for example, the 3rd listbox that was dynamically created. When these items are drag and dropped into the userform, I understand that each listbox is an object but how do you refer to a dynamically created object?

I would like to allow the user to simply click on one particular listindex within any given listbox and then have the listindex open a file. Sorry if the code is confusing. Thanks in advance

-Treygor




[vba]Sub Set_Page_Topics()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''
'This code will find the number of files in a certain folder and all of its sub folders.
'File_Name(III) will hold the names of all of those files.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''
Set FS = Application.FileSearch
Dim File_Name(20) As String
With FS
.LookIn = "C:\Documents and Settings\"
.SearchSubFolders = True
.FileName = "*"
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
Number_Of_Files = .FoundFiles.Count
For III = 1 To Number_Of_Files
File_Name(III) = .FoundFiles(III)
Next III
Else
MsgBox "There were no files found."
End If
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''


Dim i, II As Integer
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'allows me to name the dynamically created multipage and listbox
Dim NewMPage As MSForms.MultiPage
Dim NewLbox As MSForms.ListBox
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
i = 1
''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Initialize the multipage to have zero pages to start.
UserForm1.MultiPage1.Pages.Clear
''''''''''''''''''''''''''''''''''''''''''''''''''''''

'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Adds the topics to the multipage.
'Topics must be placed on sheet 1, in consecutive cells
Do While Sheet1.Cells(1, i) > ""
UserForm1.MultiPage1.Pages.Add Sheet1.Cells(1, i).Text
II = 2
With UserForm1.MultiPage1.Pages(i - 1)
Set NewMPage = .Controls.Add("Forms.Multipage.1") 'adds dynamic mulitpage to every page in multipage1
NewMPage.Pages.Clear
NewMPage.Height = 250
NewMPage.Width = 700
Do While Sheet1.Cells(II, i) > "" 'Names each dynamically created multipage sheet
With NewMPage.Pages.Add
.Caption = Sheet1.Cells(II, i)
Set NewLbox = .Controls.Add("Forms.LIstbox.1") 'adds dynamic listbox to every nested multipage
NewLbox.Height = 200
NewLbox.Width = 600
NewLbox.Clear

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''
'The following code will place all of the file names paths that are contained with in a certain folder.
For III = 0 To Number_Of_Files
NewLbox.AddItem File_Name(III) '??? How do I enable each new item added to the list box to be hyperlinked to a file?
Next III
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''
End With
II = II + 1 'Counter
Loop
End With
i = i + 1 'Counter
Loop
End Sub[/vba]

[Ken Puls]

How do I enable each new item added to the list box to be hyperlinked to a file?

You don't. You need to add the files to the listbox, then you monitor the Listbox_Click event to figure out the file name and launch that via code. (You can't add a hyperlink directly to a listbox's list.)

Now, question for you... these listboxes are all dynamically created, as is the quantity of them, correct? So you won't necessarily know how many listboxes you are starting with?

Will all listboxes contain file names and the full files paths?

I'm thinking that you may want to set up a class module to monitor all listbox click events to open your files...

[Norie]

Treygor

I think Ken's got the right idea - you need a class module for this.

Every time you create a listbox you add it to the class. (Don't know if that's the correct terminology.)

You might however encounter problems since you appear to be using multipages within multipages.

What is it you are actually trying to achieve?

Perhaps there's another approach?

[Treygor]

Thanks for the help so far. I am kind of new to VBA, if you couldn't tell.

What I am trying to acheive:
I would like a quick sorting method of different folders that is easy to use for non-excel users. For example, I have a quality manual that I will be writing. Within this manual, there are 5-6 main topics and that is what the first multipage will list. Now within each main topic, there may be 5-6 sub-categories and that is what the 2nd multipage will list. The list box that is on each sub-category multipage will be the link to an actual file in a designated folder. These files will contain, go, no-go and minimal go information.

I have everything working except for the link to the actual files and that is where I am running into trouble. I am unfamiliar with class modules. Again, thanks for all the help. I appreciate your patience and understanding when dealing with noobies.

-Treygor

[Ken Puls]

Okay, hopefully you can bear with me on this explanation...

First, create a new Class Module in your project. (This is going to be the code that triggers when the listbox is clicked.) Rename the class module to clsListBox. Open it up, and put the following code in there:

[vba]Public WithEvents LB As MSForms.ListBox

Private Sub LB_click()
'You'll need to update this with something useful
MsgBox LB.BoundValue
End Sub[/vba]

Now, go back to your useform. This next part is going to add an array that will hold all the items that need to be linked to your class module. Add the following line at the top (after Option Explicit):
[vba]Dim ListBoxes() As New clsListbox[/vba]

Now, the last thing to do is to record your listboxes in the array, so that the class module is fired when they are clicked. To do this, add the following line to your procedure to dimension a counter:
[vba]Dim lLbCount As Long[/vba]

And then change your loop to read:
[vba] Do While Sheet1.Cells(1, I) > ""
UserForm1.MultiPage1.Pages.Add Sheet1.Cells(1, I).Text
II = 2
With UserForm1.MultiPage1.Pages(I - 1)
Set NewMPage = .Controls.Add("Forms.Multipage.1") 'adds dynamic mulitpage to every page in multipage1
NewMPage.Pages.Clear
NewMPage.Height = 250
NewMPage.Width = 700
Do While Sheet1.Cells(II, I) > "" 'Names each dynamically created multipage sheet
With NewMPage.Pages.Add
.Caption = Sheet1.Cells(II, I)
Set NewLbox = .Controls.Add("Forms.LIstbox.1") 'adds dynamic listbox to every nested multipage
NewLbox.Height = 200
NewLbox.Width = 600
NewLbox.Clear

'Link the listbox to the Class module
lLbCount = lLbCount + 1
ReDim Preserve ListBoxes(1 To lLbCount)
Set ListBoxes(lLbCount).LB = NewLbox

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''
'The following code will place all of the file names paths that are contained with in a certain folder.
For III = 0 To Number_Of_Files
NewLbox.AddItem File_Name(III) '??? How do I enable each new item added to the list box to be hyperlinked to a file?
Next III
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''
End With
II = II + 1 'Counter
Loop
End With
I = I + 1 'Counter
Loop[/vba]

You'll notice that I have not finished the code you asked about. I'll leave it up to you to populate the names of the files. If you need help with it, just ask.

Now, here's the thing. You are going to need to modify the code in the class module a bit to get it to fire your file. Check out Malcolm's post here to do this. You should be able to get the file name with the bound value, but I don't know about the rest of the path, as I haven't dug into your code that much. If your files come from a lot of places, you may want to consider adding another column to your listbox, and storing the full file path in there.

HTH,

[Treygor]

Ken, your suggestions worked great and the program runs effectively. Again, thank you for your patience with me and my code. I have pasted the entire code below so that in the future, if similar questions arise for others, they will have some code to follow as an example.


[VBA] 'Thanks to Ken Puls for the help on this one!

'Here is the code that creates nested multipages with listboxes embedded in the 2nd nested multipages.

'The listboxes are filled with file paths from designated folders.

'When list box is selected, it will open the path that is listed within the listbox.

'This code is not perfect but will be nice for learning purposes. I hope.





'"""""""Class Module
Public WithEvents LB As MSForms.ListBox
Private Sub LB_click()
'This code opens a file when the dynamically created listbox is selected
Dim MyFile As String, Cmd As String
MyFile = LB.Text 'This is the listbox text that is selected
Cmd = "RunDLL32.EXE shell32.dll,ShellExec_RunDLL "
Shell (Cmd & MyFile)
End Sub


''''''''''''''''''''''''Standard Module
'Option Explicit
Dim ListBoxes() As New ClsListBox 'Part of the class Module
Sub Set_Page_Topics()

Dim lLbCount As Long

Dim i, II As Integer
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'allows me to name the dynamically created multipage and listbox
Dim NewMPage As MSForms.MultiPage
Dim NewLbox As MSForms.ListBox
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
i = 1
''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Initialize the multipage to have zero pages to start.
UserForm1.MultiPage1.Pages.Clear
''''''''''''''''''''''''''''''''''''''''''''''''''''''

'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Adds the topics to the multipage.
'Topics must be placed on sheet 1, in consecutive cells
Do While Sheet1.Cells(1, i) > ""
UserForm1.MultiPage1.Pages.Add Sheet1.Cells(1, i).Text
II = 2
With UserForm1.MultiPage1.Pages(i - 1)
Set NewMPage = .Controls.Add("Forms.Multipage.1") 'adds dynamic mulitpage to every page in multipage1
NewMPage.Pages.Clear
NewMPage.Height = 250
NewMPage.Width = 700
Do While Sheet1.Cells(II, i) > "" 'Names each dynamically created multipage sheet
With NewMPage.Pages.Add
.Caption = Sheet1.Cells(II, i)
Set NewLbox = .Controls.Add("Forms.LIstbox.1") 'adds dynamic listbox to every nested multipage
NewLbox.Height = 200
NewLbox.Width = 600
NewLbox.Clear

'Link the listbox to the Class module
lLbCount = lLbCount + 1
ReDim Preserve ListBoxes(1 To lLbCount)
Set ListBoxes(lLbCount).LB = NewLbox


'This code will find the number of files in a certain folder and all of its sub folders.
'File_Name(III) will hold the names of all of those files.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''
Set FS = Application.FileSearch
Dim File_Name(20) As String
With FS
.LookIn = ThisWorkbook.path & "\" & Sheet1.Cells(1, i).Text & "\" & Sheet1.Cells(II, i)
'***************'************'*****'Might want to rename folders or cells to match at this point.'*************
.SearchSubFolders = False
.FileName = "*.PDF"
If .Execute() > 0 Then
'MsgBox "There were " & .FoundFiles.Count & " file(s) found."
Number_Of_Files = .FoundFiles.Count
For III = 1 To Number_Of_Files
File_Name(III) = .FoundFiles(III) '.FoundFiles(III) is path of file
NewLbox.AddItem .FoundFiles(III), III - 1 'This loads listbox with file names
Next III
Else
'MsgBox "There were no files found."
End If
End With

End With
II = II + 1 'Counter
Loop
End With
i = i + 1 'Counter
Loop
End Sub[/VBA]