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

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