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

    looping for usrform (vba/excel)

    I have a userform that pops up if the user has more than one item to input into the spreedsheet.

    what i'm trying to do is loop it back to that same userform if the user has more than two items to input.
    it llops back but does not clear set focus on the txtnames listbox when it starts over nor does it clear it out so the user see's a blank listbox.
    but most importantly - it does not add the data into the spreadsheet like i need it too.

    any advice would be greatly appreciated..

    here's my code for it

    <code>


    Private Sub CommandButton1_Click()
    Unload Me
    MsgBox "Thank you, Please come again"
    End Sub

    Private Sub CommandButton2_Click()
    Me.txtname.Value = ""
    Me.txtticket.Value = ""
    Me.txtname.SetFocus
    End Sub

    Private Sub Cmdadd1_Click()
    Dim strValue As String
    Dim rng As Range
    Dim xy
    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 While xy = vbYes
    '***************
    strValue = Me.txtticket
    '****
    irow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
    '****
    If Trim(Me.txtname.Value) = "" Then
    Me.txtname.SetFocus
    MsgBox "Please enter the Job name that has been requested to be placed on hold!"
    Exit Sub
    End If
    '****
    If strValue = "" Then
    Me.txtticket.SetFocus
    MsgBox "Please enter the Service Desk Number!"
    Exit Sub
    End If
    If IsNumeric(ws.Cells(irow, 3).Value) Then
    Me.txtticket.SetFocus
    Else: MsgBox "please enter the correct Service Desk number!"
    End If

    ws.Cells(irow, 2).Value = Me.txtname.Value
    ws.Cells(irow, 3) = strValue
    Me.txtname.Value = ""
    Me.txtticket.Value = ""
    Me.txtname.SetFocus

    xy = MsgBox("Do you have more than one job within this ticket?", vbYesNo)
    If xy = vbNo Then MsgBox "THANK YOU, PLEASE HIT THE EXIT BUTTON IF DONE"
    If xy = vbYes Then frmmulti.Show
    irow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
    Exit Sub
    Loop

    '************************************************* ********************
    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
    Private Sub UserForm_QueryClose(Cancel As Integer, _
    CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the EXIT button!"
    End If
    End Sub

    </code>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: looping for usrform (vba/excel)

    could you attach a working example of your file? It is tough to debug and test without making an example...

    Steve

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

    Re: looping for usrform (vba/excel)

    the whole kit and koboodle is here. after you place one job on hold it will ask you if you have another to place on hold, try to do it three of four times and it will show you what i mean. check out the mar2008 folder after wards.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: looping for usrform (vba/excel)

    You never go into your Do Loop Cmdadd1_Click since you tell it to "do while xy= vbyes" and at that time xy is an empty variable since you haven't put anything into it.

    You can set it initially with
    xy = vbyes

    I also got an error in the line:
    If xy = vbYes Then frmmulti.Show

    since in XL2002 you can not open the same form from the form since it is already opened.

    Also the Exit sub will always prevent the loop from going more than once whether you use Yes or no. What is the purpose of the exit sub there?

    Steve

Posting Permissions

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