Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    loops and validation (excel/vba)

    Post deleted by CROSSFYRE2

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: loops and validation (excel/vba)

    Does this do what you want?

    Dim rngFound As Range
    Set rngFound = ws.Range("C:C").Find(What:= Me.txtSdnumber, _
    LookIn:=xlValues, LookAt:=xlWhole)
    If Not rngFound Is Nothing Then
    Me.txtSdnumber.setFocus
    MsgBox "Service Desk number already used", vbExclamation
    Exit Sub
    End If

    PS you do not consistently exit the procedure after displaying an error message.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: loops and validation (excel/vba)

    My code as it sits right now enables me to loop back to the first listbox to allow the user to input more data. This data includes a service number in column C. There can be many a jobs to input for each service number. What I need to to have happen is to check the service number they input to see if that number has already been used. If not then allow them to continue, else have it msgbox "service number already used".

    How can I go about this and suggestions or advice would be greatly appreciated.



    <code>
    Private Sub cmdAdd_Click()
    Dim strValue As String
    Dim rng As Range
    Dim irow As Long
    Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
    Dim ws As Worksheet
    Set ws = Worksheets("mar2008")

    Do

    strValue = Me.txtSdnumber
    irow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
    If Trim(Me.txtJobnames.Value) = "" Then
    Me.txtJobnames.SetFocus
    MsgBox "Please enter the Job name that has been requested to be placed on hold!"
    Exit Sub
    End If
    If strValue = "" Then
    Me.txtSdnumber.SetFocus
    MsgBox "Please enter the Service Desk Number!"
    End If

    If IsNumeric(ws.Cells(irow, 3).Value) Then
    Me.txtSdnumber.SetFocus
    Else: MsgBox "please enter the correct Service Desk number!"
    End If
    If Me.txtRequestor.Value = "" Then
    Me.txtRequestor.SetFocus
    MsgBox "Please enter the Requestor's Name!"
    Exit Sub
    End If
    If Me.txtINitials.Value = "" Then
    Me.txtINitials.SetFocus
    MsgBox "Please enter your initials as requested!"
    Exit Sub
    End If
    ws.Cells(irow, 1).Value = Date
    ws.Cells(irow, 2).Value = Me.txtJobnames.Value
    ws.Cells(irow, 3) = strValue
    ws.Cells(irow, 4).Value = Me.txtRequestor.Value
    ws.Cells(irow, 5).Value = "YES"
    ws.Cells(irow, 6).Value = Me.txtINitials.Value
    ws.Cells(irow, 7).Value = Me.txtCOmment.Value
    Me.txtJobnames.Value = ""
    Me.txtSdnumber.Value = ""
    Me.txtRequestor.Value = ""
    Me.txtINitials.Value = ""
    Me.txtCOmment.Value = ""
    Me.txtJobnames.SetFocus
    Dim xy
    xy = MsgBox("Do you have mor than one job within this ticket?", vbYesNo)
    If xy = vbNo Then MsgBox "THANK YOU, PLEASE HIT THE EXIT BUTTON IF DONE"
    Loop While xy = vbYes

    Dim X
    X = MsgBox("Any more Jobs to be held?", vbYesNo)
    If X = vbNo Then MsgBox "THANK YOU, PLEASE HIT THE EXIT BUTTON IF DONE"
    Exit Sub
    End Sub
    </code>

    Hello again My Friend,

    It does work, but I think I have it in the wrong place, because every time I try to put in another jobname with the same ticket number I get the error message that the service desk number has already been used.

    So what I'm trying to get done today is check validation to ensure that the service desk number has not been used before. If it hasn't then allow the user to type in his first jobname service desk ticket, etc... then if he has any more jobs within that ticket to allow him to keep entering more information (set focus at jobnames listbox (1st listbox so they can start all over again)

    any ideas

  4. #4
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: loops and validation (excel/vba)

    i'm not sure what you mean by " you do not consistently exit the procedure after displaying an error message."

    if I do an exit sub after the loop, will it not stop the loop?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: loops and validation (excel/vba)

    You should have an Exit Sub after each message box that warns the user that data are missing or incorrect, otherwise the code will continue as if nothing is wrong.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: loops and validation (excel/vba)

    Please try to explain clearly and without ambiguity what exactly should be tested.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: loops and validation (excel/vba)

    ok, I have added the exit subs after each message box as you have suggested.

    my problem still is the loop and validation procedures.

    if the user has a new jobname & service desk ticket to enter - I need to ensure that that service desk ticket has not been used before.
    If it has then Msgbox "service desk ticket already used"
    if it has not been used before then allow the user to input said data into the correct fields.
    after inputing the first jobname & service desk number ask the user if they have any more jobs to input for that same ticket.
    if not then exit else
    allow the user to start all over again and input a different jobname but with the same service desk number.
    once complete ask the user again if there are more jobanmes for this service desk number
    if not then exit else
    allow the user to start all over again and input a different jobname but with the same service desk number.


    I hope I stated it correctly this time. I'm not real Good with writing and words as you already know.

    But I do appreciate the assistance and Ideas you give me

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: loops and validation (excel/vba)

    You'll have to rethink the logic of your setup.
    1) The user can't enter anything into the userform while the macro is running, so there is no point in creating a loop. The user should fill in one set of items and click the Add button, or click the Exit button.
    2) Since the service desk number can be reused (with different job names) there is no point in checking whether the service desk number has already been used. Instead, you should check that the same job name isn't used twice with the same service desk number.

  9. #9
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: loops and validation (excel/vba)

    do you mean something like this?

    <code>
    Set rngFound = ws.Range("C:C").Find(What:=Me.txtSdnumber, _
    LookIn:=xlValues, LookAt:=xlWhole)
    If rngFound Is Nothing Then
    Set rngFound = ws.Range("B:B").Find(What:=Me.txtJobnames, _
    LookIn:=xlValues, LookAt:=xlWhole)
    Me.txtJobnames.SetFocus
    MsgBox "Service Desk HAS ALREADY BEEN USED WITH THAT JOB NAME", vbExclamation
    Exit Sub
    End If
    </code>

    if I do not perform a loop then do I set focus to the txtjobnames listbox?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: loops and validation (excel/vba)

    You don't do anything with the result of Set rngFound = ws.Range("B:B").Find(...) <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  11. #11
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: loops and validation (excel/vba)

    I'm sorry again Hans, But I do not understand. I will try and look it up in my excel books.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: loops and validation (excel/vba)

    You try to find the jobname with the instruction

    Set rngFound = ws.Range("B:B").Find(What:=Me.txtJobnames, _
    LookIn:=xlValues, LookAt:=xlWhole)

    But then you display a message box regardless of the result. Moreover, you're looking in the entire column, so you may find the jobname even if it is used with another service desk number.

    I recommend that you take a large piece of paper, and sketch out the decision flow: "if this then that".
    This may make it clearer how the code should be modified.

Posting Permissions

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