Prevent a workbook from displaying

[ordnance1]

The code below switches to another workboo (which is already open) and runs
some code on that workbook to edit an employees name. Is there any way of
doing this without EmployeeList.xlsmactually displaying on the screen?


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("Employee_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").Activate
rng1.Select

Else
MsgBox sStr & " not found"
End If

Application.Run "EmployeeList.xlsm!UpdateName"
Application.ScreenUpdating = True

EmployeeList.Show

ThisWorkbook.Activate

Application.ScreenUpdating = True
Application.EnableEvents = True

BlankList:

End Sub

 

[Barb Reinhardt]

I'm guessing you don't really need to activate and select at the places
marked below. I also don't know what's in your called sub, so don't know if
something needs to be changed there. In general, you can live without
Activate and Select in a lot of cases if the code is written properly. It
also runs faster without Activate and Select.

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
Dim myWS as excel.Worksheet

set myWS = Workbooks("EmployeeList.xlsm").Worksheets("Employee_List")
Set rng =myWS.Cells
sStr = Me.TextBox1.Value

'I'd change Range("IV65536") to this
'myWS.Cells(myws.rows.count, myws.columns.count)

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 '<~~is this needed?
ActiveWorkbook.Worksheets("Employee_List").Activate '<~~Needed?
rng1.Select '<~~~Needed?

Else
MsgBox sStr & " not found"
End If

Application.Run "EmployeeList.xlsm!UpdateName"
Application.ScreenUpdating = True

EmployeeList.Show

ThisWorkbook.Activate '<~~~won't need this if previous is removed.

Application.ScreenUpdating = True
Application.EnableEvents = True

BlankList:

End Sub