Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent a workbook from displaying
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent UserForm from displaying | Excel Programming | |||
prevent second workbook opening | Excel Programming | |||
How to prevent a formula from displaying ? | Excel Discussion (Misc queries) | |||
Prevent a workbook being closed with the X | Excel Programming | |||
HOW DO I PREVENT HIDDEN CELLS FROM DISPLAYING WHEN I COPY | Excel Discussion (Misc queries) |