Tom;

Couple of points. First; my idea of using ThisWorkbook.Name is that I don't
have to change the myOptFile declaration each time I "Save As" the file.

Second; yes, there is a Private Sub Workbook_Open () event procedure in
ThisWorkbook module.

Chip in his response suggested to initialize the value of myOptFile in a
Public Sub Workbook_Open () event.
I can see clearly now the conflict between the Private and Public open
events!!

Perhaps, I should delete the Private Sub Workbook_Open () event and copy its
code to the Public Sub Workbook_Open () procedure. It might work, but it
might also cause a lot of headache !! The w/b has over 100 macros,
functions, events, private and public procedures, etc.

Is there an alternative to the event:
.....Public Sub Workbook_Open()
.........myOptFile = ThisWorkbook.Name
.....End Sub
so that I may leave everything else unchanged ?

Thank you.


"Tom Ogilvy" wrote:

That means you have two procedures nameed Workbook_Open in that module.
Normally, when you first open the thisworkbook module, excel creates a
workbook_Open event for you. Apparently you created your own after that.

Also, if your going to use ThisWorkbook.Name in a variable, why don't you
just skip the middleman and refer to it directly in your code.

--
Regards,
Tom Ogilvy


"monir" wrote in message
...
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.