Prevent a workbook from displaying
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("Employe e_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").Activat e '<~~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
--
HTH,
Barb Reinhardt
"ordnance1" wrote:
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("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
EmployeeList.Show
ThisWorkbook.Activate
Application.ScreenUpdating = True
Application.EnableEvents = True
BlankList:
End Sub
|