Add Names To Multiple Workbooks

[Mr Nick]

Is it possible to define names in multiple workbooks? I am currently defining a name in one workbook but a need has arisen for this name to also be defined in a second workbook (which is open but hidden in the background). I'm using the following statement to define the name - how do I extend it to define it in a second sheet?

If [b7] = "Turnip" Then ActiveWorkbook.Names.Add Name:="ValidData", RefersToR1C1:= _
"='[Carrots.xls]Parsnips'!R25C1:R50C1"

[Tim Francis-Wright]

On 2002-04-23 08:19, Mr Nick wrote:
Is it possible to define names in multiple workbooks? I am currently defining a name in one workbook but a need has arisen for this name to also be defined in a second workbook (which is open but hidden in the background). I'm using the following statement to define the name - how do I extend it to define it in a second sheet?

If [b7] = "Turnip" Then ActiveWorkbook.Names.Add Name:="ValidData", RefersToR1C1:= _
"='[Carrots.xls]Parsnips'!R25C1:R50C1"

You should be able to do this by referencing the other Workbook's object.

I've assumed here that you have defined OtherWorkbook elsewhere in the procedure
to refer to the hidden workbook.

If [b7] = "Turnip" Then
ActiveWorkbook.Names.Add Name:="ValidData", RefersToR1C1:= _
"='[Carrots.xls]Parsnips'!R25C1:R50C1"
OtherWorkbook.Names.Add Name:="ValidData", RefersToR1C1:= _
"='[Carrots.xls]Parsnips'!R25C1:R50C1"
End if

[Mr Nick]

What do I need to define OtherWorkbook as? String, Workbook, Boolean ......?

I'm still learning the ins and out so you'll have to excuse my ignorance.

Cheers.

[Mr Nick]

Can nobody help me out on this one?

[Tim Francis-Wright]

On 2002-04-23 09:50, Mr Nick wrote:
What do I need to define OtherWorkbook as? String, Workbook, Boolean ......?

I'm still learning the ins and out so you'll have to excuse my ignorance.

Cheers.

Sorry for the delay.
You can use

Dim Otherworkbook as Workbook

and then Set it in context, e.g.,
when you open the hidden workbook (and it's
active),

Set OtherWorkbook = ActiveWorkbook

(There are other, clever ways to do this, but this is easy and generally foolproof.)

HTH

[Mr Nick]

I've assumed here that you have defined OtherWorkbook elsewhere in the procedure
to refer to the hidden workbook.

If [b7] = "Turnip" Then
ActiveWorkbook.Names.Add Name:="ValidData", RefersToR1C1:= _
"='[Carrots.xls]Parsnips'!R25C1:R50C1"
OtherWorkbook.Names.Add Name:="ValidData", RefersToR1C1:= _
"='[Carrots.xls]Parsnips'!R25C1:R50C1"
End if

I can't seem to figure this one out.

If I want to add the name valid data to a workbook called Potato.xls, along with the above procedure, how do I define the operation.

Please help. I'm very stuck!!

[Tim Francis-Wright]

On 2002-04-23 13:07, Mr Nick wrote:

I can't seem to figure this one out.

If I want to add the name valid data to a workbook called Potato.xls, along with the above procedure, how do I define the operation.

Please help. I'm very stuck!!

My code assumed that the OtherWorkbook variable referenced Potato.xls (probably
right after the VBA opened that file).