Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop Down List
Hi,
I'm trying to use a drop down list as a navigation system for a work sheet that has over 40 charts in it. I have created a drop down list (at the top of the worksheet above a frozen window split) that has entries populated with the static reference names that I am using (ie the names of the charts). What i want to do is allow the user to scroll down the list and click an item in the list. As they click the item i want to have excel select and display the area behind the chart that the item describes. Eg A name in the list is Anual Total Expenditure. It is physically located above cells b51:j61. If i use a simple hyperlink i would normally set it to (b51:j61). How do i make it such that if a user clicks on Anual Total Expenditure in the drop down list, that excel will select b51:j61 as if the user had clicked on a hyperlink? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop Down List
Hi there
I did this using very simple code, but first you have to physically create the hyperlinks. OK, go to the linked cells for your drop down list, and hyperlink each entry to where you want it to go exactly. Then, go to the code for the drop down list and insert the following pieces of code Private Sub ComboBox1_Change() If ComboBox1.Text = "Annual Total Expenditure" Then Range("Z3").Select ' This is where the linked cell for Annual Total Expenditure is Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True End If If ComboBox1.Text = "Monthly Total Expenditure" Then Range("Z1").Select ' This is where the linked cell for Monthly Total Expenditure is Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True End If ' ' Keep entering the If Then statements until you have all your navigation complete ' ' End Sub Hope this makes sense.. PS: you will have to write a piece of code like this for each hyperlink. Sorry I don't know of a shorter way... I'm sure there is one, but this works. Let me know how you get on... Sachin Singh CTS wrote: Hi, I'm trying to use a drop down list as a navigation system for a work sheet that has over 40 charts in it. I have created a drop down list (at the top of the worksheet above a frozen window split) that has entries populated with the static reference names that I am using (ie the names of the charts). What i want to do is allow the user to scroll down the list and click an item in the list. As they click the item i want to have excel select and display the area behind the chart that the item describes. Eg A name in the list is Anual Total Expenditure. It is physically located above cells b51:j61. If i use a simple hyperlink i would normally set it to (b51:j61). How do i make it such that if a user clicks on Anual Total Expenditure in the drop down list, that excel will select b51:j61 as if the user had clicked on a hyperlink? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop Down List
Fantastic. This is exactly what i needed. The code makes sense to me. I
was prepared to do this anyway which worked out well.. Thanks " wrote: Hi there I did this using very simple code, but first you have to physically create the hyperlinks. OK, go to the linked cells for your drop down list, and hyperlink each entry to where you want it to go exactly. Then, go to the code for the drop down list and insert the following pieces of code Private Sub ComboBox1_Change() If ComboBox1.Text = "Annual Total Expenditure" Then Range("Z3").Select ' This is where the linked cell for Annual Total Expenditure is Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True End If If ComboBox1.Text = "Monthly Total Expenditure" Then Range("Z1").Select ' This is where the linked cell for Monthly Total Expenditure is Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True End If ' ' Keep entering the If Then statements until you have all your navigation complete ' ' End Sub Hope this makes sense.. PS: you will have to write a piece of code like this for each hyperlink. Sorry I don't know of a shorter way... I'm sure there is one, but this works. Let me know how you get on... Sachin Singh CTS wrote: Hi, I'm trying to use a drop down list as a navigation system for a work sheet that has over 40 charts in it. I have created a drop down list (at the top of the worksheet above a frozen window split) that has entries populated with the static reference names that I am using (ie the names of the charts). What i want to do is allow the user to scroll down the list and click an item in the list. As they click the item i want to have excel select and display the area behind the chart that the item describes. Eg A name in the list is Anual Total Expenditure. It is physically located above cells b51:j61. If i use a simple hyperlink i would normally set it to (b51:j61). How do i make it such that if a user clicks on Anual Total Expenditure in the drop down list, that excel will select b51:j61 as if the user had clicked on a hyperlink? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
drop down list based on other drop down list pick | Excel Discussion (Misc queries) | |||
Drop down list dependant on previous drop down list | Excel Discussion (Misc queries) | |||
Drop down lists that auto create and then filter the next drop down list | Excel Worksheet Functions | |||
Drop Down List choice selecting another drop down list | Excel Worksheet Functions | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) |