Combox Box - Need to Reference Named Range
I have a drop down menu in a Form,
I'd like to have it reference a specific name range in a sheet. I have a formula that creates the dynamic range automatically. let's say for example "dynRange" is the Name of it. I'd like to have a user double click the cell the form opens. And then they Can select two different drop downs and a certain value appears from that. Question: How do I reference a Named Range in a DropDown "Combo box" and what's the code/event procedure for if a user double clicks a cell, then I can do the open.form Thank you in advance! |
Combox Box - Need to Reference Named Range
hi
here is a double click event that will show your form. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim r As Range Set r = Range("B2") 'change to suit If Not Intersect(Target, r) Is Nothing Then UserForm1.Show Else Exit Sub End If End Sub it is the "named range" that has me confused. how are you creating it? formula? can you post your formula. and what do you want to do once the named range is referenced. you could use the add item method in the form's initialzation event to add the name to the combo box. then what?????? regards FSt1 "Benjamin" wrote: I have a drop down menu in a Form, I'd like to have it reference a specific name range in a sheet. I have a formula that creates the dynamic range automatically. let's say for example "dynRange" is the Name of it. I'd like to have a user double click the cell the form opens. And then they Can select two different drop downs and a certain value appears from that. Question: How do I reference a Named Range in a DropDown "Combo box" and what's the code/event procedure for if a user double clicks a cell, then I can do the open.form Thank you in advance! |
Combox Box - Need to Reference Named Range
=LISTS!$A$2:INDEX(LISTS!$A:$A,COUNTA(LISTS!$A:$A))
That gives me Combo Box 1 =INDEX(ValData,1,MATCH('Data Entry'!G6,LISTS!$1:$1,0)): INDEX(ValData,Counter,MATCH('Data Entry'!G6,LISTS!$1:$1,0)) That will give me the Combo Box 2 's dependent value. Then it will lookup a corresponding Code on a Lookup table. "FSt1" wrote: hi here is a double click event that will show your form. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim r As Range Set r = Range("B2") 'change to suit If Not Intersect(Target, r) Is Nothing Then UserForm1.Show Else Exit Sub End If End Sub it is the "named range" that has me confused. how are you creating it? formula? can you post your formula. and what do you want to do once the named range is referenced. you could use the add item method in the form's initialzation event to add the name to the combo box. then what?????? regards FSt1 "Benjamin" wrote: I have a drop down menu in a Form, I'd like to have it reference a specific name range in a sheet. I have a formula that creates the dynamic range automatically. let's say for example "dynRange" is the Name of it. I'd like to have a user double click the cell the form opens. And then they Can select two different drop downs and a certain value appears from that. Question: How do I reference a Named Range in a DropDown "Combo box" and what's the code/event procedure for if a user double clicks a cell, then I can do the open.form Thank you in advance! |
All times are GMT +1. The time now is 01:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com