View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
OssieMac OssieMac is offline
external usenet poster
 
Posts: 2,510
Default 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