Add Hyperlink to Listbox Entry?

[bdmangum]

Per the title, I am trying to adding a hyperlink to a listbox entry. Let me outline what I am currently doing and what I would like to accomplish.

Outline:
I have an Excel file which through the use of forms I modified it into a simple database. The data is stored on a sheet using rows and columns. Each row is an entry with the columns containing different information about the entry.

I created a system which allows the user to upload an attachment with each new entry if they so desire. This is noted by a column which contains a hyperlink to the file.

The user is able to view the entries to the database through the use of a "master" viewing form. Basically this form capture the basic information of each entry and allows the user to search through it. I accomplished this through the use of a list box with six columns and six textboxes each located above a column for searching purposes.

Goal:
I would like to create a seventh column in the listbox used in the master viewing form which contains a hyperlink to any attached file. This would allow the user to easily load any related file without having to exit the form. The problem is that I cannot figure out how to add a hyperlink into a listbox. As near as I can tell the listbox allows only plain text. Does anybody know a work-around?

I considered simply using dynamic buttons off to the side of the listbox. It worked fine until the listbox contained more entries than the height allowed and thus created a scrollbar, which destroyed the buttons idea.

Any thoughts?

TIA,
BD

 

[Bong]

In a listbox, you don't really have visibility into the various columns, only the indivedual items, that is, rows.

I imagine that the items in your listbox are linked to a range of cells so that when a row is selected, you can decompose the columns of that row by way of the cells it is linked to, no?

I don't know what event is associated with selecting, or changing the selection of, an item in the listbox, but it should be possible to find one. Then, upon that event, you can follow the hyperlink (<hyperlink>.follow) in the appropriate cell associated with the listbox row selected (sheet1.ListBox1.listindex).

_________________
Bob Rashkin

 

[bdmangum]

Bob,

Thanks for the response. You grabbed the nature of my problem. I can't load the attached file upon selecting the row because the user is required to select the row in order to accomplsih searching/sorting functions. Thus your solution won't work in my situation.

I considered making a second listbox which is positioned directly to the right of the first listbox. This would work if I can somehow link the boxes so that when the user scrolls down in one the other also scrolls down. I can't seem to find the correct event to accomplish this task. If I can sort out that issue, I may be able to implement something similar to Bob's suggestion.

Thanks,
BD

 

[Bong]

I think the event you want is _click():

Private Sub object_Click( )

where object would be ListBox[red]n[/red]. Then upon that event, find the index of the item selected:

i=sheet1.ListBox[red]n[/red].ListIndex
and set the selection of the other listbox:
sheet1.ListBox[red]m[/red].selected(i)=True

_________________
Bob Rashkin

 

[bdmangum]

Bob,

Again, thanks for the response.

Your solution works perfectly when the user selects an item in ListBoxA. However, if the user simply scrolls down ListBoxA without making a selection, ListBoxB remains unchanged. This is a problem. I need it so when the user simply scrolls down a listbox, not making a selection, both listboxes scroll down. There has to be a method which accomplishes this task, I just can't find it.

Here's what I'm doing to test it out. I created a new workbook and added one form. Now on that form create two small listboxes side by side. Now fill the first ten cells in columns A and B on sheet one with the numbers 1-10 in there corresponding row.

I'm using this code to load the form.

Private Sub UserForm_Initialize()

Dim count As Integer

count = 1

While Worksheets(1).Cells(count, 1).Value <> ""
    Me.ListBox1.AddItem Worksheets(1).Cells(count, 1).Value
    Me.ListBox2.AddItem Worksheets(1).Cells(count, 2).Value

    count = count + 1
Wend

End Sub

Now I need it so the numbers in the listboxes always align correctly. Meaning 1 is across from 1, 2 across from 2, etc. The question is how do I make that work?

 

[Bong]

You can synchonize the scroll with:
sheet1.ListBox2.TopIndex=sheet1.ListBox1.TopIndex

What I don't know is what event captures the scrolling of the first listbox.

_________________
Bob Rashkin

 

[bdmangum]

I can't seem to find an event which captures the scrolling. I tried almost every listbox event and none worked. There has to be some way to capture the scrolling. I guess we keep looking.

 

[Bong]

Of course you could just wait until the user selects an item from ListBox1 and then synch the listboxes with the corresponding row selected in ListBox2?

_________________
Bob Rashkin

 

[bdmangum]

I could, and I did consider it. However this scenario played through my head. Suppose the user scrolls down listbox1 until the find entry X, yet they do not select it. Now the listboxes are currently not in sync, thus the hyperlinks are not lined up correctly. So now the user wants to open the attached file, however the hyperlink currently in line with entry X is the wrong one. Thus that system cannot work.

I could insert a restriction on when the hyperlink will load, thus forcing the user to select the entry they wis to view. I doubt my customer will like that setup.

Thankfully, this new file link tool I'm incorporating into the database is purely intended as a perk and ease of access method. Thus if I can't make it work, it's not that big of a deal.

 

[Bong]

Well, if you don't mind being a bit draconian, you could make ListBox2 invisible unless ListBox1 has an item selected.

_________________
Bob Rashkin