Guess you don't understand that thisworkbook is a built in defined
"constant" that will refer to the workbook running the code no matter which
module it is used it - it can be used in all modules and all procedures. If
you want to duplicate that constant with another constant for whatever
reason, go ahead, but in my opinion your are duplicationg what already
exists and would cause none of the problems you are having.

Activeworkbook is another story, but we aren't talking about activeworkbook.

--
Regards,
Tom Ogilvy

"monir" wrote in message
...
Tom;

Since other work books may or may not be opened by other modules in the
same
work book, it is much safer to limit the use of ThisWorkbook.Name to this
module in this work book.

If you still want to use your variable, put your lines of code in the
original workbook_Open and delete the one that has only your lines of
code.

Just to make sure I understand your suggestion. Are you suggesting that I
put the statement:
....myOptFile = ThisWorkbook.Name
in the Private Sub Workbook_Open ()
and delete the Public Sub Workbook_Open () event ??

Keep in mind that at the module level there is the declaration:
....Public myOptFile As String

Thanks again.




"Tom Ogilvy" wrote:

Any where you can use myOptFile, use ThisWorkbook.Name instead and you
won't need myOptfile

There should be one procedure named Workbook_Open.

If you still want to use your variable, put your lines of code in the
original workbook_Open and delete the one that has only your lines of
code.

--
Regards,
Tom Ogilvy

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