Solved: Text Boxes to be Initialized:

[asingh]

Hi......
I have a form in MS - Excell which has 5 textbox's (txtbox1, txtbox2..etc) and 2 combobox's (cmbox1,cmbox2). What I want is that when the form is loaded (the frm activate event) all the txtbox's be initialized to blank, and the combobox's also be initialized. I dont want to do this by typing the line : -- frmMain.txtbox1.value = "" for each of the txt box. Is there any For LOOP which can help me do this..i.e it will read all the txtbox controls on my form and intialize them all to " ".



thanks

asingh

[mvidas]

Hi asingh,

You'd be better off using the _Initialize event of the userform, rather than the _Activate event:Private Sub UserForm_Initialize()
Dim Ctrl As Control
For Each Ctrl In Me.Controls
If LCase(Left(Ctrl.Name, 6)) = "txtbox" Then
Ctrl.Object.Text = ""
End If
Next
End SubMatt

[Killian]

You can also use "TypeName" to identify controls.Dim Ctrl As Control
Dim i As Long

For Each Ctrl In Me.Controls
Select Case TypeName(Ctrl)
Case "TextBox"
'clear textbox
Ctrl.Object.Text = ""
Case "ComboBox"
For i = 1 To 5
Ctrl.AddItem i
Next i
Ctrl.ListIndex = 0
End Select
NextI used this recently, and initialized the comboboxes by referring to a named range for each, which I saved in the control's Tag property.

[Norie]

You can also use TypeOf.:)

If TypeOf ctl Is MSForms.TextBox Then