Excel: VBA: Format Group of textboxes on forms

[BIGTONE559]

Need helping with formatting all textboxes on a form.

i have the following code:

If TextBox1.Text = "" Then
TextBox1.Text = "0:00"
Else
TextBox1.Text = Format(Format(TextBox1.Text, "##:##"), "Medium Time")
End If

I want to apply this code to a group of textboxes exit event without having to go to each textbox event and type the code. Is there a way to do this?

 

[John_w]

You could put that code in its own subroutine which is called by each text box Exit event, like this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Format_TextBox TextBox1
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Format_TextBox TextBox2
End Sub

Private Sub Format_TextBox(theTextBox As MSForms.Control)
    If theTextBox.Text = "" Then
        theTextBox.Text = "0:00"
    Else
        theTextBox.Text = Format(Format(theTextBox.Text, "##:##"), "Medium Time")
    End If
End Sub

Or you could write a custom text box class event handler to handle events from your text boxes (i.e. the Exit event). http://j-walk.com/ss/excel/tips/tip44.htm shows the basic technique.

 

[BIGTONE559]

You could put that code in its own subroutine which is called by each text box Exit event, like this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Format_TextBox TextBox1
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Format_TextBox TextBox2
End Sub

Private Sub Format_TextBox(theTextBox As MSForms.Control)
    If theTextBox.Text = "" Then
        theTextBox.Text = "0:00"
    Else
        theTextBox.Text = Format(Format(theTextBox.Text, "##:##"), "Medium Time")
    End If
End Sub

Or you could write a custom text box class event handler to handle events from your text boxes (i.e. the Exit event). http://j-walk.com/ss/excel/tips/tip44.htm shows the basic technique.

Perfect Timing John. I appreciate it. I think i'll just define the function as you suggested.

thanks i'll try and post results