Userform Validation and Entry For: Next

[d-alan]

Here is a three part question that i am convinced will use the practically the same code, but I cannot fiqure the coreect syntax. I have been trying for:next combinations, anyways.

1. I have 13 textboxes in a userform that are named uni, uni2, uni3, uni4, etc... On the enter event in to the textbox I want that textbox to show the text =Each.
I have this code that works fine, however i have it thirteen times.
Private Sub uni13_enter()
uni13.Text = "Each"
End Sub


2. I think you get the drift, here I have thirteen textboxes that update totalt texbox on change. Again thirteen lines of codes just to rename from uprice, uprice1, uprice2, uprice3 etc....
Private Sub uprice_Change()
On Error Resume Next
total = uprice * quan
Totalt = Val(total.Text) + Val(total2.Text) + Val(total3.Text) + Val(total4.Text) + Val(Total5.Text) + Val(Total6.Text) + Val(Total7.Text) + Val(Total8.Text) + Val(Total9.Text) + Val(Total10.Text) + Val(total11.Text) + Val(Total12.Text) + Val(Total13.Text)
On Error Resume Next
End Sub
3. Still on the same userform, but a little different question, (same formula I think).
I have 13 comboboxes named modes, modes2, modes3, modes4 etc....
What code can I use to check to make sure that none of the comboboxes contain the same value.
if modes = modes2 then
msgbox YOU CANNOT ENTER THE SAME VALUE.

This userform has 104 textboxes, and 39 comboxes not to mention optionbuttons and commandbuttons. The code is just become to large to work with.
On a bright note it does all work except for question number 3.
Any help would be appreciated.
Thanks
-D

 

[davers5]

You need to use control arrays so you can use For Next loops. I just learned how to do this in VBA a couple days ago with the aid of this board. See my post:

http://www.mrexcel.com/board/viewtopic.php?topic=3611&forum=2

If that link doesn't get you anywhere do a search for control arrays posted by me (davers5) and it should help you out.

Good luck,

Dave

 

[d-alan]

Thanks Dave,

But I think I have bitten off too much in this one. If I would have just wriiten the longhand code for question three I would be done already. But now I want to fiqure this out.
This is the code i have put together for checking to make sure there are not duplicate entries.(?#3)

Dim i As Integer
For i = 2 To 14
'Although 13 boxes I used fourteen because at some point it was looking at thirteen in both values.

J = Controls("modes" & i)
On Error Resume Next
k = Controls("modes" & i + 1)
If k = J Then
MsgBox (k) & " Is a Double Entry Please Adjust Entry"
End If
'Since modes +(i) always is a number I just through in this code to check first entry.
If sales2.modes = J Then
MsgBox (J) & " Is a Double Entry Please Adjust Entry"
End If
Next i

The problem here is that it will only look at consecutive entries. If there is the same entry in 'box2 and 'box7 it does not find it.

Suggestions?

 

[d-alan]

This is what i came up with for checking the values of comboboxes, listboxes, etc with same name plus differing value.(ie modes2, modes3, modes4, modes5)

Dim i As Integer
For i = 2 To 12
J = Controls("modes" & i)

For r = 3 To 13
K = Controls("modes" & r)
If K<> "" Then
If i<> r Then
If J = K Then
MsgBox (K) & " IS ENTERED TWICE ON LINE " & (r) & " AND ON LINE " & (i) & _
". PLEASE ADJUST ENTRIES. "
Exit Sub
End If
End If
End If
Next r

If J<> "" Then
If sales2.modes = J Then
MsgBox (J) & " IS ENTERED TWICE ON LINE 1" & " AND ON LINE " & (i) & _
". PLEASE ADJUST ENTRIES. "
Exit Sub
End If
End If

Next i
This message was edited by d-alan on 2002-04-03 07:35