Alternative for Defined Name Ranges created by a Macro?

**e4excel** · 08-29-2011, 02:36 AM

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

**e4excel** · 08-29-2011, 02:38 AM

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

**e4excel** · 08-31-2011, 03:17 AM

Bump No Response

**e4excel** · 09-01-2011, 07:08 AM

Bump No response

**jaslake** · 09-01-2011, 09:54 AM

Hi e4excel

When your code is done with the created names have the code delete them.

**royUK** · 09-01-2011, 02:47 PM

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

**jaslake** · 09-01-2011, 02:54 PM

@royUK

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

@e4excel

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

**e4excel** · 09-02-2011, 09:52 AM

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

**jaslake** · 09-04-2011, 06:28 PM

Hi e4excel

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

**e4excel** · 09-16-2011, 05:50 AM

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

**e4excel** · 09-16-2011, 06:28 AM

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?

**jaslake** · 09-17-2011, 07:13 PM

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.

**e4excel** · 09-18-2011, 06:15 AM

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

**jaslake** · 09-18-2011, 11:47 AM

Hi e4excel

Try this

Please Login or Register  to view this content.

**e4excel** · 09-18-2011, 04:03 PM

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

**jaslake** · 09-18-2011, 04:07 PM

Hi e4excel

Happy to be of help. Thanks for the kudos.

**e4excel** · 09-18-2011, 04:55 PM

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

**e4excel** · 09-20-2011, 04:19 AM

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

**jaslake** · 09-20-2011, 08:47 AM

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

**e4excel** · 09-21-2011, 02:18 AM

Thanks Jaslake,

Will be starting , New one then..!

Regrds

**e4excel** · 09-21-2011, 03:03 PM

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

Alternative for Defined Name Ranges created by a Macro?

LinkBack

Thread Tools

Rate This Thread

Display