Creating Dynamic Dependent Defined Names Using VBA

This might get you started. I took this from a similar situation I ran into recently take a look.

In my column A, on the Input Sheet, the State selected determines the list available for the Column B (County or Parish name) and the drop downs come from named ranges on the "Menus" tab.

The validation for column A is a named range, which contains the Column Headers for the other named ranges....the validation for column B uses the indirect function to grab whatever name is in the A side...

Have a look, may be helpful?

Dear GeneralDisarray,

Thanks for the file it indeed is useful but I am looking for a solution in VBA and not in Formulas as mentioned earlier I want to create Dynamic i.e Expanding Lists but also want to have the Dependency in it , the way it in your Sheets..

I want to have the VBA code in such a way where the lIst is expandable too..

I respect and appreciate your help but I know of this method where we can have the Dependednt Validations via Formulas though its done very well and will set a good example for Data Validation for Dependent Lists but the same thing if done via VBA can also have the Name.Hidden False Feature in it...

So if you hav the VBA code please provide an example...

Regards
e4excel

Dear Jaslake,

Thanks for the Link, however I am already aware of the Formula Method, right now my net speed is too slow for the link to open completely so i had to give up..

But looking at the beggining of the tutuorial it seems that its showing a Formula approach which Im already aware of..

I am looking for the VBA Approach the way u showed me in my other post..

To testify that I know it please find my attachment , however I need the same thing created with VBA...

REgards
e4excel

Dear Jaslake,

AS per my first thread in this query what I require to do is have two different lists getting allocated depending on the selection of another dropdown.

I know the way of doing it via formulas but the problem is how do I do it in VBA?

I just need help in this step where I want to have the Dependednt Validation in the Column D.

Where I have seelctred any Bank A, Bank B, Bank C then its should show have Deposit_Types in the Column D and if the selection in the Column C is POstal Investments then the Postal_Investments should come up..

So How Do I write this code...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Regards
e4excel

Dear Forum,

I think I got the solution but I still would want someone to improve the code as it repeats the Bank_Deposit thrice for the three Banks Investments..

Please Login or Register  to view this content.

Slight;y Better Solution..!

Please Login or Register  to view this content.

Still Please feel free to add more value if there are better ways..

Thanks in advance

Thanks SNB,

For an Alternatie Solution but I think I will take time to understand the coding but it does give more options..

Thnks again..!

But what I meant was that the line

Please Login or Register  to view this content.

Can it be improved ?
I am going to definitely look at your solution though but its something that I have not done often so some time will be taken by me to understand the same.

Regards
e4excel

Snb,

LIke I said and always mention [Option Explicit] that I have a very long way to go in Excel VBA and therefore to understand the coding in pure VBA is not easy..

SInce I have a fair understanding in Formulas, I can put two and two together and come up with something to serve my purpose and as you rightly said that if I were to use complete VBA then obviously formulas might not be neccessary..

But i have not reached that stage yet and therefore I always ask for Explantions and for Formula-Inclined solutions so that it becomes easier for me to replicate them in future, in the interim I am also trying to learn new things in VBA too.

Thanks Everyone for the help provided..

I know that the query is SOLVED but I forgot to ask one more thing is about the ranges which are FIXED and hard-coded such as below...

Define Name- Compounding_Frequency - Annual,Half-Yearly,Quarterly,Monthly

How to do the same in VBA?

Please help me with a code to mention in this fashion for the Hard-coded names..

Please Login or Register  to view this content.

I tried this does not work the Source Evaluates to an error..

Originally Posted by snb

Please Login or Register  to view this content.

You'd better use validation.modify

Thanks for the reply SNB...

BUt Please explain what you meant by "Validation.Modify" and also what does the Number after Add indicate..

.Add 3,1,1, ?

Just one more thing,

How DO I make a Name Define Formula such as Deposit_Types which is a Defined Name and Now I want it to house other Defined Names such as FDP,QDP,YIP,etc..

I have formula in one of the cells which goes like this...

Please Login or Register  to view this content.

Now instead of adding the three letter names in a linear fashion I would prefer to split it into two parts as there are 2 types of Investments ..One is through the Bank Deposits and other throught Indian Postal Services?

I want to have just 2 mentioned in the formula as below:

Deposit_Types = FDP,RDP,QRD,YIP,HIP,QIP,MIP where all the 3 letter words are Defined Names and also the Deposit_Types.


Postal_Types = NSC,KVP,PO1,PO2,PO3,PO5,SRC..

Please explain and also advise for this requirement too..!

Dear SNB,

Thanks for the help,

I dont mind starting a New Thread SNB but I am unravelling a lot of things while using the solutions, so this was not a part of the original question as the more I used VBA I learnt something new...

I have used your solution but how do I Define a Name with VBA with such Fixed Values?
AS I need to use the Nmae in a Formula and not in a Data Validation List..

Please revert if you need me to start a new thread as I only had these two queries and I am done..

Warm Regards
e4excel