Declare Variable

[zapatista66]

Just to be sure I'm right:

First I write:

Option Explicit
Global gwksZap As Worksheet
Sub SetRefs()
Set gwksZap = ThisWorkbook.Worksheets("Zapatista66")
End Sub

in a module or in each module I need those variables ?

After:

Private Sub Workbook_Open()
Call SetRefs
gwksZap.Activate
End Sub

Finally:

All my declaration in the worksheet (Microsoft Excel Objet) like that:

Set wbBook1 = Workbooks("Logiciel_Production_Soumission.xls")
Set wbBook2 = Workbooks("Projets.xls")
Set wbBook3 = Workbooks("Liste_Prix.xls")
Set wbBook4 = Workbooks("Clients.xls")
Set wbBook5 = Workbooks("Template_Soumission.xls")

thanks for helping m

 

[Geof Wyght]

In a "pure" code module (not worksheet module):
Option Explicit
Dim wbBook1 as Workbook
etc ...
Public Sub SetRefs()
Set wbBook1 = Workbooks
("Logiciel_Production_Soumission.xls")
etc ...
End Sub

Then, in any other worksheet module or code module, you
just have to refer to wbBook1. You don't have to make
another Set statement again. For example:
Private Sub Workbook_Open()
Call SetRefs()
wbBook1.Activate
End Sub

Geof.

 

[Geof Wyght]

I forgot to mention that if you call SetRefs in the
workbook open event, you shouldn't have to call SetRefs
again. But if you ever have a runtime error that's not
trapped, references to these objects are destroyed, and
somehow you have to re-run SetRefs.
Geof.