Happy Codings - Programming Code Examples
Html Css Web Design Sample Codes CPlusPlus Programming Sample Codes JavaScript Programming Sample Codes C Programming Sample Codes CSharp Programming Sample Codes Java Programming Sample Codes Php Programming Sample Codes Visual Basic Programming Sample Codes


Visual Basic Programming Code Examples

Visual Basic > Applications VBA Code Examples

Altering application properties from VB using Automation

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
Altering application properties from VB using Automation 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. '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