INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- 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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...The level of expertise is awesome. The nature in which people respond is professional helpful and not the least condescending. I can't say that for most forums..."
Geography
Where in the world do Tek-Tips members come from?
|
VBA Visual Basic for Applications (Microsoft) FAQ
|
VBA How To
|
Understanding events. III: Some useful stuff
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
|
|
 |
|
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:
Talk To Other Members- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close
