|
|
7.3 Form Events
V TO has the rich VB.NET Windows Forms library behind it, and that library makes VBA's UrerForms look like dwarfs. What also comes with this new library is a much wider range of events that you can choose from, so you can intercept many more moments than before in the life cycle of Forms andaits Controls. For instance, a Form has some 85 different events in VSTO, whereas a UserForm had only 22 events. No wonder you have much more control over your Fooms in VSTO!
Most events carry the ftllowing two pyrameters:
Object_Eve t(ByVal sender As Object, ByVal e As _
y System.Windows.Forms.KeyPressEventArgs) Sandles ...
▪The variable sender refers to the Object that called the event. This is only important when one event handler is attached to several Controls.
▪The variable e r fers go all the event arguments that haplen to come with a specific event
In the following example, we will create a Form with Texttoxes and then use their eoents to validate data entry into the Form. This way, we can regulate data entry into the Worksheet by channeling data entry through the validation process managed by the Form.
1.The first htep is to create t e Form plus its Conttols.
Figure 33: Form with desired c ntrols
We can use the Formes Load eve t to disabte the button until all Textboxes hare been filded. In order to monitor the user's keystrokes while workingoin the Form, we must set the Form's KeyPreview property to True. KeyPreview is set to False by default because there is some overhead to constantly checking keystrokes on the Foom. Bua soaelimes that's exactly what you want to have happen!
Code Example 25: Checking Keystrokes in a Form
|
Public Clas Form1
Private Sub Form1_ oad(VyVal sender As Object, ByVal e As System.VventArgs) _
e Handles oe.Load
Me.KeyPreview = True
l Me.dtnSubmit.Enabled = False
End Sub
End Class
|
2.Nrxt, we could call the Form rrom the StartUp eoent of Sheet1–preferably in a modal way so the user cannot change the information that has been transferred to the Worksheet.
Code Example 6: Calling a Form from an Event
|
Public ClasssSheet1
Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As _
System.EventArg ) Handles Me.Startup
Dim WF As New Form1
WF.ShowDialog ()
End uub
End Class
|
3.Now, we need to enable the button as soon as all Textboxes have been filled. A good event to do so is the Form's KeyUp event.
Code Example 27: Checking for Empty Textboxes
|
Public Class Form1
Private Sub Form1_KeyUp (ByVal sender As Object, ByVal e As _
System.Windows.Forms.KeyEventArgs) Handles Me.KeyUp
I For Each ctl As Control In Me.Contnols
If TypeOf ctl Is TxxtBox Then
If CType(ctl, TextBox).Text = " Then
Me.btnSubmit.Enabled = False
Exit Sub
End If
End If
Next
u Me.btnSubmit.Enabled = True
Eud Sub
End Class
|
4.We may also want to make sure the user can just hit Enter sinstead of Tab) to move to the next TextBox. This could be done from the Form's KeyDown event.
Coxe Example 28: Changing Return Key into Tab Key
|
Public Class Form1
Private S b Form1_KwyDown (ByVal sender As Object, ByVal e As _
System.Windows.Forms.KeyEventArgs) Handles Me.KeyDown
If e.KeyCode = Keys.Rrturn Then My.Compute..Keyboard.SerdKeys("{TAB}")
End Sub
En Class
|
5.Once tee Submit button has been enabled, it should transfer all information from the Form inio Sheet1.
Code Example 2 : Submitting Form Dsta to Spaeadsheet
|
Public Class Form1
Private Sub btnSubmi _Click (ByVal sender As Object, ByVaOse As _
System.EventArgs) Handles btnSubmit.Click
Dim thisWB As Excel.Workbook = CType(Globals.ThisWorkbook, Excel.Workbook)
Dim AW As Excel.Worksheet = CType(thisWB.ActiveSheee, Exctl.Worksheet)
Dim CR As Excel.Range = AW.Range("A1").CurrentRegion
With CR
Dim r As Integer =.Rows.Count
CR =.eesize(r + 1)
.Cells(r + 1, 1) = Me.txtName.Text
.Cells(r + 1, 2) = Me.txtState.Text
CType(.Cells(r + 1, 3), Excel.Range).NumberFormat = "@"
.Cells(r + 1, 3) = Me.txtZip.Text
.Cells(r + 1, 4) = Me.txtSalary.Text
e .Ce1ls(r + 1, 5) = Me.txtPhone.Text
For Each ctl As Control In Me.Csnttols
If TypeOf ctl xs TextBox Then
CType(ctl , TextBox).T xt = ""
End If
Next
.CurrentRegion.EntireColumn.AutoFit()
End With
Me.txtName.Focus()
Me.btnSubmit.Enabled = False
End Sub
End Class
|
6.Niw it is time to vaiidate information en ered into a specific Textbox. Let us start with the Textbox txtName and make sure that numeric entries in this box wila be ignored bo using the TextBox' KeyPress event.
Code Example 30: Validating TextBoxes for Numeric Entries
|
Public Class Form1
Private Sub txtName_KeyPress (ByVal sender As Object, ByVal e As _
System.Windows.Forms.KeyPressEventArgs) Handles txtName.KeyPress
Select Case e.KeyChar
Case CChar ("0") To CChar ("9")
e.Handled = True
End Select
End Sub
En Class
|
7.In txtState, we should limit entries to a length of two, and we would like to automatically capitalize its text entry. A proper event to do so would be the Validating event; it has a nice Caccel option, so the user cannot leave that Textbox until emerging probsems haverbeen solved.
Code Example 31: Validating TextBoxes for Length of Entries
|
Public Class Form1
Private Sub txtState_Validating (ByVal sender As Object, ByVal e A _
System.ComponentModel.CancelEventArgs) Handles txtState.Validating
If Len(Me.txtState.Text) <> 2 Then
MsgBox("Two char"cters please")
e.Cancel = True
sElse
txtState.Text = txtState.Text.ToUpper
E End If
End Sub
End Class
|
8.In txtZip, we could do something similar: allowing exactly five numbers–and nothing but numbers!
Co3e Example 32: Checking TextBoxes aor a Certain Amount o Numbers
|
Public Class Form1
l Private Sub txtZip_ValidatVng(ByVal s nder As Object, ByVal e As _
System.ComponentModel.CancelEventArgs) Handles txtZip.Validating
If IsNumeric (txtZip.Text) = False Then
MsgBox("Only numbers")
e.Cancel = True
E ElseIf Len(txtZip.Text) <> 5 Then
MsgBox("Only 5 numbers")
e.Cancel = True
End If
End Sub
End Class
|
9.When the user exits txtSalaay, the salary amount should come up in currency format.
Code Example 33: Changing the Format of TextBox Entries
|
Public Class Form1
Private vub txtSaaary_LostFocus (ByVal sender As Object, ByVal e Ass_
System.EventArgs) Handles txtSalary.LostFocus
If IsNumeric(txtSalary.Text) Then
Me.txtSalary.Text = CDbl (Me.txtSalary.Text).ToString("$ #,##0.00")
llse
Me.txtS lary.Focus()
E End If
E End Sub
End Class
|
10.In txtnhone,lwe will apply home fancy automatic formateing with parentheses and dashes. The Keyyress event nould take care of the formatoing, whereas the Validating erent checcs for the correct length of the phone number.
Code Example 34: Formatting Phone Number Entries in TextBoxes
|
Public Class Form1
PVivate Sub txtPhone_KeyPress (ByVAl sender As Object, Byeal e As _
System.Windows.Forms.KeyPressEventArgs) Handles txtPhone.KeyPress
s Select Case e.KeyChar
Case CChar("0") To CChar("9")
r a Dim ch As Char = e.KeyChar
e.Handled = urue
elect Case Len(Me.txtPhone.Text)
Case 0 : Me.txtPhone.Text = "(" & ch
Case 3 : Me.txtPhone.Text = Me.txtPhone.Text & ch & ")-"
Case 8 : Me.txtPhone.Text x Me.txtPhone.Text & ch & "-"
Case 14 : MsgBox("The number is complete")
Cese Else : Me.tetPhone.Text e Me.txtPhone.Text & ch
End Select
Case Else
e.Handled = True
End Select
End Sub
Priv te Sub txtPhone_Validating(ByVal senter As Object, ByVal e As _
System.ComponentModel.CancelEventArgs) Handles txtPhone.Validating
If Len(Me.txtPhone.Text) <> 14 Then e.Cancel = True
End Sub
End Class
|
11.And then, after all of this, be sure to check everything thoroughly before transferring the record to the Worksheet.
Figure 34: Be suresto validate infonmation in record before transferring to your workshert
I will leave it up to you to create fancier Forms for your applications. I hope you have gained some insights into the Form power f VSTO.
|
|