Chip;
Thank you for your suggestion. In a regular module Declaration section, I
decla
....Public myOptFile As String
and I added in ThisWorkbook module:
.....Public Sub Workbook_Open()
.........myOptFile = ThisWorkbook.Name
.....End Sub
The following error appeares when I open the file:
"Compile error: Ambiguous name detected: workbook_open"
and Public Sub Workbook_Open() is highlighted.
"Chip Pearson" wrote:
Code must execute within a procedure -- code cannot exist outside
a procedure. You could declare a module level Public variable
(not a constant) and initialize its value in the workbook's Open
procedure.
'[in a regular code module]
Public myOptFile As String
'[in the ThisWorkbook code module]
Public Sub Workbook_Open()
myOptFile = ThisWorkbook.Name
End Sub
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"monir" wrote in message
...
Myrna::ross::Nigel::Gareth::Tushar;
Thank you all for your helpful responses. The modul-level
declaration:
....Const myOptFile As String = "Test.xls"
does solve the problem.
If I save the file as Test5.xls, then I've to (remember to)
change the
string in the module Declaratios section!!
Why can't I use instead:
....Const myOptFile As String = ThisWorkbook.Name
(Compile error: Constant expression required, and .Name is
highlighted)
Thanks again.