Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to original workbook
My code below swithches to another open workbook and makes a change to it,
which works fine. What I need is to get back to the workbook that was active when the code started and finish running the remaining code. I can not enter a workbook name because it that name changes from year to year. 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.ScreenUpdating = True Application.EnableEvents = True BlankList: End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to original workbook
Is the code you posted in the workbook that you want to get back to? If so,
then ThisWorkbook.Activate Alternatively you can set a workbook variable to the active workbook before changing the active workbook then return to the initial workbook by using the variable. Dim wbInitial As Workbook Set wbInitial = ActiveWorkbook 'Your other code here where active workbook changes 'Reactivate the initial workbook wbInitial.Activate -- Regards, OssieMac "ordnance1" wrote: My code below swithches to another open workbook and makes a change to it, which works fine. What I need is to get back to the workbook that was active when the code started and finish running the remaining code. I can not enter a workbook name because it that name changes from year to year. 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.ScreenUpdating = True Application.EnableEvents = True BlankList: End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return to original location | Excel Programming | |||
return to original sheet | Excel Programming | |||
return to original sheet | Excel Programming | |||
Return to Original Workbook | Excel Programming | |||
Return WB to original state | Excel Programming |