×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here
Home > Forums > Programmers > Languages > VBA Visual Basic for Applications (Microsoft) > FAQs

VBA Visual Basic for Applications (Microsoft) FAQ

VBA How To

Understanding events. III: Some useful stuff by combo
faq707-4975
Posted: 23 Mar 04

The whole FAQ consists of four parts
the basics
automation events (this one)
some useful stuff
a substitute for control arrays in VBA

As it was told, events can be handled only in class modules, for instantiated objects. But no one told that we canÆt use existing object modules, no one told that we have to use default object module event handlers.
Some examples:
1.
To trap application events (still in excel) just enough in ThisWorkbook module:

Private WithEvents App As Application
æ now App available in the left combo

Private Sub Workbook_Open()
Set Me.App = Application
End Sub

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
MsgBox "application event: " & Wb.Name
End Sub
simpler?

2.
Selective switching of events instead of conditional handling. In some cases it is a good replacement for rather bold Application.EnableEvents, touching all excel events. In ThisWorkbook module (can also be in Sheet1 module):

Public WithEvents Wks1 As Worksheet
Public WithEvents Wks2 As Worksheet

Private Sub Wks1_Activate()
æ event code
End Sub

Private Sub Wks2_Calculate()
æ event code
End Sub
and somewhere else in the code:
Set Wks1=Sheet1     æ switch Activate event for Sheet1
. . . .
Set Wks2=Sheet1     æ  switch Calculate event for Sheet1
Set Wks1=Nothing   æ switch off Activate event for Sheet1
. . . .
Set Wks2=Nothing   æ switch off Calculate event for Sheet1[/color][/tt]

3.
Excel as a painting tool (?):
A small userform with:

[tt][color blue]Private WithEvents wks As Worksheet

Private Sub UserForm_Initialize()
Set wks = ActiveSheet
End Sub

Private Sub UserForm_Terminate()
Set wks = Nothing
End Sub

Private Sub wks_SelectionChange(ByVal Target As Range)
Target.Interior.ColorIndex = Rnd * 56
End Sub
and a standard module with:
Sub ShowModelessForm()
UserForm1.Show vbModeless
End Sub

Now make active sheetÆs cells square, set zoom to 50% or 25% and run ShowModelessForm. Interesting?

4.
You can use full power of automation. Have you considered referencing PowerPoint library in excel and running it together with workbook this way? ThisWorkbook module:

Private WithEvents ppApp As PowerPoint.Application

Private Sub ppApp_PresentationNewSlide(ByVal Sld As PowerPoint.Slide)

End Sub

Private Sub Workbook_Open()
Set ppApp = New PowerPoint.Application
ppApp.Visible = msoTrue
End Sub

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close