CODEWITHSUNDEEP
vbaexcel
Elias Gröndal
Elias Gröndal

Reputation: 31

vba Macro to open other WB and execute Macro fails when other WB have userform show on opening

As topic implies I have a problem I cannot find any solution to.

I have a Workbook (1) with the purpose to open other WBs and run macros in them. Everything works like a charm except when the other WB has Workbook_Open() event to open a Userform (typically it asks if the WB should be updated). Then I get error code 1004 and my code fails.

How could I supress the Workbook_Open event from triggering when I open another WB?

I have tried the setting Application.EnableEvents = False but it´s not related.

Thank you very much for any help on this topic!

Here is the code for opening the WB

Public Function wbTargetOpen(sTargetPath As String, SPassword As String) As Workbook

Dim sWBName As String

sWBName = Mid(sTargetPath, InStrRev(sTargetPath, "\") + 1, Len(sTargetPath) - InStrRev(sTargetPath, "\") + 1)

If WorkbookIsOpen(sWBName) Then

    Set wbTargetOpen = Workbooks(sWBName)

    If wbTargetOpen.ReadOnly = True Then
        wbTargetOpen.Close
        Set wbTargetOpen = Workbooks.Open(FileName:=sTargetPath, UpdateLinks:=0, ReadOnly:=False, WriteResPassword:=SPassword)
    End If

Else
    Set wbTargetOpen = Workbooks.Open(FileName:=sTargetPath, UpdateLinks:=0, ReadOnly:=False, WriteResPassword:=SPassword)
End If

If wbTargetOpen.ReadOnly Then sErrorCode = "ReadOnly"

End Function

Upvotes: 0

Views: 167

Answers (2)

user6432984
user6432984

Reputation:

Close the other userforms before you run the macros.

Sub CloseOtherUserForms()
    Dim frm As UserForm
    For Each frm In UserForms
        If Not TypeName(frm) = "MacroForm" Then
            Unload frm
        End If
    Next
End Sub

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149295

All you have to do is add one word VbModeless to the other workbook which launches the userform.

Private Sub Workbook_Open()
    UserForm1.Show vbModeless
End Sub

The vbModeless will launch the form but will also allow your macro to run.

Upvotes: 3

Related Questions