Access an open workbook without making it active
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("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
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
--
Regards,
OssieMac
|