Windows form under excel (VB.Net)

Add-in Express™ Support Service
That's what is more important than anything else

Windows form under excel (VB.Net)
How to make the windows form modular, and how to call the host application in the code 
Mourad Mnif




Posts: 8
 Joined: 2010-08-19
Hello add-in-express folks,
I have a windows form that shows after a button is pressed in an excel ribbon. This form will get some data from the user and then fill some cells in the active worksheet. I am actually stuck with coding the form. I have 2 questions:

1/ What should I do to make the form modular (disable any functionality in Excel as long as the form is active)?

2/ How can I call the current Excel application in the code of the form (something similar to Me.HostApplication which I m using in the AddinModule)?

Thanks in advance for your support
Posted 19 Aug, 2010 14:14:00 Top
Alexander Solomenko




Posts: 140
 Joined: 2009-02-27
Hi Mourad,

1. I am afraid, I may not understand the first question correctly. Could you give more details?
2. You can call the current Excel application using the property ExcelApp in the AddinModule.
Regards,
Aleksandr Solomenko
Posted 23 Aug, 2010 06:49:53 Top
Mourad Mnif




Posts: 8
 Joined: 2010-08-19
Hello Alexander

I created an ADX COM Add-in in Visual Studio 2010 where I built an excel ribbon with a button then I added a Windows Form as a new item to the project. So in the project solution I have a AddinModule.vb and WindowsForm.vb

In the AddinModule.vb code, I wrote the following code: 


Private Sub AdxRibbonButton1_OnClick(ByVal sender As System.Object, ByVal control As AddinExpress.MSO.IRibbonControl, ByVal pressed As System.Boolean) Handles AdxRibbonButton1.OnClick

     Dim MyForm As New WindowsForm
     MyForm.Show()

End Sub


and in WindowsForm.vb I entered: 


Private Sub DatesButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DatesButton.Click

        Dim ExcelApp As New Excel.Application
        Me.DatesBox.Text = ExcelApp.InputBox(Prompt:="Please enter the range of rates of return", Title:="Enter the range", Type:=8).Address(External:=True)

End Sub


The ExcelApp that I am using in the code of the form is a new Excel Application different fr om the inital host application (wh ere the button on the ribbon was pressed). If I was coding on the AddinModule.vb, I would have called it Me.Hostapplication, but I cannot do the same in the form.

Regarding the first question, I would like to disable the functionalities in Excel when the Windows Form is active, so that the user cannot make any changes on the workbooks. What should I do to make the Windows Form behave as such?
Thanks for the reply.

Mourad
Posted 23 Aug, 2010 09:16:37 Top
Alexander Solomenko




Posts: 140
 Joined: 2009-02-27
Hi Mourad,

Private Sub DatesButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DatesButton.Click
Dim ExcelApp As New Excel.Application
Me.DatesBox.Text = ExcelApp.InputBox(Prompt:=\"Please enter the range of rates of return\", Title:=\"Enter the range\", Type:=8).Address(External:=True)
End Sub


In the form you can use ExcelApp which you created.

Regarding the first question you should show the form as modal (MyForm.ShowDialog()).
Regards,
Aleksandr Solomenko
Posted 24 Aug, 2010 07:35:12 Top
Nicholas Hebb


Guest


I haven't had a chance to fully test this yet, but based on a support response, I created a static class like this: 


    public static class CurrentApp
    {
        public static Excel._Application XL
        {
            get
            {
                return (Excel._Application)((AddinModule)AddinExpress.MSO.ADXAddinModule.CurrentInstance).HostApplication;
            }
        }
    }


I believe the VB.NET equivalent would be: 


Public NotInheritable Class CurrentApp
	Private Sub New()
	End Sub
	Public Shared ReadOnly Property XL() As Excel._Application
		Get
			Return DirectCast(DirectCast(AddinExpress.MSO.ADXAddinModule.CurrentInstance, AddinModule).HostApplication, Excel._Application)
		End Get
	End Property
End Class


That way, I can call CurrentApp.XL from anywhere in the project to get the current instance of the host application.
Posted 24 Aug, 2010 07:53:28 Top
Mourad Mnif




Posts: 8
 Joined: 2010-08-19
Hello Alexander

1/ I wrote this code regarding the first question

Dim MyForm As New ReportingForm
MyForm.ShowDialog()

2/ I used the following code for the second question

Dim ExcelApp As Excel.Application = CType(Marshal.GetActiveObject("Excel.Application"), Excel.Application)

instead of Dim ExcelApp As New Excel.Application

Everything is working fine. Many thanks for your help.
Mourad
Posted 24 Aug, 2010 07:54:29 Top
Mourad Mnif




Posts: 8
 Joined: 2010-08-19
Hi Nicholas

I tried with your suggestion and it also works fine:

Dim ExcelApp As Excel.Application = DirectCast(DirectCast(AddinExpress.MSO.ADXAddinModule.CurrentInstance, AddinModule).HostApplication, Excel._Application)

Thanks for your support.
Mourad
Posted 24 Aug, 2010 08:02:04 Top