0

In Visual Studio 2017 I'm creating a Windows Form Application with a Form1 where the user picks an opened Excel workbook to listen to its BeforeSave event.

Once that workbook is saved, it gets detected by the program which then colects some specific data from the sheets and opens Form2 with that data.

The issue I'm having is that when I load Form2 on the BeforeSave event, its controls (like Labels, buttons, textboxes, etc) don't show correctly. They appear like boxes with a background color.

Here's my code reduced and changed to the part that matters:

' Reference:
' Microsoft Excel 15.0 Object Library
Imports Microsoft.Office.Interop
Public Class Form1
    Private xlApp As Excel.Application
    Public Shared WithEvents xlBook As Excel.Workbook
    Private Shared Sub Workbook_BeforeSave(SaveAsUI As Boolean, ByRef Cancel As Boolean) Handles xlBook.BeforeSave
        Form2.Show()
        'Form1.Close()
    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try
            xlApp = Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")
            xlBook = xlApp.Workbooks(1)
        Catch
            MsgBox("Excel isn't opened.")
            Close()
        End Try
    End Sub
End Class

Edit: Using Application.Run(Form2) works (don't know why) but then I also can't close Form1 with Form1.Close().

41686d6564 stands w. Palestine
  • 19,168
  • 12
  • 41
  • 79
user7393973
  • 2,270
  • 1
  • 20
  • 58

1 Answers1

1

The BeforeSave event like the other Interop.Excel events is raised on a different thread. Therefore, you can't access the GUI thread directly from it. In order to access the GUI thread (e.g., access form controls, create a form on the main thread, etc.), you need to call the Invoke method with a delegate.

Replacing Form2.Show() with the following would solve the problem:

Invoke(Sub() Form2.Show())

However, you should create a new instance of the form that you want to load. So, instead of the previous line, you should use something like the following:

Private Sub Workbook_BeforeSave(SaveAsUI As Boolean,
                                ByRef Cancel As Boolean) Handles xlBook.BeforeSave
    Invoke(Sub()
               Dim frm As New Form2
               frm.Show()
           End Sub)
End Sub

To learn more about WinForms thread-safe calls, check the first article in the references.

References:

41686d6564 stands w. Palestine
  • 19,168
  • 12
  • 41
  • 79
  • Can I keep the `xlBook` `Shared`? I need to use it on the `Form2` class. – user7393973 May 21 '18 at 10:45
  • Sorry I didn't notice you were using a Shared variable. Actually, it doesn't have to be declared as Shared in order to access it from another form. You can call `frm.Show(me)` and then access the `xlBook` variable in Form2 using `DirectCast(Me.Owner, Form1).xlBook` or you can pass the variable to the created instance of Form2 by having another public field/property there. – 41686d6564 stands w. Palestine May 21 '18 at 10:53
  • Thanks, that should do it. – user7393973 May 21 '18 at 11:12