VB and VBA Users Source Code: Altering application properties from VB using Automation
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Altering application properties from VB using Automation
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Monday, October 01, 2001
Hits:
861
Category:
Visual Basic General
Article:
The following code shows a work around to change Excel's EnableEvents property from VB. Note, this property prevents things like Workbook_Open events firing when a workbook is opened. Option Explicit 'Purpose : Sets the Excel's EnableEvents property as this will not work through ' VB using automation. 'Inputs : oExcel The Excel application to change the EnableEvents property. ' bEventStatus The Value to set EnableEvents to. 'Outputs : Returns True if suceeded. 'Author : Andrew Baker 'Date : 28/Sep/2001 'Notes : Requires a reference to Microsoft Excel XX Object Library. 'Example : Function ExcelApplicationEvents(oExcel As Excel.Application, bEventStatus As Boolean) As Boolean Dim xlTempBook As Workbook On Error GoTo ErrFailed 'Create a temporary workbook Set xlTempBook = oExcel.Workbooks.Add 'Add a module xlTempBook.VBProject.VBComponents.Add 1 'vbext_ct_StdModule 'Add the code to change the application events With xlTempBook.VBProject.VBComponents(xlTempBook.VBProject.VBComponents.Count).CodeModule .InsertLines .CountOfLines + 1, "Public Sub SetEventsStatus(bEventsStatus as boolean)" .InsertLines .CountOfLines + 1, Chr$(9) & "Application.EnableEvents = bEventsStatus" .InsertLines .CountOfLines + 1, "End Sub" End With 'Call the code to change the application events oExcel.Run "'" & xlTempBook.Name & "'!SetEventsStatus", bEventStatus 'Close the workbook xlTempBook.Close False Set xlTempBook = Nothing Exit Function ErrFailed: Debug.Print "Error in ExcelApplicationEvents: " & Err.Description ExcelApplicationEvents = False End Function Private Sub Form_Load() Dim oExcel As Excel.Application 'Create an instance of Excel Set oExcel = New Excel.Application Debug.Print "Application Events are: " & oExcel.EnableEvents 'Turn application events off ExcelApplicationEvents oExcel, False Debug.Print "Application Events are: " & oExcel.EnableEvents 'Turn application events on ExcelApplicationEvents oExcel, True Debug.Print "Application Events are: " & oExcel.EnableEvents 'Unload Excel oExcel.Quit Set oExcel = Nothing End Sub
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
