vba cascading dropdown list with free text entry option

Dear Forum,

I have been trying to code a data validation drop-down list. The reason I am using code is that the list is a cascading list that may require free text entry depending on choices selected.

The basic premise of the code:

If user selects "Site" as "Company" then they are presented with a drop-down and a cascading drop down list.
If user selects "Site" as "Third Party" or "Customer" they are presented with a free text field to enter "Location"
There are a few nuances if the user selects US or another country in that "N/A" gets populated into the region field for non-US.

Issues
• I keep getting an error when I select "Company".
• Not sure if “Case” is correct approach or do I use “If else” statements, or can both be mixed?
• Formula1 needs to reference the correct row. I've tried using ThisRow in the formula for the indirect statements but just get errors every time.

Code.jpg

Any guidence would be appreciated.
Thanks

Hi and thank you for speedy reply. Apology for the picture but I was getting firewall block message with the code enserted as per instructions. Couldn't figure out what I was doing wrong so resorted to the image

Good to learn that "case" route was right way. How does one choose one approach over another?

A key issue is that that the validation code for "Company" case is failing. The code construct looks exactly as per reference manual. Could this be due to invalid selection error (I would click to ignore) if entering the dropdown manually? Do I need some error routine here to ignore that?

Sorry for delay.

The error flags this line but not every time. I changed selection 10 times in a row and it didn't happen. Then it occured 5 times in a row and I reset all the cells to original values and it started to work again (but multiple rows).

< code > .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=IF(I5=""US"",INDIRECT(J5),INDIRECT(I5))" < /Code >

This code is referencing criteria in I5 to get cascade drop down based on value in J5.

The error is:

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

Do you think its because I'm testing it static on line 5 (as test) but the wrap around code is referencing multiple rows?

Thanks. Hold fire... I will upload a copy of my worksheet on Friday.

Example file uploaded