In a macro I need to find a value in any of 10 workbooks. Anytime the value
from a userform is found the macro should halt a let me do anything outside
the macro just like the standard find/findnext function.
This works, except for the part, where the macro needs to halt and I can do
anything until I use the Next button on the userform

For i = 1 To 10
Workbooks(Files(i)).Activate
For j = 1 To Worksheets.Count
With Worksheets(j).Range("A1:A2000")
Set c = .Find(Nr, LookIn:=xlValues)
If Not c Is Nothing Then
If Nr = "" Then Exit Sub
firstAddress = c.Address
Worksheets(j).Activate
Range(firstAddress).Select
Do
Set c = .FindNext(c)
nextAddress = c.Address
Range(nextAddress).Select
something here!!
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next
Next

something here!!!
can be a Msgbox or Inputbox of course, but using any of these prevents me
from using the spreadsheet because the macro still has control. The standard
Excel find/findnext function stops and lets the user take control. How is
that done?

My userform is used vbModeless.

Hi there,

I think perhaps you need to separate the Find and the Find Next.
The Find goes to the first instance and the Find Next loops through the
matches after the activecell. I'm not sure how this would cope with 10
workbooks.
Alternatively you could list the locations of all the matches in a listbox
hidden and then use the Next to increment down the list.


"Cor van der Bliek" wrote:

In a macro I need to find a value in any of 10 workbooks. Anytime the value
from a userform is found the macro should halt a let me do anything outside
the macro just like the standard find/findnext function.
This works, except for the part, where the macro needs to halt and I can do
anything until I use the Next button on the userform

For i = 1 To 10
Workbooks(Files(i)).Activate
For j = 1 To Worksheets.Count
With Worksheets(j).Range("A1:A2000")
Set c = .Find(Nr, LookIn:=xlValues)
If Not c Is Nothing Then
If Nr = "" Then Exit Sub
firstAddress = c.Address
Worksheets(j).Activate
Range(firstAddress).Select
Do
Set c = .FindNext(c)
nextAddress = c.Address
Range(nextAddress).Select
something here!!
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next
Next

something here!!!
can be a Msgbox or Inputbox of course, but using any of these prevents me
from using the spreadsheet because the macro still has control. The standard
Excel find/findnext function stops and lets the user take control. How is
that done?

My userform is used vbModeless.

That's not what I'm looking for. It should behave jus like the program's
Find/Find next function. When an occurence of the key is found it stops and
let's me full control. Using the next key I can choose the next occurence and
so on.

"Libby" wrote:

Hi there,

I think perhaps you need to separate the Find and the Find Next.
The Find goes to the first instance and the Find Next loops through the
matches after the activecell. I'm not sure how this would cope with 10
workbooks.
Alternatively you could list the locations of all the matches in a listbox
hidden and then use the Next to increment down the list.


"Cor van der Bliek" wrote:

In a macro I need to find a value in any of 10 workbooks. Anytime the value
from a userform is found the macro should halt a let me do anything outside
the macro just like the standard find/findnext function.
This works, except for the part, where the macro needs to halt and I can do
anything until I use the Next button on the userform

For i = 1 To 10
Workbooks(Files(i)).Activate
For j = 1 To Worksheets.Count
With Worksheets(j).Range("A1:A2000")
Set c = .Find(Nr, LookIn:=xlValues)
If Not c Is Nothing Then
If Nr = "" Then Exit Sub
firstAddress = c.Address
Worksheets(j).Activate
Range(firstAddress).Select
Do
Set c = .FindNext(c)
nextAddress = c.Address
Range(nextAddress).Select
something here!!
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next
Next

something here!!!
can be a Msgbox or Inputbox of course, but using any of these prevents me
from using the spreadsheet because the macro still has control. The standard
Excel find/findnext function stops and lets the user take control. How is
that done?

My userform is used vbModeless.