CODEWITHSUNDEEP
vbaexcelms-word
Niwith
Niwith

Reputation: 23

Bypass workbook_open when opening from word

I am working on a project that requires both an excel document and a word document. I have fully programmed the excel document to work using UserForms, one of which opens automatically when opening the document. (see code below)

Private Sub Workbook_Open()
    frmOpen.Show
End Sub

The word document has been programmed to read data from this excel document and write it into a report. (see code below)

Private Sub cmdAutomatic_Click()
    Dim objExcel As New Excel.Application
    Dim exWb As Excel.Workbook
    Dim selectID As String


    Set exWb =objExcel.Workbooks.Open("C:\ Path")
    exWb.Close

    ''Data is written into the document here

    Set exWb = Nothing
    Unload Me
End Sub

The issue is that every time this button is pressed it opens the user form from the excel document and halts the other code until the user form is closed. Is there a way to only have the User Form open when it.

I have tried

Application.EnableEvents = False

However this just returns a method or data member not found (so I assume that this has to be run excel to excel?)

Sorry if this question has already been answered, I could not find anything that addressed this issue. Also sorry if this has a really simple solution, this is for a school project and this is my first time using VBA

Edit: I realized that doing the following might work

exWb.Application.EnableEvents = False

However because I need to put this before the "Set" for it to stop the form from opening, it doesnt work (as the object is not set at the point the line is run).

Upvotes: 1

Views: 311

Answers (1)

R3uK
R3uK

Reputation: 14547

You can disable Excel events with objExcel.EnableEvents = False before opening the workbook and reactivate them afterwards with objExcel.EnableEvents = True.

And as @Siddarth Rout told in comments, you can show your UserForm in Modeless mode with frmOpen.Show vbmodeless to avoid blocking other code execution. See MSDN remarks about this : https://msdn.microsoft.com/en-us/library/office/gg251819.aspx

So your code will look like this :

Private Sub cmdAutomatic_Click()
    Dim objExcel As New Excel.Application
    Dim exWb As Excel.Workbook
    Dim selectID As String

    objExcel.EnableEvents = False
    Set exWb =objExcel.Workbooks.Open("C:\ Path")

exWb.Close
    objExcel.EnableEvents = True
    ''Data is written into the document here

    Set exWb = Nothing
    Unload Me
End Sub

Upvotes: 1

Related Questions