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!

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!
>

=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!
> >