modeless userform problem

[AD108]

Hello,

My goal with the code below was to check if the user had entered a vendor
name for each item that had been ordered. The userform is shown if a vendor
name is missing, and I want them to be able to add it while the userform is
on the screen. The problem is that the loop runs all the way through, as
the userform is modeless. Is there a way around this? (Can I stop the loop
until the user clicks ok on the userform, and have the userform modeless at
the same time?)

Thanks in advance.

AD108

Sub ValidateOrder()
Dim rngCell As Range
Dim intRow As Integer
Dim intCol As Integer
Dim rngVendor As String
Dim strItem As String
Dim msg As String

For Each rngCell In Sheets(14).Range("AG3:AG398")
If rngCell.Value <> 0 Then
intRow = rngCell.Row
intCol = rngCell.Column
Sheets(2).Activate
rngVendor = Cells(intRow, 39).Value
If rngVendor = "" Then
strItem = Cells(intRow, 3)
msg = "Please enter a Vendor for "
msg = msg & strItem
UserForm3.Label1.Caption = msg
UserForm3.Show vbModeless
End If
End If
Next rngCell
End Sub

 

[Tom Ogilvy]

Untested, but you could try something like this:

If rngVendor = "" Then
strItem = Cells(intRow, 3)
msg = "Please enter a Vendor for "
msg = msg & strItem
UserForm3.Label1.Caption = msg
UserForm3.Show vbModeless

do while some condition involving the userform
doevents
Loop

End If
End If

the specifics would depend on how you are handling the userform.

 

[AD108]

Thanks again Tom,

Ariel

Untested, but you could try something like this:


do while some condition involving the userform
doevents
Loop

the specifics would depend on how you are handling the userform.