Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access an open workbook without making it active
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access an open workbook without making it active
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Range from Open Workbook to Active Workbook | Excel Programming | |||
Access current active workbook from DLL | Excel Programming | |||
Making an open workbook active | Excel Programming | |||
Get range value active workbook on open add-in | Excel Programming | |||
How do I make an open workbook the active workbook | Excel Programming |