|
Control
and Dialog Box
Events in Excel
After you have
added controls to your dialog box or document, you add event procedures
to determine how the controls respond to user actions.
User forms and controls have a predefined set of events. For example, a
command
button
has a Click event that occurs when the user clicks the command button,
and UserForms have an Initialize event that runs when the form is
loaded.
To write a control or form event procedure, open a module
by double-clicking the form or control, and select the event from the
Procedure drop-down list box.
Event procedures include the name of the control. For example, the name
of the Click event procedure for a command button named Command1 is
Command1_Click.
If you add code to an event procedure and then change the name of the
control, your code remains in procedures with the previous name.
For example, assume you add code to the Click event for Commmand1 and
then rename the control to Command2. When you double-click Command2,
you will not see any code in the Click event procedure. You will need
to move code from Command1_Click to Command2_Click.
To simplify development, it’s a good practice to name your controls in
Excel before writing code.
We have added 3 labels, namely, 'Name', 'Salary' and 'Package' to the
form with corresponding text boxes where the user types the name and
the salary. On clicking on the command button 'Calculate Package', the
package on the form is displayed and data is also correctly transferred
to the Excel worksheet with formatting as specified in the code.
On clicking the command button 'Clear' data on the form is cleared. The
'Exit' button is clicked when you are finished with your data entries.
Macro Code:
Private Sub cmdCalculate_Click()
Range("A2").Value=txtName.Text
Range("B1").Value="salary"
Range("B2").Value=Val(txtSal.Text)
Range("C1").Value="Package"
Range("C2").Value=Val(txtSal.Text)+HRA+LTA+Medical+PF
txtPkg.Text=Val(txtSal.Text)+HRA+LTA+Medical+PF
Range("a1:c2").Font.Bold=True
Range("a1:c2").Font.Size=12
End Sub
Private Sub cmdClear_Click()
txtName.Text=""
txtSal.Text=""
txtPkg=""
End Sub
Private Sub cmdExit_Click()
End
End Sub
|
|
|