VBA defined named range not appearing in Names list...

[George]

Hi guys, I've looked around at CPearson, OZGrid and Debra's (the name of
which escapes me atm) but I haven't so far found a solution to my problem.

I'm pulling in data from another workbook and then defining named ranges
based on the updated data. This is all working fine as are the lines that
work out the name of the range and the address of the range. Here's the code
I'm using...

dynRangeName = Cells(thisRow, thisCol).Value & depts(thisCol) & "Testers"
Set dynamicRange = Range(Cells(startRow, thisCol + 1), Cells(thisRow,
thisCol + 1))
Debug.Print dynRangeName & " = " & dynamicRange.Address
ThisWorkbook.Names.Add dynRangeName, dynamicRange

The Debug.Print line outputs lines such as these (which are the correct
names and the correct ranges):

ESFncTesters = $B$2:$B$61
LSFncTesters = $B$62:$B$123
NSFncTesters = $B$124:$B$174

My only problem now is that these named ranges that I've added using
"ThisWorkbook.Names.Add" aren't displaying in the Names list (through Insert

Names > Define...) and as I'm using these NRs for Data Validation, the DV

doesn't work :-(

Any ideas? I've tried using "Visible:True" on the creation of the names with
no luck. If I create all these names with "rubbish" ranges through the menu
system in Excel when VBA overwrites these names it works, but I didn't really
want to go down this route if I can help it...I don't want to have to rely on
me (or other users) inserting names so the VBA can add them correctly...

Thanks as usual - this forum's been a superb resource for me. Top work you
guys!
George

 

[jhyatt]

check in file > properties > contents tab the named ranges i create with code
are usually there.

 

[George]

Thanks JHyatt - useful tip that

I can now see the named ranges are defined as

Data!ESFncTesters (with the sheet name before it)

How can I set a Named Range from VBA without this reference so that it's
usable in DV across all worksheets in the workbook?

 

[jhyatt]

i am no expert but maybe try.

1. On the Insert menu, point to Name, and then click Define.
2. In the Names in workbook box, type the name.
3. If the Refers to box contains a reference, select the equal sign (=) and
the reference and press BACKSPACE.
4. In the Refers to box, type = (equal sign).
5. Click the tab for the first worksheet to be referenced.
6. Hold down SHIFT and click the tab for the last worksheet to be referenced.
7. Select the cell or range of cells to be referenced.

 

[George]

That would probably work however I don't want to go down that route if
possible as when I need to add more ranges I (or a user) would need to add
the next named range to get it to work. I was hoping there would be a way to
create a completely new Named Range using VBA and have it accessible all
across the workbook in Excel without me needing to define it using the menu
system.

I'll keep looking around to see if there's anything I can find that will
help me out with this - in the short term however it looks like I'm going to
have to add them using "Insert > Names > Define" to make sure they
work...bummer :-(

Thanks for the earlier tip though JH!

 

[jhyatt]

have you tried recording a macro using this method to ficgure the code out
and then adjust it accordingly.

 

[jhyatt]

something like this

ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:= _
"=Sheet1:Sheet3!R1C1:R16C1"

 

[George]

Aha - through a combination of recording the macro (always a useful process)
and your code here I think I've worked it out. I suspect it's because I
haven't defined the sheet name in the reference so when Excel adds the name
it automatically defines it with "Sheet!" so the cell references work
properly.

It could also be the "ActiveWorkbook" whereas I'm using "ThisWorkbook" but
I'll try them out tomorrow now and get back to you...thanks for the help on
this JH, always nice to bounce ideas around and get to the bottom of the
problem.

 

[jhyatt]

anytime

 

[George]

Hmmm.spoke too soon Thought I'd give it one last try before heading home...

This code works in that it defines the named range and it shows in the names
list window (meaning it should be available for DV in other sheets):

dynRangeName = Cells(thisRow, thisCol).Value & depts(thisCol) & "Testers"
dynamicRange = "Data!" & Range(Cells(startRow, thisCol + 1), Cells(thisRow,
thisCol + 1)).Address
Debug.Print "dynamic range is " & dynamicRange
ThisWorkbook.Names.Add Name:=dynRangeName, RefersToR1C1:=dynamicRange

The only problem now is that my named range is defined (when viewed in the
named range list) as the string :-(

="Data!$B$2:$B$61"

I need it without the quotes

I'm definitely heading out of the office now but will think about this
tomorrow morning. If there's any more help to be had on this I'll gladly take
it as well

 

[jhyatt]

it looks to me like the second half of the line is only refering to the
dynamic range you set.

dynamicRange = "Data!" & Range(Cells(startRow, thisCol + 1), Cells(thisRow,
thisCol + 1)).Address

if you want it active on all sheets i think you need to declare those
sheets. the dynamic range you have set does not do that.

ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:=_
"=Sheet1:Sheet3!R1C1:R16C1" 'put the sheets you want here

 

[George]

JHyatt - a misunderstanding has happened here I think. The named range is
only defined against one sheet and I want to use that named ranges on other
sheets in the workbook. I'm going to be playing around with the workbook
today to see if I can get some resolution...thanks for the help on this
though.

Thanks

 

[George]

Sorry about the earlier post, was in a hurry...i'll calrify what I mean. When
I add a named range through the menu system in Excel it defines the named
range as "Sheet!Reference" and it's available on all sheets in the workbook.
I think that's where my setup of the named range is going wrong as I'm not
referencing the sheet name in the range. Hopefully if I specify the sheet
name in the range then it will be defined to all other sheets in the
workbook. Hopefully.

Anyway will be playing around with it again today to see if I can crack this

 

[George]

Riiiiiiiiight...got it sorted now

Because I'd "activated" the data sheet I was working on, despite me
specifying to add the name to ThisWorkbook VBA added it with the worksheet
name added on to the front.

By doing this instead:

dataSheet.Parent.Names.Add <name>, <range>

Excel has now added the named range into the "proper" list and it's
accessible everywhere in the workbook!

Yay!

Thanks to JH for all the help...worked it out in the end but some very
useful hints from him helped me out!