Dropdown box
 

Im trying to get dynamic dependent dropdowns to work.

I was referred to xldynamic.com - and downloaded their code,
(http://www.xldynamic.com/source/xld.Dropdowns.html#dv)

I am wanting to do it using excel validation. I downloaded the sample
excel file, but when trying to get the file to work, Excel returns the
error, "Method range of object failed", and then I am referred to the
code see below.

Any ideas - to fix the code, or where else I can try....

Many thanks Darin



If Not Intersect(Range(kList1), Target) Is Nothing Then
If Target.Count = 1 Then

With data.Range(kList1Hnd)
Set oFoundCell = .Find(what:=Target.Value, _
LookIn:=xlValues)
If oFoundCell Is Nothing Then
MsgBox "Critical error"
Exit Sub
End If
End With

'load the List2 dropdown and set the default to item 1
iTargetCol = oFoundCell.Column - 1
fzCreateValidationList2 Target.Offset(1, 0), iTargetCol,
Target
Target.Offset(1, 0).Value = data.Range(kList2Hnd &
iTargetCol).Value
End If



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Dropdown box
 

Hi
if you want to use the data validation listboxes see:
http://www.contextures.com/xlDataVal02.html

"Darin Kramer" wrote:



Im trying to get dynamic dependent dropdowns to work.

I was referred to xldynamic.com - and downloaded their code,
(http://www.xldynamic.com/source/xld.Dropdowns.html#dv)

I am wanting to do it using excel validation. I downloaded the sample
excel file, but when trying to get the file to work, Excel returns the
error, "Method range of object failed", and then I am referred to the
code see below.

Any ideas - to fix the code, or where else I can try....

Many thanks Darin



If Not Intersect(Range(kList1), Target) Is Nothing Then
If Target.Count = 1 Then

With data.Range(kList1Hnd)
Set oFoundCell = .Find(what:=Target.Value, _
LookIn:=xlValues)
If oFoundCell Is Nothing Then
MsgBox "Critical error"
Exit Sub
End If
End With

'load the List2 dropdown and set the default to item 1
iTargetCol = oFoundCell.Column - 1
fzCreateValidationList2 Target.Offset(1, 0), iTargetCol,
Target
Target.Offset(1, 0).Value = data.Range(kList2Hnd &
iTargetCol).Value
End If



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Dropdown box
 

Hi Frank -thanks I looked at the link and got it to work. However I have
two additional questions
(box 2 is dependant on result in box 1)
1) the list boxes that are automatically displayed are very short (only
hold about 5 entries...., can I extend them? - ie make them longer so u
dont have to scroll up and down so much...?
2) when you do multiple changes to box 1, box 2 should be cleared, but
it just keeps the previous Box 1 result....?

Thanks

D

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Dropdown box
 

Hi
1) Not possibl
2) Also not possible without VBA


"Darin Kramer" wrote:

Hi Frank -thanks I looked at the link and got it to work. However I have
two additional questions
(box 2 is dependant on result in box 1)
1) the list boxes that are automatically displayed are very short (only
hold about 5 entries...., can I extend them? - ie make them longer so u
dont have to scroll up and down so much...?
2) when you do multiple changes to box 1, box 2 should be cleared, but
it just keeps the previous Box 1 result....?

Thanks

D

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Dropdown box
 

Frank

I Would prefer to use VBA - dont need any fancy VBA to decide my lists -
I know what they are and they not going to change... where can I look
for VBA code just for the dependant lists for data validatin (the one on
xldynamic is too complex (selects lists) and doesnt work)

Really appreciate you assistance...

Tahks

D

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!