# Userform [Averages of Textboxes IF populated)

#### Grizlore

##### Active Member
I am trying to create some VBA code, which will average the contents of up to twelve textboxes on a Userform, and then put the average in the next one.

I have sorted this, HOWEVER.. I have a couple of problems.

• The average isn’t correct
• Some of the textboxes may be blank, and therefore should not be included in the calculation
Here is my code
Code:
``````Private Sub CommandButton3_Click()
Dim results(12) As Double
Dim ave As Double
results(1) = CDbl(TextBox5.Value)
results(2) = CDbl(TextBox6.Value)
results(3) = CDbl(TextBox7.Value)
results(4) = CDbl(TextBox8.Value)
results(5) = CDbl(TextBox9.Value)
results(6) = CDbl(TextBox10.Value)
results(7) = CDbl(TextBox11.Value)
results(8) = CDbl(TextBox12.Value)
results(9) = CDbl(TextBox13.Value)
results(10) = CDbl(TextBox14Value)
results(11) = CDbl(TextBox15.Value)
results(12) = CDbl(TextBox16Value)

ave = Application.WorksheetFunction.Average(results)

TextBox17.Value = ave

End Sub``````

Many Thanks

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
i think it might be easier to use a formula for this, and maybe create a button to run it

I am afraid i can't help with the VBA but i would guess a forumla would be far easier

Try below code

Code:
``````Private Sub CommandButton2_Click()

Dim a As Double
Dim b As Integer

a = 0
b = 0

For i = 0 To Me.Controls.Count - 1
If TypeName(Me.Controls(i)) = "TextBox" Then
If Me.Controls(i).Value <> "" Then
a = a + Val(Me.Controls(i).Value)
b = b + 1
End If
End If
Next i

TextBox13.Value = a / b

End Sub``````

Private Sub CommandButton1_Click()

Dim a As Double
Dim b As Integer

a = 0
b = 0

For i = 0 To Me.Controls.Count - 1
If TypeName(Me.Controls(i)) = "TextBox" Then

If Me.Controls(i).Value <> "" Then
a = a + Val(Me.Controls(i).Value)
b = b + 1
End If
End If
Next i

TextBox13.Value = a / b

End Sub

Thanks for you help, however...

The calculation is wrong. There are other TextBoxes on the Userform, and I guess they are being included too. [?]

Is there a way around this ?

Thanks for you help!

Sorry Plz try below code

Code:
``````Private Sub CommandButton1_Click()

Dim a As Double
Dim b As Integer

a = 0
b = 0

For i = 0 To Me.Controls.Count - 1
if Me.Controls(i).name  = "TextBox5" or Me.Controls(i).name  = "TextBox6" or Me.Controls(i).name  = "TextBox7" or Me.Controls(i).name  = "TextBox8" or Me.Controls(i).name  = "TextBox9" or Me.Controls(i).name  = "TextBox10" or Me.Controls(i).name  = "TextBox11" or Me.Controls(i).name  = "TextBox12" or Me.Controls(i).name  = "TextBox13" or Me.Controls(i).name  = "TextBox14" or Me.Controls(i).name  = "TextBox15" or Me.Controls(i).name  = "TextBox16" Then
If Me.Controls(i).Value <> "" Then
a = a + Val(Me.Controls(i).Value)
b = b + 1
End If
End If
Next i

TextBox13.Value = a / b

End Sub``````

Last edited:
Thanks again

I get an error here TextBox17.Value = a / b (I have changed your "TextBox13.Value")

I think the "a" and "b" comes back as a zero

This is the code i am using.

Code:
``````Private Sub CommandButton3_Click()

Dim a As Double
Dim b As Integer

a = 0
b = 0

For i = 0 To Me.Controls.Count - 1
If Me.Controls(i).Name = "Textbox5" Or Me.Controls(i).Name = "Textbox6" Or Me.Controls(i).Name = "Textbox7" Or Me.Controls(i).Name = "Textbox8" Or Me.Controls(i).Name = "Textbox9" Or Me.Controls(i).Name = "Textbox10" Or Me.Controls(i).Name = "Textbox11" Or Me.Controls(i).Name = "Textbox12" Or Me.Controls(i).Name = "Textbox13" Or Me.Controls(i).Name = "Textbox14" Or Me.Controls(i).Name = "Textbox15" Or Me.Controls(i).Name = "Textbox16" Then
If Me.Controls(i).Value <> "" Then
a = a + Val(Me.Controls(i).Value)
b = b + 1
End If
End If
Next i

TextBox17.Value = a / b

End Sub``````

Try this
Code:
``````Private Sub CommandButton3_Click()

Dim a As Double
Dim b As Integer

a = 0
b = 0

For i = 0 To Me.Controls.Count - 1
If Me.Controls(i).Name = "TextBox5" Or Me.Controls(i).Name = "TextBox6" Or Me.Controls(i).Name = "TextBox7" Or Me.Controls(i).Name = "TextBox8" Or Me.Controls(i).Name = "TextBox9" Or Me.Controls(i).Name = "TextBox10" Or Me.Controls(i).Name = "TextBox11" Or Me.Controls(i).Name = "TextBox12" Or Me.Controls(i).Name = "TextBox13" Or Me.Controls(i).Name = "TextBox14" Or Me.Controls(i).Name = "TextBox15" Or Me.Controls(i).Name = "TextBox16" Then
If Me.Controls(i).Value <> "" Then
a = a + Val(Me.Controls(i).Value)
b = b + 1
End If
End If
Next i

if b = 0 then
msgbox "Plz entry things!!"
exit sub
end if

TextBox17.Value = a / b

End Sub``````

Last edited:
Change all Textbox to TextBox, then may fix.

"b" always seems to be 0, and therefore I keep getting the MsgBox

?

Replies
6
Views
211
Replies
3
Views
166
Replies
0
Views
100
Replies
0
Views
95
Replies
26
Views
588

### Forum statistics

1,203,327
Messages
6,054,751
Members
444,748
Latest member
knowak87 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back