Declaring Constants that can be used throughout the Workbook :)
 

Hiya Forumittes :)

I have put some standard code in "Workbook_open".
The code uses a "Const" (Constant) set-up in (General)(Declarations).

---- Code in General Declarations ----

Const Library_Name = "library.xla"

---- End Code ----

---- Code in Workbook_open ----

Application.Run(Library_Name & "!make_toast")

---- End Code ----


...So far so good...

But i put a button in Sheet 1, that needs to use the same "Const". But
"Sheet1" cant see the "Const" value set in "ThisWorkBook".

---- Code in Sheet 1 ----

Private Sub Testing_Button_Click()

MsgBox(Library_name)

End Sub

---- End Code ----

* so in summary, is there a way to set a value in "ThisWorkBook", used
in "Workbook_open" and through buttons on "Sheet1..2..3". Im hoping
along the lines of a Global/Public Constant.

....Dont ya just love the curly ones... ;)

eXcellence

Declaring Constants that can be used throughout the Workbook :)
 

Hi,

Public Const Library_Name = "library.xla"

HTH

"eXcellence" wrote:

Hiya Forumittes :)

I have put some standard code in "Workbook_open".
The code uses a "Const" (Constant) set-up in (General)(Declarations).

---- Code in General Declarations ----

Const Library_Name = "library.xla"

---- End Code ----

---- Code in Workbook_open ----

Application.Run(Library_Name & "!make_toast")

---- End Code ----


...So far so good...

But i put a button in Sheet 1, that needs to use the same "Const". But
"Sheet1" cant see the "Const" value set in "ThisWorkBook".

---- Code in Sheet 1 ----

Private Sub Testing_Button_Click()

MsgBox(Library_name)

End Sub

---- End Code ----

* so in summary, is there a way to set a value in "ThisWorkBook", used
in "Workbook_open" and through buttons on "Sheet1..2..3". Im hoping
along the lines of a Global/Public Constant.

....Dont ya just love the curly ones... ;)

eXcellence

Declaring Constants that can be used throughout the Workbook :)
 

If you put it in the ThisWorkbook module, you have to declare it as public
and to precede it with ThisWorkbook when being used in another module

Public Const Library_Name = "library.xla"

....

MsgBox(ThisWorkbook.Library_name)


as you would with any class property

--
HTH

Bob Phillips

"eXcellence" wrote in message
oups.com...
Hiya Forumittes :)

I have put some standard code in "Workbook_open".
The code uses a "Const" (Constant) set-up in (General)(Declarations).

---- Code in General Declarations ----

Const Library_Name = "library.xla"

---- End Code ----

---- Code in Workbook_open ----

Application.Run(Library_Name & "!make_toast")

---- End Code ----


..So far so good...

But i put a button in Sheet 1, that needs to use the same "Const". But
"Sheet1" cant see the "Const" value set in "ThisWorkBook".

---- Code in Sheet 1 ----

Private Sub Testing_Button_Click()

MsgBox(Library_name)

End Sub

---- End Code ----

* so in summary, is there a way to set a value in "ThisWorkBook", used
in "Workbook_open" and through buttons on "Sheet1..2..3". Im hoping
along the lines of a Global/Public Constant.

...Dont ya just love the curly ones... ;)

eXcellence

Declaring Constants that can be used throughout the Workbook :)
 

Put your constant declaration in a general module and it will be visible to
all the components in the workbook.

--
Regards,
Tom Ogilvy

"eXcellence" wrote in message
oups.com...
Hiya Forumittes :)

I have put some standard code in "Workbook_open".
The code uses a "Const" (Constant) set-up in (General)(Declarations).

---- Code in General Declarations ----

Const Library_Name = "library.xla"

---- End Code ----

---- Code in Workbook_open ----

Application.Run(Library_Name & "!make_toast")

---- End Code ----


..So far so good...

But i put a button in Sheet 1, that needs to use the same "Const". But
"Sheet1" cant see the "Const" value set in "ThisWorkBook".

---- Code in Sheet 1 ----

Private Sub Testing_Button_Click()

MsgBox(Library_name)

End Sub

---- End Code ----

* so in summary, is there a way to set a value in "ThisWorkBook", used
in "Workbook_open" and through buttons on "Sheet1..2..3". Im hoping
along the lines of a Global/Public Constant.

...Dont ya just love the curly ones... ;)

eXcellence

Declaring Constants that can be used throughout the Workbook :)
 

... im really starting to like this forum....

I worked this out, came back to the forum, and the answer was here..
Using Tom's suggestion.

----
I put all my code into "Module1". all the "Const" delcarations, all the
code from "Workbook_open" and the code from sheet1 for the button
"Testing_Button_Click".

In the call in "ThisWorkBook" "Workbook_Open", i put one line to call
the subroutine i copied the code into.... "Test_Button" (which is now
stored in the module).

So in summary, all code, and variables are in one module, its all
visible and can be called from anywhere.

....Many Thanks again !!... you cant see the huge smile on my face....
::))