Access an open workbook without making it active

[ordnance1]

Is there any way to run my code below without actually making workbook
Employee_List active? It is open in the background.

Private Sub Edit_Name_Click()

If ListBox1.Value = " " Then GoTo BlankList

Unload EmployeeList

Application.ScreenUpdating = False
Application.EnableEvents = False

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

Set rng = Workbooks("EmployeeList.xlsm").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.xlsm").Activate
ActiveWorkbook.Worksheets("Employee_List").Activate
rng1.Select

Else
MsgBox sStr & " not found"
End If

Application.Run "EmployeeList.xlsm!UpdateName"
Application.ScreenUpdating = True

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

Application.EnableEvents = True

BlankList:

End Sub

 

[Chip Pearson]

You very rarely need to make anything (Range, Sheet, Workbook) active
in order to use it. Just declare a variable of type Workbook, set it
to the appropriate workbook, and use the variable anywhere you need to
reference the workbook. E.g.,

Dim WB As Workbook
Set WB = Workbooks("MyBook.xls")
'...... more code
WB.Worksheets("Sheet1").Range("A1").Value = 1234

Here, the workbook WB is referenced regardless of what workbook might
be active. You can do similar things with worksheets and ranges.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[OssieMac]

I wonder if Chip's answer is really what you are looking for.

Because you select rng1 in the workbook suggests that you are using the
selection in the called routine and are having a problem passing rng1 to the
called routine without selecting the workbook, worksheet and range.

Also I wonder if your called routine is in the correct place in your code
because I should think that you only want to call the routine if rng1 is
found. The following code passes rng1 to the called routine. However, you
need to include the parameter in the called routine sub. See end of this post
for how to do this.

I have tested the following code and it works.

Private Sub Edit_Name_Click()

If ListBox1.Value = " " Then GoTo BlankList

Unload EmployeeList

Application.ScreenUpdating = False
Application.EnableEvents = False

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

Set rng = Workbooks("EmployeeList.xlsm").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.Run "EmployeeList.xlsm!UpdateName", rng1
Else
MsgBox sStr & " not found"
End If


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

Application.EnableEvents = True

BlankList:

End Sub


Called sub with parameter included. This is the sub I tested with and it
inserts "Testing" in the correct cell without activating the workbook or
worksheet.

Sub UpdateName(rng1 As Range)
rng1.Offset(0, 1) = "Testing"
End Sub