Passing variables between Workbooks

[Vetinari]

Hi All,

I'm writing some code in two workbooks. Both have code in the Workbook_Open event, but if I open one workbook with the other (i.e. not opening it directly from explorer etc.) I need to bypass the Workbook_Open event. EDIT: not bypass, as such, just make a decision based on shtNew whether to open a form or not.

I'm doing this by (trying to) pass a variable from Workbook A to workbook B like;

Workbook A

Sub Workbook_Open()
...
Workbooks.Open ("C:\Program Files\LS Sales Enquiry\Sales Enquiry.xls"), shtNew
...
End Sub

Workbook B

Private Sub Workbook_Open(shtNew As Boolean)
If shtNew = "" Then
UserForm1.Show
Else
If shtNew = False Then
If shtNew = True Then
UserForm1.Show
End If
End If
End If
End Sub

The variable is being passed, but I get an error with the procedure declaration line in Workbook B: "Compile Error: Procedure declaration does not match description of event or procedure having the same name".

I've searched this and other forums and tried some of the fixes suggested;
- delete code and recreate Workbook_Open event
- using and not using ByVal
but the problem remains. If I stop passing the variable, the error goes.

Can anyone help?

TIA

Jeff.

 

[SkipVought]

Hi,

Excel events are not such that you can arbitrarily add your own arguments.

Sub Workbook_Open()
dim wbB as workbook
...
set wbB = Workbooks.Open ("C:\Program Files\LS Sales Enquiry\Sales Enquiry.xls")

If shtNew = "" Then
    wbB.UserForm1.Show
Else
'[b][red]these next two statements are contradictory!!!
    If shtNew = False Then
        If shtNew = True Then[/red][/b]
            wbB.UserForm1.Show
        End If
    End If
End If

End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]