Vlookup on second WorkBook

[Patrick Simonds]

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.

 

[Stopher]

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

 

[Tom Ogilvy]

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.

 

[Patrick Simonds]

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]

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

 

[Patrick Simonds]

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").Activate
rng1.Select

Else
MsgBox sStr & " not found"
End If

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

End Sub

 

[Tom Ogilvy]

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

try changing

Unload EmployeeList

to
EmployeeList.Hide

 

[Patrick Simonds]

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]

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").Activate
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