Solved: Counting non-empty text boxes.

[Marcster]

Hello people, I wonder if you can help?.

I have 9 text boxes on a userform in Word. What i want is that when a user puts any text/numbers in the first 8 boxes, the 9th will display how many text boxes have data in them.
I want the 9th text box to auto update when the user changes any of the 8 text boxes. (maybe using the _AfterUpdate event of the text box?).

Any idea's?.

Thanks,

Marcster.

[sandam]

Hi and Welcome to VBAX :)

This should do the trick :thumb

(tested in Word 2003)

Option Explicit

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

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

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CheckData
End Sub

Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CheckData
End Sub

Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CheckData
End Sub

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CheckData
End Sub

Private Sub TextBox7_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CheckData
End Sub

Private Sub TextBox8_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CheckData
End Sub

Sub CheckData()
Dim oControl As TextBox
Dim i As Integer

i = 0
For Each oControl In UserForm1.Controls
If Len(oControl.Text) > 0 And oControl.Name <> "TextBox9" Then
i = i + 1
End If
Next
TextBox9.Text = CStr(i)
End Sub

[MOS MASTER]

Hi and Welcome to VBAX! :hi:

In this Excel topic: http://www.vbaexpress.com/forum/showthread.php?t=3335 you'll find a few more tricks for looping and counting multiple textboxes in a userform.

Enjoy! :whistle:

[Marcster]

Thanks sandam. Just what I was after. Works fine in Word 2000 and XP too. :pleased:

Will also look at the Excel topic too. Thanks MOS MASTER. :pleased:

Marcster.

[MOS MASTER]

Hi Marc, :yes
You're welcome! :beerchug:

[sandam]

glad to help :)

[fumei]

This is a bad use of a textbox.

Textboxes are for INPUT. Using them to display information is poor programming. There is NO input.

Use a label!

[MOS MASTER]

This is a bad use of a textbox.

Textboxes are for INPUT. Using them to display information is poor programming. There is NO input.

Use a label!
Ay...didn't thought of that one...You're absolutly right! :yes

[fumei]

It is very common to use textboxes for display, but if the text to be displayed is programatically put there, a textbox is the wrong control. They take up more resources - although these days this is not as much an issue I admit - and also allow user input and change.

If the DESIGN, the reason, is to tell the user something....why allow them to change that something? I would say at least half of UserForms I have seem could have most of their textboxes replaced as labels.

My rant for the day.....

[sandam]

:blush :omg2: I should know better. It was a case of looking and solving the problem with the first solution that came to mind.