Imports System.Windows.Forms
Imports Office = Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel
Imports MSForms = Microsoft.Vbe.Interop.Forms

' Office integration attribute. Identifies the startup class for the workbook. Do not modify.
<Assembly: System.ComponentModel.DescriptionAttribute("OfficeStartupClass, Version=1.0, Class=CustomerOrders.OfficeCodeBehind")>

Public Class OfficeCodeBehind 

    Friend WithEvents ThisWorkbook As Excel.Workbook
    Friend WithEvents ThisApplication As Excel.Application

#Region "Generated initialization code"

    ' Default constructor.
    Public Sub New()
    End Sub

    ' Required procedure. Do not modify.
    Public Sub _Startup(ByVal application As Object, ByVal workbook As Object)
        ThisApplication = CType(application, Excel.Application)
        ThisWorkbook = CType(workbook, Excel.Workbook)

    
    End Sub

    ' Required procedure. Do not modify.
    Public Sub _Shutdown()
        ThisApplication = Nothing
        ThisWorkbook = Nothing
    End Sub

    ' Returns the control with the specified name on ThisWorkbook's active worksheet.
    Overloads Function FindControl(ByVal name As String) As Object
        Return FindControl(name, CType(ThisWorkbook.ActiveSheet, Excel.Worksheet))
    End Function

    ' Returns the control with the specified name on the specified worksheet.
    Overloads Function FindControl(ByVal name As String, ByVal sheet As Excel.Worksheet) As Object
        Dim theObject As Excel.OLEObject
        Try
            theObject = CType(sheet.OLEObjects(name), Excel.OLEObject)
            Return theObject.Object
        Catch Ex As Exception
            ' Returns Nothing if the control is not found.
        End Try
        Return Nothing
    End Function
#End Region

    ' Called when the workbook is opened.
    Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
        Dim inputForm As GetCustomer = New GetCustomer(Me)
        inputForm.Show()
    End Sub

    ' Called before the workbook is closed. Note that this method
    ' might be called multiple times and the value assigned to Cancel
    ' might be ignored if other code or the user intervenes.
    ' Cancel is False when the event occurs. If the event procedure
    ' sets this to True, the document does not close when the procedure is finished.
    Private Sub ThisWorkbook_BeforeClose(ByRef Cancel As Boolean) Handles ThisWorkbook.BeforeClose
        Cancel = False
    End Sub

    Public Sub EchoStringToCell(ByVal str As String, ByVal cell() As Integer)
        Dim sheet1 As Excel.Worksheet = CType(Me.ThisApplication.Sheets.Item(1), Excel.Worksheet)
        Dim range As Excel.Range = CType(sheet1.Cells(cell(0), cell(1)), Excel.Range)
        range.Value2 = str
    End Sub

    Public Sub CreateChart(ByVal CustomerID As String, ByVal CompanyName As String, ByVal Year As Integer)
        'Now create the chart.
        Dim xlSheet As Excel.Worksheet = CType(Me.ThisApplication.Sheets.Item(1), Excel.Worksheet)
        Dim xlChart As Excel.Chart = CType(ThisWorkbook.Charts.Add(After:=xlSheet), Excel.Chart)
        Dim cellRange As Excel.Range = xlSheet.UsedRange

        xlChart.Name = CustomerID + "-" + Year.ToString()
        xlChart.ChartWizard(cellRange.CurrentRegion, Excel.Constants.xl3DBar, Type.Missing, Excel.XlRowCol.xlColumns, 1, Type.Missing, False, CompanyName + " Orders for " + Year.ToString(), Type.Missing, Type.Missing, Type.Missing)

        'Apply some formatting to the chart title.
        xlChart.ChartTitle.Font.Size = 16
        xlChart.ChartTitle.Shadow = True
        xlChart.ChartTitle.Border.LineStyle = Excel.Constants.xlSolid
    End Sub

End Class
