Excel VBA Names Collection

[TheFIDDLER]

Must be missing something simple here. Anyway, thought I would post it so someone can point me in the right direction.

I am trying to use the value of a name which is stored in workbook A from Workbook B. The name is called "debugmode" and stores a True/False value.

Sub dosomething()
If Workbooks("One").Names(debugmode).Name = True Then Cancel = True
End sub

I get a subscript out of range error.

And then I tried a variation:
If Workbooks("tbm_intro.xls").Names("debugmode") = True Then Cancel = True
And got a type mismatch error.

[BrianB]

Names are stored at 2 levels - WorkBook and Worksheet.

If set up manually, the ruling is that the first time a name is used it goes to Workbook level, if duplicated if goes to Worksheet level. This is controlled in VBA with Workbooks().Names.Add or Worksheets().Names Add. You should therefore probably be using something like :-

If Workbooks("tbm_intro.xls").WorkSheets("Sheet1").Names("debugmode") .Value = True ...............