Using "If then else" in column with validated options

[cdfjdk]

Can anyone point me to an example of using "if then else" in a column with drop-down options?

I have a drop-down list of countries (Angola, ..., Zambia) and in the same list a further option - Intercountry. I.e. all in a single column.

I would like the validation to permit the user to select multiple countries in the list if Intercountry is selected, but otherwise restrict the user to select only a single country, as in this pseudocode:

If Intercountry
Then select multiple countries = true
Else select multiple countries = false

Many thanks for any pointers!

 

[MickG]

Try this:-
NB:- The first selection in your Validation cell must be "Intercountry" to get Muliselection.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] rngDV [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] oldVal [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] newVal [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]If[/COLOR] Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Set[/COLOR] rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
[COLOR="Navy"]If[/COLOR] rngDV [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not Intersect(Target, rngDV) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
       Application.EnableEvents = False
            newVal = Target.Value
            Application.Undo
            oldVal = Target.Value
            Target.Value = newVal
                [COLOR="Navy"]If[/COLOR] Not newVal = "" And Left(oldVal, 12) = "Intercountry" [COLOR="Navy"]Then[/COLOR]
                    Target.Value = IIf(oldVal = "", newVal, oldVal & ", " & newVal)
                [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[cdfjdk]

Thanks Mick!
Have tried without success to get this working and will try again later today - but any suggestions as to settings maybe I have overlooked?

 

[MickG]

Have a look at this example:-
https://app.box.com/s/o87b53yo7w5cghoolv5bx82o5aftn7gt

Regrds Mick

 

[cdfjdk]

The code works in exactly the way I want in your example, only in my workbook it doesn't!
Maybe this is because there are other validated columns in the worksheet that interfere with SpecialCells(xlCellTypeAllValidation)?

 

[MickG]

You could perhaps change the line below to relate to a specific cell say "D1", as shown below.

If Not Intersect(Target, Range("D1")) Is Nothing Then

 

[cdfjdk]

Afraid not! Changing the range to D1 also knocks out the functionality in the example file. And I've tried with every other range combination I can imagine without success.

I've also tried restricting the range for SpecialCells(xlCellTypeAllValidation) without that helping, so I guess I was off track with my suspicions about that.

 

[MickG]

Have you got other "Event codes" in the sheet , this can produce a problem !!!

 

[cdfjdk]

Uhm, first thank you for your patience, Mick... I had it saved in a module, not under the worksheet.
It's working perfectly now!
CJ

 

[MickG]

You're welcome

Great news!!!!