Search acrross Workbboks & Worksheets???
 

This pseudo code should get you started:

Dim lbox as Listbox
set lbox = Sheet1!Listbox1
for each wkbk in Workbooks
for each ws in wkbk.Worksheets

With ws.cells
Set c = .Find("Target", lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
lbox.AddItem c.Address(0,0,xlA1,True)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next ws
Next wkbk

--
Regards,
Tom Ogilvy

"Chris" wrote
in message ...
Hi
I hope someone can help me out there.
I need to SEARCH for a string across all open workbooks &
all sheets with in those workbook. Then list the location
of each find in a List Box. Once user select a location
from List it takes them to it.
Is it possible ????
I use XL2002 on Win2K

Thanks a million in advance

Regards
Chris

Search acrross Workbboks & Worksheets???
 

Hi Tom

Thank you so much for the help. I almost got it working
now. Please see below. I have used it on Userform2. It
works fine & [populate the List Box with all find within
all open workbooks & worksheet & it also is fine when
selecting any range with in active workbook, however it
fails & gives run time error "Acticate methood failed"
when I try to select something which is not the active
book.
Please advise what changes are required to ListBox1_Click
procedure to make it work

Thanks a million

Private Sub ListBox1_Click()

With Me.ListBox1

Range(ListBox1.Value).Activate
End With
End Sub

Private Sub UserForm_Initialize()

For Each wkbk In Workbooks
For Each WS In wkbk.Worksheets

With WS.Cells
Set c = .Find("289917-002", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Me.ListBox1.AddItem c.Address(0, 0, xlA1, True)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
Next WS
Next wkbk


End Sub

-----Original Message-----
This pseudo code should get you started:

Dim lbox as Listbox
set lbox = Sheet1!Listbox1
for each wkbk in Workbooks
for each ws in wkbk.Worksheets

With ws.cells
Set c = .Find("Target", lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
lbox.AddItem c.Address(0,0,xlA1,True)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
Next ws
Next wkbk

--
Regards,
Tom Ogilvy

"Chris" wrote
in message ...
Hi
I hope someone can help me out there.
I need to SEARCH for a string across all open workbooks
&
all sheets with in those workbook. Then list the
location
of each find in a List Box. Once user select a location
from List it takes them to it.
Is it possible ????
I use XL2002 on Win2K

Thanks a million in advance

Regards
Chris


.

Search acrross Workbboks & Worksheets???
 

Private Sub ListBox1_Click()

Application.Goto range(Listbox1.Value) , True
End Sub

if that doesn't work
Private Sub ListBox1_Click()
Dim rng as Range
set rng = Range(Listbox1.Value)
rng.parent.parent.Activate
rng.parent.Activate
rng.select
End Sub

--
Regards,
Tom Ogilvy


"Chris" wrote in message
...
Hi Tom

Thank you so much for the help. I almost got it working
now. Please see below. I have used it on Userform2. It
works fine & [populate the List Box with all find within
all open workbooks & worksheet & it also is fine when
selecting any range with in active workbook, however it
fails & gives run time error "Acticate methood failed"
when I try to select something which is not the active
book.
Please advise what changes are required to ListBox1_Click
procedure to make it work

Thanks a million

Private Sub ListBox1_Click()

With Me.ListBox1

Range(ListBox1.Value).Activate
End With
End Sub

Private Sub UserForm_Initialize()

For Each wkbk In Workbooks
For Each WS In wkbk.Worksheets

With WS.Cells
Set c = .Find("289917-002", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Me.ListBox1.AddItem c.Address(0, 0, xlA1, True)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
Next WS
Next wkbk


End Sub

-----Original Message-----
This pseudo code should get you started:

Dim lbox as Listbox
set lbox = Sheet1!Listbox1
for each wkbk in Workbooks
for each ws in wkbk.Worksheets

With ws.cells
Set c = .Find("Target", lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
lbox.AddItem c.Address(0,0,xlA1,True)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
Next ws
Next wkbk

--
Regards,
Tom Ogilvy

"Chris" wrote
in message ...
Hi
I hope someone can help me out there.
I need to SEARCH for a string across all open workbooks
&
all sheets with in those workbook. Then list the
location
of each find in a List Box. Once user select a location
from List it takes them to it.
Is it possible ????
I use XL2002 on Win2K

Thanks a million in advance

Regards
Chris


.

Search acrross Workbboks & Worksheets???
 

Wow!!!!
The 1st option worked right away..thanks a million

-----Original Message-----
Private Sub ListBox1_Click()

Application.Goto range(Listbox1.Value) , True
End Sub

if that doesn't work
Private Sub ListBox1_Click()
Dim rng as Range
set rng = Range(Listbox1.Value)
rng.parent.parent.Activate
rng.parent.Activate
rng.select
End Sub

--
Regards,
Tom Ogilvy


"Chris" wrote in
message
...
Hi Tom

Thank you so much for the help. I almost got it working
now. Please see below. I have used it on Userform2. It
works fine & [populate the List Box with all find within
all open workbooks & worksheet & it also is fine when
selecting any range with in active workbook, however it
fails & gives run time error "Acticate methood failed"
when I try to select something which is not the active
book.
Please advise what changes are required to
ListBox1_Click
procedure to make it work

Thanks a million

Private Sub ListBox1_Click()

With Me.ListBox1

Range(ListBox1.Value).Activate
End With
End Sub

Private Sub UserForm_Initialize()

For Each wkbk In Workbooks
For Each WS In wkbk.Worksheets

With WS.Cells
Set c = .Find("289917-002", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Me.ListBox1.AddItem c.Address(0, 0, xlA1,
True)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
Next WS
Next wkbk


End Sub

-----Original Message-----
This pseudo code should get you started:

Dim lbox as Listbox
set lbox = Sheet1!Listbox1
for each wkbk in Workbooks
for each ws in wkbk.Worksheets

With ws.cells
Set c = .Find("Target", lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
lbox.AddItem c.Address(0,0,xlA1,True)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
Next ws
Next wkbk

--
Regards,
Tom Ogilvy

"Chris" wrote
in message news:08bc01c3adee$4db07ee0
...
Hi
I hope someone can help me out there.
I need to SEARCH for a string across all open
workbooks
&
all sheets with in those workbook. Then list the
location
of each find in a List Box. Once user select a
location
from List it takes them to it.
Is it possible ????
I use XL2002 on Win2K

Thanks a million in advance

Regards
Chris


.



.