Microsoft Office Tutorials and References
In Depth Information
UserForms have an Initialize event that is
triggered when you call the UserForm, which
can help you take action on your UserForm or
workbook. Suppose you want to enhance the
customized look of your form with a welcome
greeting that changes to reflect the time of day.
For example, if the UserForm were to be opened
in the morning, the message would include the text
“Good morning,” and so on for the afternoon and
evening. The following code achieves that effect,
as shown in Figure 19-2.
figurE 19-2
Private Sub UserForm_Initialize()
Dim TimeOfDay As String
If Time < 0.5 Then
TimeOfDay = “Good Morning ! “
ElseIf Time >= 0.5 And Time < 0.75 Then
TimeOfDay = “Good Afternoon ! “
TimeOfDay = “Good Evening ! “
End If
Label1.Caption = TimeOfDay & “Welcome to the company workbook.”
End Sub
TiMEs in VBA
Even after studying the preceding code, you might wonder why a number less
than .5 translates to morning, why a number greater than or equal to .5 and less
than .75 translates to afternoon, and why a number greater than or equal to .75
translates to evening. The reason is that VBA regards a time of day as a completed
percentage of the calendar day. For example, 12:00 noon is the halfway mark of a
calendar day, and one-half of something can be mathematically represented by the
expression .5. The Time function in VBA interprets a number less than .5 as
morning because by definition, half the day would not yet have completed. Afternoon is
between .5 (12:00 noon) and up to just before 6:00 PM, which the Time function
interprets as .75, being at the three-fourths mark of the 24-hour calendar day. A
Time number greater than or equal to .75 is evening because it is at or past 6:00 PM
and before the Time number of 0, which is 12:00 midnight of the next day.
You can also populate a Label’s caption from another control’s event procedure. Suppose your
UserForm provides a CommandButton that when clicked, toggles column C as being visible or
hidden, such as with this line of code in the CommandButton’s Click event:
Columns(3).Hidden = Not Columns(3).Hidden
Search JabSto ::

Custom Search