ExcelBanter - View Single Post

Ulrich MÃ¶ller

Am 09.06.2017 um 23:21 schrieb :
I need some help resolving an issue where my msgbox goes into a terminal loop.

I have a form with a runtime-generated list of names with check boxes next to each, plus €˜Go and €˜Cancel buttons. When the user launches the routine, the list box populates with names it reads from within the file. The user selects the names of the individuals he/she is interested in and the routine launches without issue.

As part of the code, I have error traps for canceling and exiting the routine for various reasons. They all work normally, except for the condition where the user clicks the launch button with no names selected. I have the following error trap code:

sub ChooseNames
dim i as Integer, j as Integer
Dim bFlg as Boolean

' some code ....

resetChoice:

j = 0

bFlg = False

For i = 0 To form1.ListCount - 1
' run through list box of names looking for
If form1.Selected(i) = True Then
'do some action here
j = j + 1 €˜ index to next name in list
bFlg = True
End If
Next i

€˜ error trap
If bFlg = False Then
MsgBox "Select one or more names, then click " & _
"'Go or click 'Cancel'", vbInformation, "Selection Required"
GoTo resetChoice
End If

' more code ....

End Sub

If any name is selected the bFlg is tripped to TRUE and the code executes normally. If no names are selected bFlg remains false and the error trap is activated. If the user responds to the message and selects €˜Cancel, the routine exits normally. If the user hits the €˜Go button without any of the names being selected, the message reappears and even with the form set to modal as true, I cant select anything in the list box or get the message box to disappear. So effectively, the user is stuck in a message box loop.

Any suggestions on how to display the message and allow the user to select from the run time list or cancel and exit?

Art
It does not make any sense to jumo back to the label "resetChoice" after
displaying the msgbox. The code execution does not stop to make a new
selection possible. Instead use the "normal" event driven programming
style: display the message and then leave the sub , so that the user has
a new change to select an item or to quit.

Some sample code:

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdGo_Click()
Dim i As Long
Dim strItem As String
Dim blnSelectionFound As Boolean

' Process selected items
For i = 0 To lstSelection.ListCount - 1
If lstSelection.Selected(i) Then
' process selected item
strItem = lstSelection.List(i)
blnSelectionFound = True
End If
Next

' Check for a missing selection and display a warning
If Not blnSelectionFound Then
MsgBox Prompt:="Select one or more names, then click " _
& "'Go or click 'Cancel'", _
Buttons:=vbOKOnly + vbInformation, _
Title:="Selection Required"
End If
End Sub

Ulrich