Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help getting combobox to open a file
this is a bit hard to explain and im pretty new with excel.
what i need is a combobox (will have to add about 20 of these) which lists a value of my choosing, and when a user clicks one of these items in the list, it opens a file for that item. It would be useful if the items in the combobox were added/removed as i add and remove the items from the list that will be used to fill the box. the filenames will NOT be the same as the text in the combobox, and they will not be excel files, and for some of them it may just need to open a folder rather than a file. The idea ive had so far is have a hidden sheet with the list of text that i want in the combobox, and also for each of these text items make a hyperlink to the file or folder that should be opened when that item is clicked from the combobox. This way, all that needs to be done is when the user clicks the relevant item in the combobox, excel simply follows the hyperlink which should then open whatever its pointing to. It also makes it quick and easy to maintain since when adding a new item to the list, all i have to do is type the text i want in the combobox and then in the same cell add a hyperlink to the file it should open. I am pretty sure this is possible. for example purposes ill put the combobox on "sheet1" and the list of items that need to go in it on "sheet2". i cant use validation box because i need the dropdown to be visible at all times since this is going to be used by people that dont know anything about excel and so wouldnt know the list was there without the arrow being visible all the time. so can anyone explain exactly how to do this as im not a complete beginner (i can get the activex combobox onto the form and open the vb editor) but have no idea how to write the code to do this or where to put it (i assume it all goes into the combobox click event). thanks --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help getting combobox to open a file
That is correct, it would go in the combobox click event, so to get to that
event, after you place the combobox, double click on it. This will take you to the click event. Since you list will by dynamic, you probably will want to create a dynamic name to define it. do instert=Name=Define Name: List RefersTo: =Offset(Sheet2!$A$1,0,0,CountA(Sheet2!$A$1:$A$200) ,1) Make the 200 as long as the largest number of entries in the combobox you will have. Now in the properties of the combobox, put your listfillrange property to Workbookname!List then in the click event use code something like this: Private Sub ComboBox1_Click() Dim rng As Range Dim hl As Hyperlink If ComboBox1.ListIndex < -1 Then Set rng = Range("List")(ComboBox1.ListIndex + 1) Set hl = rng.Offset(0, 1).Hyperlinks(1) ThisWorkbook.FollowHyperlink Address:=hl.Address, _ SubAddress:=hl.SubAddress, NewWindow:=True End If End Sub Using the combobox to add new entries just add complexity. If you want to add more entries, go to sheet 2 and add it at the end of the current list. You might have to force a full recalc to get the combobox list to update. -- Regards, Tom Ogilvy "neowok " wrote in message ... this is a bit hard to explain and im pretty new with excel. what i need is a combobox (will have to add about 20 of these) which lists a value of my choosing, and when a user clicks one of these items in the list, it opens a file for that item. It would be useful if the items in the combobox were added/removed as i add and remove the items from the list that will be used to fill the box. the filenames will NOT be the same as the text in the combobox, and they will not be excel files, and for some of them it may just need to open a folder rather than a file. The idea ive had so far is have a hidden sheet with the list of text that i want in the combobox, and also for each of these text items make a hyperlink to the file or folder that should be opened when that item is clicked from the combobox. This way, all that needs to be done is when the user clicks the relevant item in the combobox, excel simply follows the hyperlink which should then open whatever its pointing to. It also makes it quick and easy to maintain since when adding a new item to the list, all i have to do is type the text i want in the combobox and then in the same cell add a hyperlink to the file it should open. I am pretty sure this is possible. for example purposes ill put the combobox on "sheet1" and the list of items that need to go in it on "sheet2". i cant use validation box because i need the dropdown to be visible at all times since this is going to be used by people that dont know anything about excel and so wouldnt know the list was there without the arrow being visible all the time. so can anyone explain exactly how to do this as im not a complete beginner (i can get the activex combobox onto the form and open the vb editor) but have no idea how to write the code to do this or where to put it (i assume it all goes into the combobox click event). thanks --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help getting combobox to open a file
ok thanks ill try that, it looks simpler than i thought.
yeah to add new entries i will not be using the combobox, that box wil ONLY be for users to select an item which then opens a file or folde (as pointed to by the hyperlink). for adding new items (the most wil be about 15 anyway) ill just add them to the list -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to open a 82 KB XLSM file due to "Too many different cellformats" & "Converter failed to open the file." errors. | Excel Discussion (Misc queries) | |||
Unable to open a 82 KB XLSM file due to "Too many different cellformats" & "Converter failed to open the file." errors. | Setting up and Configuration of Excel | |||
In Excel - Use Windows Explorer instead of File Open to open file | Excel Discussion (Misc queries) | |||
reading from another file and pasting to current file, "combobox" | New Users to Excel | |||
Combobox Change Event when file is saved as | Excel Programming |