+ Reply to Thread
Results 1 to 21 of 21

Alternative for Defined Name Ranges created by a Macro?

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

    Alternative for Defined Name Ranges created by a Macro?

    Is there an Alternative for Defined Name Ranges created by a Macro?

    Dear Forum,

    I have found a way of creating Defined Names with a Macro ..
    http://www.contextures.com/xlNames03.html

    Please Login or Register  to view this content.
    No this works fine but is it possible to not have the Defined Names in the Formula Manager but yet have the functionality going smooothly..

    Example:
    When I tried changing the colour of a particular column based on different values using a Macro it worked fantastically without even showing anything in the conditional formatting..
    Which means that if im distributing the file no-one will know how its done but yet it works like conditional formatting..

    On similar grounds, is it possible to have the Defined Name Created but not shown when one goes to the Formula Manager?

    I know that I can always lock the VBA Module code but trying to Protect the sheet sometimes causes some Worksheet_Activate,Change and Selection Change events to not fire...

    Warm regards
    e4excel

    Hope I have been able to explain correctly..
    Last edited by e4excel; 09-02-2011 at 10:07 AM.

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

    Re: Is there an Alternative for Defined Name Ranges created by a Macro?

    By Alternative I meant to create it with a Macro but not show it in the Formula manager, as I know the conventional way of creating names....from the Name manager manually...
    Attached Files Attached Files
    Last edited by e4excel; 08-29-2011 at 03:04 AM.

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

    Bump No Response

    Bump No Response

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

    Bump No response

    Bump No response

  5. #5
    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: Is there an Alternative for Defined Name Ranges created by a Macro?

    Hi e4excel

    When your code is done with the created names have the code delete them.
    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.

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Is there an Alternative for Defined Name Ranges created by a Macro?

    That's the strangest reason for using that I have seen - to stop the user knowing that you have used Conditional Formatting.

    I don't know why you need the code to define the Named Ranges, but you can hide the names
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  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: Is there an Alternative for Defined Name Ranges created by a Macro?

    @royUK

    Didn't know that...thanks for adding to my education.

    @e4excel

    See here for discussion http://www.cpearson.com/excel/DefinedNames.aspx

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

    Re: Is there an Alternative for Defined Name Ranges created by a Macro?

    Thanks a lot Jaslake, that was very useful, I just needed to know whether that was possible to hide it but since it is I might just use it while distribtuing to avoid any tamper to the Defined Names..

    For Reference some excerpts from the link :


    By default, names created manually or with VBA code are visible -- they will appear in the Name Box drop down and in the Names dialog. You can, however, make the name hidden so that it isn't visible to the user. A hidden Name can be used in any manner just like a normal, visible Name. To make a name hidden, you set the Visible parameter to False. For example,

    Please Login or Register  to view this content.
    The name SomeName2 can be used exactly as a visible name, but will not appear to the user. The only way to hide a Defined Name is via VBA code. There is nothing in the Excel user interface that allows you to hide a Name.
    Warm Regards
    e4excel

  9. #9
    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: Is there an Alternative for Defined Name Ranges created by a Macro?

    Hi e4excel

    You're welcome...glad to be of help...and thanks to royUK for adding to our knowledge.

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

    Re: Is there an Alternative for Defined Name Ranges created by a Macro?

    I know that this thread is closed as I had already got a solution on the main query, however there's something connecting to it which prompted me to ask again..

    So, please help me on the same...

    Thanks for the help with which I tried my first Defined Name with a Macro..

    Please Login or Register  to view this content.
    In Formulas we usually use the OFFSET function to get an Expanding Name,
    Please Login or Register  to view this content.
    ...

    Likewise, in VBA , how would i get the same effect for creating a Defined Expanding Macro and also How do I add it to the Data validation List...

    Please Login or Register  to view this content.
    So I can use it widely as I am still stuck with two things now, Expanding Name and Adding the Name to the Validation List..

    I am learning VBA every moment and its really wonderful..

    Warm regards
    e4excel

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

    Re: Is there an Alternative for Defined Name Ranges created by a Macro?

    I think I got the first part , stupid of me not to look at the first post again ..

    I think the second part is also underway but please feel free to add more information so that others can also find thisuseful the way I did..

    Please Login or Register  to view this content.
    Will need to look at the 1st post code carefully as it has so many Constants and Variables, never used them earlier..Always a first time

    But when I run the Macro again it gives me an error 1004 as the Range already has a Data-validation List so what do i do for that?
    Last edited by e4excel; 09-16-2011 at 06:56 AM.

  12. #12
    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: Is there an Alternative for Defined Name Ranges created by a Macro?

    Hi e4excel

    Seems to me once you've done this
    Please Login or Register  to view this content.
    you don't need to do this
    Please Login or Register  to view this content.
    If your Data Validation is set to Lists --> "Deposit_Type". Haven't tested it.

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

    Re: Alternative for Defined Name Ranges created by a Macro?

    Dear Jaslake

    Ok!

    But then one is that Im defining the Name with a Macro but the other is that I need to use the name in the range of the cells E2:E20..

    So how do I do that as that became difficult..

    Warm regards
    e4excel

  14. #14
    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: Alternative for Defined Name Ranges created by a Macro?

    Hi e4excel

    Try this
    Please Login or Register  to view this content.

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

    Re: Alternative for Defined Name Ranges created by a Macro?

    Thanks a lot Jaslake,

    You wont believe me even after you giving me code, I was having difficulties implementing it but ultimately perseverance paid off..

    And it worked really well..

    Thanks again , now I understood how to Add Data Validation and also define name in the same Macro..

    REgards
    e4excel

  16. #16
    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: Alternative for Defined Name Ranges created by a Macro?

    Hi e4excel

    Happy to be of help. Thanks for the kudos.

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

    Re: Alternative for Defined Name Ranges created by a Macro?

    I am thankful to you that I could learn the codes..!
    You deserved tthe kudos and everyone in the forum does..!

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

    Re: Alternative for Defined Name Ranges created by a Macro?

    Thanks a lot everyone who have helped me so far in the Data Validation Endeavor..

    Really very thankful..

    Its cool..

    Just also one more thing though, i dont know whether a new Query is required or not..

    Can I also do dependent Validation on 2 different Lists with a Macro..

    If yes/ no please let me know so that i cn start a new thread though its very much relevant..

    Regards
    e4excel
    Last edited by e4excel; 09-20-2011 at 05:15 AM.

  19. #19
    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: Alternative for Defined Name Ranges created by a Macro?

    Hi e4excel
    Yes I think a new Thread would be appropriate.

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

    Re: Alternative for Defined Name Ranges created by a Macro?

    Thanks Jaslake,

    Will be starting , New one then..!

    Regrds

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

    Re: Alternative for Defined Name Ranges created by a Macro?

    Dear Jaslake,

    I started a new thread for creating Dependent Dropdowns using VBA...

    http://www.excelforum.com/excel-prog...using-vba.html

    REgards
    e4excel

    Thanks for the help

+ 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