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]
On Mon, 4 Jan 2010 14:14:01 -0800, ordnance1
wrote:
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("Employe e_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").Activat e
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