Userform Validation and Entry For: Next
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Userform Validation and Entry For: Next

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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/viewtop...c=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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com