Call a specific validation dropdown list based on another validation dropdown list

[Karlbirger]

Hi guys,

And good morning to you all (dependning on if you on the plus side of GMT).

This is my first post in this forum, and I'm grateful for any help you pro's can give me. I have tried to solve this problem for myself for two days now (while I've searched google the same time for the answer). But I can't find any answer to this problem.

Software in use: Windows Vista & Excel 2007


What I want to do
I want to call a validation dropdown list in cell J6, based on what input you choose from the validation dropdown list in cell I6.
Both lists contains only text, and the list in cell I6 have three options to choose from: "Real estate", "Energy", or "Other".
Based on which one of these three you choose, one specific validation dropdown list should appear in cell J6 (the list connected to either real estate, energy, or other).


What I have done
I have tried several different techniques, ranging from cowboy vba in modules to worksheet_change and calculate in the worksheet. My latest grasp for a correct code looks like the following.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("I6") = "Real estate" Then

Range("J6").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Real"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With


Elseif Range("I6") = "Energy" Then

Range("J6").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Energy"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With


Elseif Range("I6") = "Other" Then

Range("J6").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Other"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End If

End Sub

When I run this code, I get an error message:

"Run-time error '1004':
Application-defined or object-defined error"

The first line of code the debugger hits at is the one in bold below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("I6") = "Real estate" Then

Range("J6").Select
    With Selection.Validation
        .Delete
[B]        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Real"[/B]
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With


ElseIf Range("I6") = "Energy" Then...

etc...

Okey, that's about it. I hope the information above is enough to get around and come closer to a solution.

Thanks in advance.

Johan

 

[VoG]

This may help you http://www.contextures.com/xlDataVal02.html

 

[Karlbirger]

Thank you VoG for such a quick answer.

I have went through the guide (really nice, how could I have missed this one?!) but I still have one dilemma.

In my first data validation dropdown list (cell I6), I have three words:
1. Real estate
2. Energy
3. Other

When I use the formula for two words, I get an error message (it works fine if I use single words, e.g. Realestate, and not using the formula for two words).

Do I have to use dynamic lists or is there any complement I can add to the formula to be able to use both sinle words and two words in the first list?

Regards
Johan

 

[VoG]

That is covered here http://www.contextures.com/xlDataVal02.html#TwoWord

 

[Karlbirger]

Like I said, I used the forumla for two words, but I get an error message telling me "The formula you entered cotains an error".

I'm asking myself, is it because I'm using both single words and two words in the first list? I don't know the answer, but it works fine when I remove the two words formula and change "Real estate" to "Realestate". But that ain't so goodlooking imo.

Thanks in advance.

Johan

 

[Karlbirger]

To clarify, the formula below does not work when I put it on cell J6 as to support for cell I6

 =INDIRECT(SUBSTITUTE(A2," ",""))

"The formula you typed contains an error"



Thanks in advance.

Johan

 

[Karlbirger]

Many posts now, I'm sorry.

Ofcourse I don't have cell A2 in my code, I use cell I6.

/Johan

To clarify, the formula below does not work when I put it on cell J6 as to support for cell I6

 =INDIRECT(SUBSTITUTE(A2," ",""))

"The formula you typed contains an error"



Thanks in advance.

Johan

 

[Karlbirger]

My previous post may have been confusing. My point is that the formula below does not work.

Grateful for input/help.

Johan

INDIRECT(SUBSTITUTE(I6," ","")