+ Reply to Thread
Results 1 to 20 of 20

Creating Dynamic Dependent Defined Names Using VBA

  1. #1
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,400

    Creating Dynamic Dependent Defined Names Using VBA

    Dear Forum,

    I have been using Defined Names created manually and have also learnt to create them with the help of VBA..

    But, there are times when we need to also have Dependent Drop_Downs on the value of a another dropdown or cell value..

    In my example file, I have 3 Main Lists

    1. For the Account Holder
    2. Name of the Bank - Bank A,Bank B, Bank C, Postal Investments, ..... etc
    3 Investment Types - There would be 2 lists in this column dependent on the selection in the Name of the Bank...Bank Investments are Deposit_Types and the Postal Investments are called as Postal_Investments..

    For all practical purposes, I have just defined them manually...

    Now but I know them to defined them suing a Macro but do not know how to do the Dependency in that?

    Ex: If I selct Postal Investment then I want the list for Postal Investments and if Bank A,B or C is selected then Deposit_Type list should come up..

    Please find the attachment..
    I have already got answers in my quest on defining Names using VBA..

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

    using the OFFET function for Dynamic List

    http://www.excelforum.com/excel-prog...y-a-macro.html


    Warm Regards
    e4excel
    Attached Files Attached Files
    Last edited by e4excel; 09-23-2011 at 04:36 PM.

  2. #2
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: 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?
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,400

    Re: Creating Dynamic Dependent Defined Names Using VBA

    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

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Creating Dynamic Dependent Defined Names Using VBA

    Hi e4excel

    Take a look at this link...it's a video that steps you though Dependant Data Validation.
    http://www.contextures.com/excel-vid...html#Dependent
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,400

    Re: Creating Dynamic Dependent Defined Names Using VBA

    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
    Attached Files Attached Files

  6. #6
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Creating Dynamic Dependent Defined Names Using VBA

    Hi e4excel

    This Thread describes what you wish to do
    http://www.excelforum.com/excel-gene...ed-ranges.html

    Basically it says it can't be done...however, it offers a ""workaround". I've not yet studied the "workaround" but will. I'd suggest you do the same. I'll keep you up to date on what I discover...appreciate if you'll do the same.

  7. #7
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Creating Dynamic Dependent Defined Names Using VBA

    Hi e4excel

    With the help of the Thread I referenced I'm able to get this code to work with Dynamic Named Ranges...copy of file attached.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,400

    Re: Creating Dynamic Dependent Defined Names Using VBA

    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

  9. #9
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,400

    Re: Creating Dynamic Dependent Defined Names Using VBA

    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.

  10. #10
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,400

    Re: Creating Dynamic Dependent Defined Names Using VBA

    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

  11. #11
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Creating Dynamic Dependent Defined Names Using VBA

    There are....
    For a purely VBA-approach see the attachment
    Attached Files Attached Files



  12. #12
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,400

    Re: Creating Dynamic Dependent Defined Names Using VBA

    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

  13. #13
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Creating Dynamic Dependent Defined Names Using VBA

    I think you only need dynamic named ranges if you are not using VBA. To me it seems to be a userinterface solution.
    As soon as you are able to use VBA, you can use the dynamic objects 'usedrange', 'currentregion', 'intersect', 'end(xlup)','specialcells', etc.
    Also if you use VBA you won't need a formula referring to a range in a worksheet to define the validation formula.
    So I won't comment on your formula because I think you won't need it in the first place.

  14. #14
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,400

    Re: Creating Dynamic Dependent Defined Names Using VBA

    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..

  15. #15
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,400

    Re: Creating Dynamic Dependent Defined Names Using VBA

    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?

  16. #16
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,400

    Re: Creating Dynamic Dependent Defined Names Using 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..

  17. #17
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Creating Dynamic Dependent Defined Names Using VBA

    Please Login or Register  to view this content.
    You'd better use validation.modify

  18. #18
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,400

    Re: Creating Dynamic Dependent Defined Names Using VBA

    Quote Originally Posted by snb View Post
    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..!

  19. #19
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Creating Dynamic Dependent Defined Names Using VBA

    I get the impression that you do not consider this thread as being 'solved'. If you do nevertheless, please act accordingly by starting a new thread.

  20. #20
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,400

    Re: Creating Dynamic Dependent Defined Names Using VBA

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1