Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I change the reference to a named range via VBA? | Excel Programming | |||
reference first cell in a named range | Excel Worksheet Functions | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
named range, offset self-reference | Excel Discussion (Misc queries) | |||
Reference a named range | Excel Programming |