modeless userform problem
 

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

modeless userform problem
 

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.

--
Regards,
Tom Ogilvy


"AD108" wrote in message
...
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

modeless userform problem
 

Thanks again Tom,

Ariel
"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"AD108" wrote in message
...
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