+ Reply to Thread
Results 1 to 4 of 4

Userform msgbox option to return to parent form

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Userform msgbox option to return to parent form

    In the following code (button click event) if the user has failed to select any attendees on the form a msgbox pops up giving the user the choose to exit or go back and select attendees on the form. The exit part is easy but I cannot figure out how to stop and leave the form up with the users other selections preserved and allow them to finish the filling out the form. I tied goto to return to the last area in the code before the button click but goto on works in the same procedure
    Any help will be appreciated.
    Robert

    File attached

    Private Sub cbt_Enter_Date_Click()
    Dim emplRng As Range
    Dim topicRng As Range
    Dim emplSel As Integer
    Dim topicSel As Integer
    Dim i
    Dim atndCnt As Integer
    
    'freeze panes
        Range("C2").Select
        ActiveWindow.FreezePanes = True
    
    'set row and column for match
    Set topicRng = Range("topics")
    Set emplRng = Range(Range("b2"), Range("b2").End(xlDown))
    atndCnt = 0
    
        'test that a topic has been selected
        If Len(cbx_Topic_Choice.Value) = 0 Then
          MsgBox "select a topic"
          Exit Sub
        End If
        'test that a date has been entered
        If Len(tbx_Date.Value) = 0 Then
          MsgBox "enter a date"
        Else 'select attendees
        
            For i = 0 To lstAttendees.ListCount - 1
            
                If lstAttendees.Selected(i) Then 'test for selected attendees
                    'count attendees
                    atndCnt = atndCnt + 1
                    
                    'replace date in corresponding cell
                    emplSel = WorksheetFunction.Match(lstAttendees.List(i), emplRng, 0) + 1
                    topicSel = WorksheetFunction.Match(cbx_Topic_Choice.Value, topicRng, 0) + 5
                    Cells(emplSel, topicSel).Value = CDate(tbx_Date)
                End If
            
            Next i
     'PROBLEM START           
            'If user did not selcet any employees, remind them and give option to correct or exit
            If atndCnt < 1 Then
                
                Dim varAnswer As String
                varAnswer = MsgBox("You did not select any attendees. No updates were made. Would you like to select attendees?", _
                vbYesNo, "Warning")
                If varAnswer = vbNo Then
                    'Exit Sub
                    cbn_Close_Form_Click
     
    'POINT OF FAILURE               
                    Else
                    'go back to the form
                    frmUpdtTpk.Show 
                    
                    End
                                                
                    
                    
                End If
                
                
            End If
            
        End If
      
        'Relocate userform to right of Topic Column
        Application.Goto Reference:=Cells(emplSel, topicSel), _
        Scroll:=True
        Cells(emplSel, topicSel).Activate
        With ActiveWindow
            Me.Left = .PointsToScreenPixelsX(ActiveCell.Left) + 300 + topicSel
            Me.top = .PointsToScreenPixelsY(ActiveCell.top) ' - ActiveCell.Height)
        End With
        
      
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Userform msgbox option to return to parent form

    you shouldn't require any code in order to do nothing ;-)
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Userform msgbox option to return to parent form

    Hello Hammer_757,

    This change will do what you want...
            'If user did not selcet any employees, remind them and give option to correct or exit
            If atndCnt < 1 Then
                
                Dim varAnswer As String
                varAnswer = MsgBox("You did not select any attendees. No updates were made. Would you like to select attendees?", _
                vbYesNo, "Warning")
                If varAnswer = vbNo Then
                    'Exit Sub
                    cbn_Close_Form_Click
                    
                    Else
                    'go back to the form
                    lstAttendees.SetFocus
                    lstAttendees.TopIndex = 0
                    Exit Sub
                End If
                
            End If
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: Userform msgbox option to return to parent form

    thanks Leith!
    that worked perfect

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1