PDA

View Full Version : Finding Highest valued TextBox?



Simon Lloyd
10-24-2006, 09:15 PM
Hi, does anyone know how to examine a userform that has 12 textboxes on it each with a value in it?....the examination would be to find the highest value return the value and which box it resides in........if there were two or more values the same return all those values and the boxes they reside in.

I have an idea that they would need to be manipulated into some sort of array but have no idea how to do it or how to return the result....basically i have nothing!

Any ideas?

Regards,
Simon

acw
10-24-2006, 11:13 PM
Simon

Put a button on your form, and include the following code


Private Sub CommandButton1_Click()
Dim arr(12, 2)
i = 1
For Each cnt In Me.Controls
If TypeName(cnt) = "TextBox" Then
arr(i, 1) = cnt.name
arr(i, 2) = Val(cnt.Text)
i = i + 1
End If
Next cnt

holder = WorksheetFunction.Max(WorksheetFunction.Index(arr, 0, 3))
For i = 1 To 12
If arr(i, 2) = holder Then
MsgBox arr(i, 1) & ", " & arr(i, 2)
End If
Next i

End Sub


I've hard coded the array to have 12 lines based on your post.

HTH

Tony

Simon Lloyd
10-25-2006, 12:08 AM
Tony thanks very much works a treat!

Regards,
Simon