I have two workbooks Able and Baker. When I click on a cell in Able a
UserForm is displayed. On this UserForm I click on a name (from a list of
names) in ListBox1. Then I want to click on a button labeled Edit which will
take me to WorkBook Baker (worksheet called Names) and select the cell which
contains the name I selected in ListBox1. I just can not figure out what
code to assign to the Edit button to make this happen.

Patrick Simonds wrote:
I have two workbooks Able and Baker. When I click on a cell in Able a
UserForm is displayed. On this UserForm I click on a name (from a list of
names) in ListBox1. Then I want to click on a button labeled Edit which will
take me to WorkBook Baker (worksheet called Names) and select the cell which
contains the name I selected in ListBox1. I just can not figure out what
code to assign to the Edit button to make this happen.

Is the name you select in listbox1 unique to Names and does it have a
specific column?

To open the new workbookuse:

Sub {insert name of command button here} ()

Workbook("Baker").Sheets("Names").Activate
Range ("A1").select

Then we need to work out what coulun we are searching in and do an
index or match or vlookup or something similar in code. Then decide
what you want to do once you have found the cell, just got to the cell
or do something with another variable in the row?

Regards

Stopher

Private Sub Edit_Click()
Dim rng as Range, rng1 as range
if me.Listbox1.ListIndex < -1 then
set rng = Workbooks("Baker").Worksheets("names").Cells
sStr = me.Listbox1.Value
set rng1 = rng.Find(What:=sStr, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
MsgBox sStr & " found at " & rng.Address
Else
MsgBox sStr & " not found"
End If
End Sub


You can modify
set rng = Workbooks("Baker").Worksheets("names").Cells

to look at a smaller range and
LookAt:=xlWhole to xlPart if the name is not the only value in the cell.

change LookIn:=xlformulas to xlValues if the names are produced by formulas.

--
Regards,
Tom Ogilvy


"Patrick Simonds" wrote in message
...
I have two workbooks Able and Baker. When I click on a cell in Able a
UserForm is displayed. On this UserForm I click on a name (from a list of
names) in ListBox1. Then I want to click on a button labeled Edit which
will take me to WorkBook Baker (worksheet called Names) and select the cell
which contains the name I selected in ListBox1. I just can not figure out
what code to assign to the Edit button to make this happen.

Thanks for your help, but I need it to make WorkBook Baker active and to
select the cell that matches the search criteria.


"Tom Ogilvy" wrote in message
...
Private Sub Edit_Click()
Dim rng as Range, rng1 as range
if me.Listbox1.ListIndex < -1 then
set rng = Workbooks("Baker").Worksheets("names").Cells
sStr = me.Listbox1.Value
set rng1 = rng.Find(What:=sStr, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
MsgBox sStr & " found at " & rng.Address
Else
MsgBox sStr & " not found"
End If
End Sub


You can modify
set rng = Workbooks("Baker").Worksheets("names").Cells

to look at a smaller range and
LookAt:=xlWhole to xlPart if the name is not the only value in the cell.

change LookIn:=xlformulas to xlValues if the names are produced by
formulas.

--
Regards,
Tom Ogilvy


"Patrick Simonds" wrote in message
...
I have two workbooks Able and Baker. When I click on a cell in Able a
UserForm is displayed. On this UserForm I click on a name (from a list of
names) in ListBox1. Then I want to click on a button labeled Edit which
will take me to WorkBook Baker (worksheet called Names) and select the
cell which contains the name I selected in ListBox1. I just can not figure
out what code to assign to the Edit button to make this happen.

Private Sub Edit_Click()
Dim rng as Range, rng1 as range
Dim sStr as String
if me.Listbox1.ListIndex < -1 then
set rng = Workbooks("Baker.xls").Worksheets("names").Cells
sStr = me.Listbox1.Value
set rng1 = rng.Find(What:=sStr, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
Workbooks("Baker.xls").Activate
ActiveWorkbook.Worksheets("Names").Activate
rng1.Select
' or Application.Goto rng1, true

Else
MsgBox sStr & " not found"
End If
End if
End Sub


--
Regards,
Tom Ogilvy



"Patrick Simonds" wrote in message
...
Thanks for your help, but I need it to make WorkBook Baker active and to
select the cell that matches the search criteria.


"Tom Ogilvy" wrote in message
...
Private Sub Edit_Click()
Dim rng as Range, rng1 as range
if me.Listbox1.ListIndex < -1 then
set rng = Workbooks("Baker").Worksheets("names").Cells
sStr = me.Listbox1.Value
set rng1 = rng.Find(What:=sStr, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
MsgBox sStr & " found at " & rng.Address
Else
MsgBox sStr & " not found"
End If
End Sub


You can modify
set rng = Workbooks("Baker").Worksheets("names").Cells

to look at a smaller range and
LookAt:=xlWhole to xlPart if the name is not the only value in the
cell.

change LookIn:=xlformulas to xlValues if the names are produced by
formulas.

--
Regards,
Tom Ogilvy


"Patrick Simonds" wrote in message
...
I have two workbooks Able and Baker. When I click on a cell in Able a
UserForm is displayed. On this UserForm I click on a name (from a list of
names) in ListBox1. Then I want to click on a button labeled Edit which
will take me to WorkBook Baker (worksheet called Names) and select the
cell which contains the name I selected in ListBox1. I just can not
figure out what code to assign to the Edit button to make this happen.

Thank you below you will see my final code. I could not get it to work by
using the ListBox Index but was able to get around that by creating a
TextBox which contained the ListBox value. Was wondering if you could
explain why last 2 lines of my code do not work. I should explain that when
I search out the TextBox1 value in the other WorkBook it causes a macro to
run allowing me to edit the selected name.


Private Sub Edit_Name_Click()

Unload EmployeeList

Dim rng As Range, rng1 As Range
Dim sStr As String

Set rng = Workbooks("EmployeeList.xls").Worksheets("Employee _List").Cells
sStr = Me.TextBox1.Value

Set rng1 = rng.Find(What:=sStr, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
Workbooks("EmployeeList.xls").Activate
ActiveWorkbook.Worksheets("Employee_List").Activat e
rng1.Select

Else
MsgBox sStr & " not found"
End If

Workbooks("Vacation - Leave Book Master.xls").Activate
EmployeeList.Show

End Sub

I am guessing the code is in the code module for the userform named
EmployeeList.

try changing

Unload EmployeeList

to
EmployeeList.Hide

--
Regards,
Tom Ogilvy


"Patrick Simonds" wrote in message
...
Thank you below you will see my final code. I could not get it to work by
using the ListBox Index but was able to get around that by creating a
TextBox which contained the ListBox value. Was wondering if you could
explain why last 2 lines of my code do not work. I should explain that
when I search out the TextBox1 value in the other WorkBook it causes a
macro to run allowing me to edit the selected name.


Private Sub Edit_Name_Click()

Unload EmployeeList

Dim rng As Range, rng1 As Range
Dim sStr As String

Set rng =
Workbooks("EmployeeList.xls").Worksheets("Employee _List").Cells
sStr = Me.TextBox1.Value

Set rng1 = rng.Find(What:=sStr, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
Workbooks("EmployeeList.xls").Activate
ActiveWorkbook.Worksheets("Employee_List").Activat e
rng1.Select

Else
MsgBox sStr & " not found"
End If

Workbooks("Vacation - Leave Book Master.xls").Activate
EmployeeList.Show

End Sub

Thanks

The code below is on the UserForm EmployeeList. When I click on the Edit
Button (which runs the code below) it takes me to the other WorkBook
(EmployeeList.xls) and runs code on that WorkBook which allows one to edit
the selected name. Then I need to come back to the original WorkBook
(Vacation - Leave Book Master.xls) and UserForm EmployeeList. Following your
advice I changed Unload EmployeeList to EmployeeList.Hide but it still did
not return me to Vacation - Leave Book Master.xls


"Tom Ogilvy" wrote in message
...
I am guessing the code is in the code module for the userform named
EmployeeList.

try changing

Unload EmployeeList

to
EmployeeList.Hide

--
Regards,
Tom Ogilvy


"Patrick Simonds" wrote in message
...
Thank you below you will see my final code. I could not get it to work by
using the ListBox Index but was able to get around that by creating a
TextBox which contained the ListBox value. Was wondering if you could
explain why last 2 lines of my code do not work. I should explain that
when I search out the TextBox1 value in the other WorkBook it causes a
macro to run allowing me to edit the selected name.


Private Sub Edit_Name_Click()

Unload EmployeeList

Dim rng As Range, rng1 As Range
Dim sStr As String

Set rng =
Workbooks("EmployeeList.xls").Worksheets("Employee _List").Cells
sStr = Me.TextBox1.Value

Set rng1 = rng.Find(What:=sStr, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
Workbooks("EmployeeList.xls").Activate
ActiveWorkbook.Worksheets("Employee_List").Activat e
rng1.Select

Else
MsgBox sStr & " not found"
End If

Workbooks("Vacation - Leave Book Master.xls").Activate
EmployeeList.Show

End Sub

Since I don't see any code in the code you posted that "runs code on that
WorkBook which allows one to edit
the selected name." would guess that you are using the selectionChange
event. If so, that sounds like a bad idea to me since you want to be
interactive.

I would move the code for that action into a general module in Baker.xls (if
it must still support selection change). Let's say you name it
Sub UpdateName()

and you call that from selection Change instead:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UpdateName
End Sub


If you use the Target object in your code, then in UpdateName add a line at
the top like

Set Target = ActiveCell

then your userform code would be

Private Sub Edit_Name_Click()

On Error goto ErrHandler
EmployeeList.Hide

Dim rng As Range, rng1 As Range
Dim sStr As String

Set rng = Workbooks("EmployeeList.xls").Worksheets("Employee _List").Cells
sStr = Me.TextBox1.Value

Set rng1 = rng.Find(What:=sStr, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
Application.EnableEvents = False
Workbooks("EmployeeList.xls").Activate
ActiveWorkbook.Worksheets("Employee_List").Activat e
rng1.Select
Application.Run "Employee_List.xls!UpdateName"

Else
MsgBox sStr & " not found"
End If

ErrHandler:
Workbooks("Vacation - Leave Book Master.xls").Activate
Application.EnableEvents = True
EmployeeList.Show

End Sub

--
Regards,
Tom Ogilvy


"Patrick Simonds" wrote in message
...
Thanks

The code below is on the UserForm EmployeeList. When I click on the Edit
Button (which runs the code below) it takes me to the other WorkBook
(EmployeeList.xls) and runs code on that WorkBook which allows one to edit
the selected name. Then I need to come back to the original WorkBook
(Vacation - Leave Book Master.xls) and UserForm EmployeeList. Following
your advice I changed Unload EmployeeList to EmployeeList.Hide but it
still did not return me to Vacation - Leave Book Master.xls


"Tom Ogilvy" wrote in message
...
I am guessing the code is in the code module for the userform named
EmployeeList.

try changing

Unload EmployeeList

to
EmployeeList.Hide

--
Regards,
Tom Ogilvy


"Patrick Simonds" wrote in message
...
Thank you below you will see my final code. I could not get it to work
by using the ListBox Index but was able to get around that by creating a
TextBox which contained the ListBox value. Was wondering if you could
explain why last 2 lines of my code do not work. I should explain that
when I search out the TextBox1 value in the other WorkBook it causes a
macro to run allowing me to edit the selected name.


Private Sub Edit_Name_Click()

Unload EmployeeList

Dim rng As Range, rng1 As Range
Dim sStr As String

Set rng =
Workbooks("EmployeeList.xls").Worksheets("Employee _List").Cells
sStr = Me.TextBox1.Value

Set rng1 = rng.Find(What:=sStr, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
Workbooks("EmployeeList.xls").Activate
ActiveWorkbook.Worksheets("Employee_List").Activat e
rng1.Select

Else
MsgBox sStr & " not found"
End If

Workbooks("Vacation - Leave Book Master.xls").Activate
EmployeeList.Show

End Sub