VBA for Excel 2007 tutorial-Form events


Form events

An event of the form is fired up when an action is performed on the form. The common events of the form are Initialize, Activate, Click, and Resize. You can view the events list of the form by doubly clicking on form.

VBA for Excel: Events of form object

   -Initialize: This event does its job when the form firstly loads (initialization). For example, show a welcome message when the form firstly loads.
By selecting Initialize from the procedure dropdown list, the UserForm_Initialize() procedure will show. The code in this procedure is executed when the form firstly loads.

   Private Sub UserForm_Initialize()
       MsgBox "Welecome!" 'display welcome message
   End Sub

   -Activate: This event works when the form is active. For example, when the form is active, 10 numbers from 1 to 10 will be populated in to the current working sheet.

  Private Sub UserForm_Activate()
    Dim i As Integer
    For i = 1 To 10
      Cells(i, 1) = i
    Next
  End Sub

   -Click: This event performs its job when the use clicks on the form. For example, when the form is clicked the will be bigger or smaller.

Dim i As Integer 'global variable
  Private Sub UserForm_Click()
    Dim curWidth As Integer
    curWidth = Me.Width
    If i = 0 Then
      Me.Width = curWidth * 2 'change form width
      i = 1
   ElseIf i = 1 Then
     Me.Width = curWidth / 2 'change form width
     i = 0
   End If
  End Sub

  Private Sub UserForm_Initialize()
    i = 0 ' assign 0 to the i variable to make a start
  End Sub

   -Resize: When the form is resized, the Resize event does a job. For example, when the form is resized, the width and height of the form will be displayed in a message box.

  Private Sub UserForm_Initialize()

    'Resize the form when initializing
    Dim curWidth, curHeight As Integer
    curWidth = Me.Width
    curHeight = Me.Height
    Me.Width = curWidth + 500
    Me.Height = curHeight + 300

  End Sub

   'This event works when the form is resized
  Private Sub UserForm_Resize()

    MsgBox " Form width:" & Me.Width & vbCrLf & "Form height:" &   Me.Height

   End Sub



Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.