Visual Studio: Basic Excel WorkBook + UI Forms.

December 2016


In this [How-To] we will:
  • Create a new Excel Workbook Project.
  • Populate a user form with win-Form-Excel-interactive controls, and show it at start-up.
  • Create a Ribbon with a button.
  • Code the controls to interact with excel cells object.
  • Finally publish the workbook.

For the purpose of this tutorial we're using Microsoft Visual Studio 2013.
You can download the (free for individual developers version)
Visual Studio Community 2013 From Here
you will need the Office development Tools for visual studio as well: Read More Here

For Testing the Project at anytime hit F5 from the Keyboard.

Creating the project.





*
    • Fill the Excel sheet Column A something like this:

Adding Win Form and Ribbon:

  • For adding a win Form and populating controls to it, see:

Visual Studio: Your first windows forms application.
the Form should look like this:

we'll write a code that will look in the Excel sheet for non empty cells in column A and add the values of those cells to the ComboBox we've added to the From.
the TextBox will be used to enter a value which will replace the value of the selected cell by pressing the button.
the font button will show a font dialog to manipulate the font of the selected cell.
  • Now to add the Ribbon, go to Solution explorer right click on the project choose Add -> New Item:


from the dialog choose:
  • we'll add a single button from the ToolBox to the new Ribbon:

Coding Ribbon button:

  • Double click on the button we've added to the ribbon, and add this code

the code will show the form we've created as a dialog box:
Private Sub Button1_Click(sender As Object, e As RibbonControlEventArgs) Handles Button1.Click
        Dim a As Windows.Forms.Form = New Global.ExcelWorkbook1.Form1
        a.ShowDialog()
    End Sub

Form1 Codes

the Codes will include
  • initiating the form and adding items to the ComboBox.
  • Getting the ComboBox to select cells according to it's selected item.
  • Coding the Change Value Button to change the value of the selected cell.
  • Coding the Font Dialog.

Combo Box

  • From the Solution Explorer Right Click on the Form1.vb and choose View Code, or simply select it from the solution explorer and hit F7,

Now the following code will create a Private Sub and when ever we call it, it will clear the ComboBox and populate it's items;
Private Sub combo()
        ComboBox1.Items.Clear()
        Dim i As Integer = 1, ws As Excel.Worksheet
        ws = Globals.ThisWorkbook.Sheets("sheet1")
        Dim j = ws.Cells(ws.Rows.Count, 1).End(Excel.XlDirection.xlUp).Row
        Do Until i = j + 1
            If ws.Cells(i, 1).value <> Nothing Then
                ComboBox1.Items.Add(ws.Cells(i, 1).value.ToString)
            End If
            i = i + 1
        Loop
        ComboBox1.SelectedItem = ComboBox1.Items.Item(0)
    End Sub
  • Now we'll handle the Form1-Shows event;




*
    • and call the combo sub

Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        combo()
End Sub
  • Now if you hit F5, the excel workbook will be shown, go to the Add-in tab hit the button and Form1 will be shown with the ComboBox added items:



*
      • The code to select Cells from ComboBox

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim i As Integer = 1, ws As Excel.Worksheet
        ws = Globals.ThisWorkbook.Sheets("sheet1")
        Dim j = ws.Cells(ws.Rows.Count, 1).End(Excel.XlDirection.xlUp).Row
        Do Until i = j + 1
            If ws.Cells(i, 1).value = ComboBox1.SelectedItem.ToString Then
                ws.Cells(i, 1).select()
                Exit Do
            End If
            i = i + 1
        Loop
End Sub

Set Value Button Code

  • In the Solution Explorer Double Click on Form1, then Double Click on the button to go to the code area, and write this code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim m As Excel.Range = Globals.Sheet1.Cells(1, 1)
        Dim i As Integer = 1, ws As Excel.Worksheet
        ws = Globals.ThisWorkbook.Sheets("sheet1")
        Dim j = ws.Cells(ws.Rows.Count, 1).End(Excel.XlDirection.xlUp).Row
        Do Until i = j + 1
            If ws.Cells(i, 1).value = ComboBox1.SelectedItem.ToString Then
                If Not TextBox1.Text = "" Then
                    ws.Cells(i, 1).value = TextBox1.Text
                    combo()
                Else
                    MessageBox.Show("Please Enter a non-blank Value")
                End If
                Exit Do
            End If
            i = i + 1
        Loop
 End Sub

Font Dialog Code

  • Double Click on the Font Button to go to Code area;

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim f As New FontDialog, ws As Excel.Worksheet
        ws = Globals.ThisWorkbook.Sheets("sheet1")
        Dim Cell As Excel.Range = ws.Application.ActiveCell
        f.ShowColor = True
        If f.ShowDialog() <> Windows.Forms.DialogResult.Cancel Then
            With Cell
                .Font.Name = f.Font.Name
                .Font.Size = f.Font.Size
                .Font.Color = f.Color
                .Font.Italic = f.Font.Italic
                .Font.Bold = f.Font.Bold
                .Font.Underline = f.Font.Underline
                .Font.Strikethrough = f.Font.Strikeout
            End With
        End If
    End Sub

Publishing the Poject

Please visit this link:
Visual Studio: Your first windows forms application.

Related :

This document entitled « Visual Studio: Basic Excel WorkBook + UI Forms. » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.