Listing 1
Imports Microsoft.Office.Tools.Ribbon
Public Class Ribbon
Private Sub Ribbon_Load(ByVal sender As System.Object, ByVal e As RibbonUIEventArgs) Handles MyBase.Load
End Sub
Private Sub chkChinook_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) _
Handles chkChinook.Click
Call UpdatePeriodDropDown()
End Sub
Private Sub chkNorthwind_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) _
Handles chkNorthwind.Click
Call UpdatePeriodDropDown()
End Sub
Private Sub UpdatePeriodDropDown()
Dim periods As List(Of String) =
Utility.UpdateAccountingPeriods(chkChinook.Checked, chkNorthwind.Checked)
Me.ddlPeriod.Items.Clear()
If (Not IsNothing(periods) AndAlso periods.Count > 0) Then
For index = 0 To periods.Count - 1
Dim entry = Factory.CreateRibbonDropDownItem
entry.Label = periods(index)
Me.ddlPeriod.Items.Add(entry)
Next
End If
End Sub
Private Sub btnLoadData_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) _
Handles btnLoadData.Click
Dim AccountingPeriod = ddlPeriod.SelectedItem.Label
WriteConsolidatedSheet(AccountingPeriod)
End Sub
Private Sub btnTestAddWorkSheet_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles btnTestAddWorkSheet.Click
Dim wsUtility As New Utility
Dim wsNextSheetNumber = Globals.ThisWorkbook.Worksheets.Count + 1
wsUtility.AddWorksheet(String.Format("AfterSheet{0}", wsNextSheetNumber), True)
End Sub
Private Sub btnTestWorksheetNoName_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles btnTestWorksheetNoName.Click
Dim wsUtility As New Utility
Dim wsNextSheetNumber = Globals.ThisWorkbook.Worksheets.Count + 1
wsUtility.AddWorksheet(String.Format("BeforeSheet{0}", wsNextSheetNumber), False)
End Sub
' FillWorkSheetWithTable("Ref_Rows", Properties.Settings.Default.Ref_Rows, "", "");
Private Sub btnChinookDetailForPeriod_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles btnChinookDetailForPeriod.Click
Dim AccountingPeriod = txtPeriod.Text
Dim wsUtility As New Utility
Dim wsName = String.Format("Chinook Invoices {0}", AccountingPeriod)
wsUtility.AddWorksheet(wsName, True)
wsUtility.AddChinookInvoicesForPeriod(wsName, AccountingPeriod)
End Sub
Private Sub ddlTestLocateLabel_SelectionChanged(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles ddlTestLocateLabel.SelectionChanged
Dim LabelText = ddlTestLocateLabel.SelectedItem.Label
Dim wsUtility As New Utility
Dim row = wsUtility.FindFirstOccuranceRow(1, LabelText)
MessageBox.Show(String.Format("Text '{0}' found in row {1}", LabelText, row))
End Sub
Public Sub WriteConsolidatedSheet(AccountingPeriod As String)
Dim wsUtility As New Utility
wsUtility.WriteValueToSheet("Period", 3, AccountingPeriod)
'MessageBox.Show(String.Format("Load Data Clicked for period {0}", AccountingPeriod))
Dim ChinookTotalSales = Data.Chinook.Data.TotalSalesForPeriod(AccountingPeriod)
wsUtility.WriteValueToSheet("ChinookSales", 3, ChinookTotalSales.ToString)
'MessageBox.Show("Total Sales Retrieved For Chinook")
Dim NorthwindTotalSales = Data.Northwind.Data.TotalSalesForPeriod(AccountingPeriod)
wsUtility.WriteValueToSheet("NorthwindSales", 3, NorthwindTotalSales.ToString)
'MessageBox.Show("Total Sales Retrieved For Northwind")
Dim TotalSales = ChinookTotalSales + NorthwindTotalSales
wsUtility.WriteValueToSheet("CombinedSales", 3, TotalSales.ToString)
Dim LargestSale = "Chinook"
If NorthwindTotalSales > ChinookTotalSales Then LargestSale = "Northwind"
Dim ConsolidatedDetail = wsUtility.ConsolidateDetail(AccountingPeriod)
Dim BoldRow = 0
For index = 0 To ConsolidatedDetail.Count - 1
Dim row As Integer = 0
If index = 0 Then
row = wsUtility.FindFirstOccuranceRow(1, "FormatRow")
ElseIf index = ConsolidatedDetail.Count - 1 Then
row = wsUtility.FindFirstOccuranceRow(1, "DataRow")
Else
row = wsUtility.FindFirstOccuranceRow(1, "DataRow")
wsUtility.InsertRowActiveSheet(row)
'MessageBox.Show("Inserted Row")
wsUtility.CopyRowActiveSheet(row + 1, row, False)
'MessageBox.Show("Copied Row")
End If
'Should the line be bold due to highest sales for the month?
If ConsolidatedDetail(index).LargestSale Then BoldRow = row
wsUtility.WriteValueToSheet(row, 1, "")
wsUtility.WriteValueToSheet(row, 2, ConsolidatedDetail(index).Company)
wsUtility.WriteValueToSheet(row, 3, (index + 1).ToString)
wsUtility.WriteValueToSheet(row, 4, ConsolidatedDetail(index).ItemDate.ToShortDateString)
wsUtility.WriteValueToSheet(row, 5, ConsolidatedDetail(index).CustomerName)
wsUtility.WriteValueToSheet(row, 6, ConsolidatedDetail(index).Amount.ToString)
wsUtility.WriteValueToSheet(row, 8, ConsolidatedDetail(index).SalesToDate.ToString)
row += 1
'MessageBox.Show("Row Completed")
Next
'Set Largest Sale Row bold
If BoldRow > 0 Then wsUtility.SetRowBold(BoldRow)
'Hide Column A
wsUtility.HideColumn(1)
'Add a new worksheet to hold Chinook Invoices detail
wsUtility.AddWorksheet("Chinook Detail", True)
wsUtility.AddChinookInvoicesForPeriod("Chinook Detail", AccountingPeriod)
End Sub
Private Sub btnWorksheetSize_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles btnWorksheetSize.Click
Dim wsUtility As New Utility
Dim wsSize = wsUtility.GetActiveWorksheetDimensions()
MessageBox.Show(String.Format("Active WorkSheet Size = {0} x {1}", wsSize.LastRow, wsSize.LastColumn))
End Sub
Private Sub txtColumnNumber_TextChanged(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles txtColumnNumber.TextChanged
Dim colNum = txtColumnNumber.Text
Dim wsUtility As New Utility
Dim colLetter = wsUtility.GetColumnLetter(Integer.Parse(colNum))
MessageBox.Show(String.Format("Column Letter for {0} is {1}", colNum, colLetter))
End Sub
Private Sub btnLargestOrderForPeriod_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles btnLargestOrderForPeriod.Click
Dim AccountingPeriod = txtPeriod.Text
Dim LargestChinook = Data.Chinook.Data.LargestSaleForPeriod(AccountingPeriod)
Dim msg1 = String.Format("Largest Invoice for Chinook in period {0} is InvoiceID {1}", AccountingPeriod, LargestChinook)
MessageBox.Show(msg1)
Dim LargestNorthwind = Data.Northwind.Data.LargestSaleForPeriod(AccountingPeriod)
Dim msg2 = String.Format("Largest Order for Chinook in period {0} is OrderID {1}", AccountingPeriod, LargestNorthwind)
MessageBox.Show(msg2)
End Sub
End Class