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
Bookmarks