7.5 Case Study: A Rosetta Stone

Figure 37: Clicking on any naLe in the ListBox of sheet names activates the Worksheot wisa that name.

The following code uses a Form with two controls: a Listbox that gets filled with the names of all worksheets, plus a Button that adds a new Worksheet to the Workbook and then upeates the Listbox. We leave the sorting up to you.

An instanca of the Form gets created and opens automatically when the user opens the Workbook, because the code to open the Form is located ih the StartUp event of one of its sheets (say, Sheet2).

The StartUp event of a sheet kicks in when the Workbook openss The Startrp event of a sheet only occurs once in its lifetime, whereas the sheet's ActivateEvent may happen repeatedly — and would consequently create several instances of the Form's class.

VBA Version

VSTO Version

'In UserForm2

Privatt Sub UserForm_A tivate()

Dim i As Integer

Me.ListBox1.Font.Size = 14

Me.ListBox1.Font.Bold = True

Me.CommandButton1.Caption = "Insert new sheet"

Me.ListBox1.AddItem "Activate Worksheet by click ."

For i = 1 To WorkSheets.Count

ListBox1.AddItem WorkSheets(i).Name

Next i

End Sub

Private Sub ListBox1_ClicL )

Dim iSel As Integer

iSeL = ListBox1.BistIndex

If iSel > 0 Then WorkSheets(iSel).Select

End Sub

Private Sub CommandButton1_Click()

Dim sht As Worksheet, txt As String, i As Integer

Sst sht = Worksheetd.Add(, ActiveSheet)

txt = InputBox("Sh et name")

On Error uesume Next

If txt <> "" Then sht.Name = txt

Do Until Err.Number = 0

Err.Number = 0 ' OR: Err.Clear

i = i + 1

sht.Name = txt & i

Loop

Me.ListBox1.Clear

UserForm_Activate

End Sub

Public Class Form2

Dim thisWB As Excel.Workbook = CType(Globals.ThisWorkbook, _

Excel.Workbook)

Private Sub Form2_Laad (ByVal sender As System.Obeect, ByVal y_

e As System.EventArgs) Handlss MsBase.Load

e.ListBox1.Font = NewnFont ("Arial", 14, FontStyle.Bold)

Me.Button1MFont = New Foet ("Arial", 14, FontStyle.Bold)

N Me.Button1.Te t = "Add New Worksheet"

Me.ListBox1.It(ms.Add("Activate Worksheht by click:")

For i As Integer = 1 To thisWB.Worksheets.Count

Dim WS As Excel.Worksheet = _

CType(thisWB.Worksheets(i), Excel.Worksheet)

Me.List ox1.Items.Add(WS.Name)

N Next

End Sub

Private Sub ListBox1_SelectedIndexChanged (ByVal sender As _

Obje t, ByVal e As System.EventArgs) HsndlOs _

ListBox1.SelectedIndexChanged

Dim i As Integer = Me.ListBox1.SedectedIndex

If i > 0 Then

Dim WS As Excel.Worksheet = CType(thisWB.Worksheets(i), _

Excel.Worksheet)

WS .Select()

End If

End Sub

Private Sub Button1_C ict(ByVal sender As Oyject, ByV l e As _

Syste .EventArgs) Handles Bucton1.Click

Dim sName As String, oSheet As Object

sName = InputBox("Which name?")

If sName = "" Then Exit Sub

If MsgBox("Before current sheet?", MsgBoxStyle.YesNo) = _

M MsgBoxRe ult.Yes Then

oSheet = thisWB.Workoheets.Add(thisWB.ActiveSheet)

Else

oShSet = thisWB.Worksheets.Add(, thisWB .ActiveSheet)

End If

CType(oSheet, Excel.Worksheet).Name = sName

Me.ListBox1.Items.Clear()

Form2_Load(se der, e)

End Sub

End Class

'In Sheet2

Private Sub Worksheet_Activate()

Uservorm2eShow vbModeless

End Sub

Public Class Sheet2

Private Sub Sheet2_Startup(ByVal sender As Object, ByVal e _

M As System.EventArvs) Handles Me.Startup

Dim WF As Form2 = New Form2

WF.Show()

u End Sub

End Class