Sending a variable to another workbook?

[Gustaf]

I've inherited a pretty extensive VBA app in Excel 2003. It uses a "router" workbook to open alternative versions of a workbook containing the code. Now there's a need to add a custom Help menu, and the links/paths used in that menu should be sent from the router workbook, while the menus themselves are constructed in the code workbook. The router file creates a Workbook object of the code file, but I haven't figured out a way to use that to transfer the data. Any ideas?

Gustaf

 

[paulgu]

Hi Gustaf,
Why not use a worksheet in the "router" workbook? If you don't want it to be visible to others then hide it. Worksheets are for holding data after all.
HTH
Paul

 

[Bernie Deitrick]

Gustaf,

Of course, the code that you would need depends on where you are saving the data the needs to
accessed, but usually values are stored in cells:

Dim myB As Workbook
Dim myS As Worksheet
Dim myV As String

Set myB = Workbooks("CodeFile.xls")
Set myS = myB.Sheets("Storage Values")
myV = myS.Range("A1").Value


HTH,
Bernie
MS Excel MVP

 

[Gustaf]

Of course, the code that you would need depends on where you are saving the data the needs to
accessed, but usually values are stored in cells:

Thank you. I've considered this solution, but the data is not really meant to be displayed in Excel, so it's not ideal. What I'm looking for is a way of changing the value of global variables inside workbook, and use that when building the menus. But thank you anyway. It may come in use.

Gustaf

 

[Bernie Deitrick]

Gustaf,

Can you describe how you would find the value for your global variable?

What I'm looking for is a way of changing the value of global variables inside workbook, and use
that when building the menus.

If you can put it into words, then we can put it into code.

HTH,
Bernie
MS Excel MVP