Solved: Transfer Variable Wbk to Wbk?

[omnibuster]

Hi.
If Wbk2(Two.xls) open with macro SubOne (in Wbk CallTwo....xls) how disable/enable run WbkOpenEvents?
:banghead: What I do wrong?


Code in Wbk CallTwo..
Public Allow As Boolean
Sub One()
Allow = True
Call Failikontr
End Sub
Sub Failikontr()
Dim FilePath, FName As String, WB As Workbook

If IsWkbOpened("Two.xls") Then
Windows("Two.xls").Activate
Else
FilePath = ActiveWorkbook.Path
If Right$(FilePath, 1) <> "\" Then FilePath = FilePath & "\"
FName = "Two.xls"
Set WB = Workbooks.Open(FilePath & FName, UpdateLinks:=0)
End If
End Sub

Function IsWkbOpened(sWkbName As String) As Boolean
Dim i As Long
For i = Workbooks.Count To 1 Step -1
If Workbooks(i).Name = sWkbName Then
Exit For
End If
Next

If i = 0 Then
IsWkbOpened = False
Else
IsWkbOpened = True
End If
End Function



Code in Wbk Two.
Public Allow As Boolean
Public Sub Workbook_Open()
If Allow = False Then
'Disable Wbk open event
Exit Sub

Else
Call PostMsg(Allow)
'Run Wbk_Open_Event code only if Allow = true
Call PostMsg(Allow)
End If
End Sub

Public Function PostMsg(Allow As Boolean)
MsgBox Allow & vbCrLf & vbCrLf & "Should be True????"
End Function

[Jan Karel Pieterse]

Put

Application.EnableEvents=False

just before opening the file.
Remember to turn it back on afterwards.

NB. Your declaration is wrong. This line:

Dim FilePath, FName As String

declares FilePath as a variant, not as a string. The variable type MUST be set for each variable, so the proper syntax is:

Dim FilePath As String, FName As String

I do not like declaring multiple variables on a single line of code, doing that makes it harder to find them. (just my opinion!)

[omnibuster]

Thanks for response Jan.

NB. Your declaration is wrong. This line:
Dim FilePath, FName As String


My mistake.Sry.


Application.EnableEvents=False
just before opening the file.
Remember to turn it back on afterwards.


Sounds I´m explaned not enought.
If I open WbkTwo its WbkOpenEvent should disabled only when Wbk was opened with code CommandButton1_Click eg. if Switch=True then exit Sub.
Some reason MsgBox Switch says allways False???
How this turn to true??


Public Switch As Boolean
Private Sub CommandButton1_Click()
Switch = True
Call Failikontr
End Sub
Sub Failikontr()
Dim FilePath As String, FName As String, WB As Workbook

If IsWkbOpened("Two.xls") Then
Windows("Two.xls").Activate
Else
FilePath = ActiveWorkbook.Path
If Right$(FilePath, 1) <> "\" Then FilePath = FilePath & "\"
FName = "Two.xls"
Set WB = Workbooks.Open(FilePath & FName, UpdateLinks:=0)
End If
End Sub

Function IsWkbOpened(sWkbName As String) As Boolean
Dim i As Long
For i = Workbooks.Count To 1 Step -1
If Workbooks(i).Name = sWkbName Then
Exit For
End If
Next

If i = 0 Then
IsWkbOpened = False
Else
IsWkbOpened = True
End If
End Function


Public Switch As Boolean
Public Sub Workbook_Open()
If Switch = True Then
'How get Switch to TRUE?
MsgBox Switch
Exit Sub
Else
MsgBox Switch
End If
End Sub

[Jan Karel Pieterse]

The Switch variable in the other workbook cannot be changed BEFORE workbook_Open has run, so will never have an effect.
However, you could use the Switch variable inside the code that opens the workbook:
'Turn off events if switch is true
Application.EnableEvents = Not Switch
'Workbooks.Open goes here
Application.EnableEvents=True

[omnibuster]

Big Thanks Jan.
Works good.