Auto Opening for Macro

[Guest]

I am trying to have a Macro run automatically when a sheet is opened in
excel. Also would I need to put this code in for each spreadsheet designed
for it to run or can just the main sheet trigger the macro to run on all
sheets published or where should I put Sub Auto_Open() in the code below:

Option Explicit
Public Declare Function SetTimer Lib "user32" ( _
ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerfunc As Long) As Long

Public Declare Function KillTimer Lib "user32" ( _
ByVal hwnd As Long, _
ByVal nIDEvent As Long) As Long

'==========Public Declarations ==============================
Public TimerID As Long 'Turn On and Off with this ID
Public TimerActive As Boolean 'Is the timer active
Public Const tmMin As Long = 2 'Min time allowed
Public Const tmDef As Long = 5 'Default if min set low
'============================================================

Public Sub ActivateMyTimer(ByVal Sec As Long)
Sec = Sec * 1000
If TimerActive Then Call DeActivateMyTimer

On Error Resume Next
TimerID = SetTimer(0, 0, Sec, AddressOf Timer_CallBackFunction)
TimerActive = True

End Sub

Public Sub DeActivateMyTimer()
KillTimer 0, TimerID
End Sub

Sub Timer_CallBackFunction(ByVal hwnd As Long, ByVal uMsg As Long, ByVal
idevent As Long, _
ByVal Systime As Long)

Application.SendKeys "~", True
If TimerActive Then Call DeActivateMyTimer

End Sub

Function TmMsgBox(sMsg As String, Btn As VbMsgBoxStyle, Optional ShowFor As
Long, _
Optional sTitle As String) As VbMsgBoxResult

If sTitle = "" Then sTitle = Application.Name
If ShowFor < tmMin Then ShowFor = tmDef
ActivateMyTimer ShowFor
TmMsgBox = MsgBox(sMsg, Btn, sTitle)
DeActivateMyTimer

End Function

Sub aTest()
Dim Answer

Answer = TmMsgBox("You have until 12:01 Thursday OK!", vbYesNo +
vbDefaultButton1, , "Data Entry check")

MsgBox Answer

End Sub

 

[paul.robinson]

Hi
Your mail is confusing.
1. You say "..when a sheet is opened in Excel". Do you mean a sheet is
activated, or do you mean a workbook is opened?
2. You say "I'm trying to have a macro run automatically..". You list
several macros. Which one do you want to run automatically?
3. The Auto_Open sub can only be in the ThisWorkbook code module. You
do not list an Auto_Open macro in your mail.
4. your mail talks about sheets, but I suspect you mean workbooks?

Code can be made to respond to certain events. If you want it to run
when the workbook opens you will put it in the ThisWorkbook code
module. If you want it to run when sheets are activated, you might put
it in the ThisWorkbook or particular sheet modules. If you want it to
run for different workbooks opening or sheets being activated, which
are not in the workbook containing the code, you will need a class
module.
Can you specify which of these scenarios you need?
regards
Paul

 

[moon]

Another thing is... Why are you using an API-call if VBA offers its own
Timer? Now to me it seems to be a little overkill.
This simply works:

Public Sub Test()
MsgBox "Wait 10 seconds..."
Wait (10)
MsgBox "Toodles"
End Sub

Public Sub Wait(ByVal nSeconds As Integer)
Dim startTime
startTime = Timer
While Timer < startTime + nSeconds
DoEvents
Wend
End Sub

 

[Guest]

Hi Paul,

Please see my respond to your questions below and I think I will probaly
need a class module.

Thanks Norm

Hi
Your mail is confusing.
1. You say "..when a sheet is opened in Excel". Do you mean a sheet is
activated, or do you mean a workbook is opened?

A:When a work book is opened containing several sheets in it.

2. You say "I'm trying to have a macro run automatically..". You list
several macros. Which one do you want to run automatically? A: > > Sub aTest()

3. The Auto_Open sub can only be in the ThisWorkbook code module. You
do not list an Auto_Open macro in your mail.

? How is that done?

 

[paul.robinson]

Hi
I'm still not sure if you want code to run when a workbook is opened
or when a sheet is activated. That said, I hope this might help:

You can put all your code in a normal code module.
1. If you want aTest to run when you open the workbook containing the
code (which VBA calls ThisWorkbook) then double click the Thisworkbook
code module in the VB editor. Where it says (General) change it to
Workbook and the WorkBook_Open sub will appear. Insert ATest like this

Private Sub Workbook_Open()
Call aTest
End Sub

Now aTest will run when ThisWorkBook is opened.
2. If you want aTest to run when a particular worksheet in
Thisworkbook is active then doubleclick on the code module for that
sheet (suppose it is sheet1), change (General) to Worksheet and select
Activate from the other dropdown. You can now create this:

Private Sub Worksheet_Activate()
Call aTest
End Sub

This will ONLY run when sheet1in Thisworkbook is activated.

3. If you want aTest to run when any worksheet in Thisworkbook is
activated then doubleclick on the ThisWorkbook code module , change
(General) to Worksheet and select SheetActivate from the other
dropdown. You can now create this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call aTest
End Sub

This will run when any sheet in Thisworkbook is activated. This sub
can also be used to restrict the running of aTest to particular sheets
e.g.
If Sh.Name<>"DataSheet" Then
Call aTest
End if

4. If you want to run aTest when any sheet is activated in a workbook
that does not contain the code (i.e. is not Thisworkbook) then you
need a class module to trap application events (like SheetActivate).
The following is taken from Bullen et al "Excel 2002 VBA";
(a) Insert a class module into your project (Insert, Class Module in
VB Editor). Suppose it is called Class1.
(b) At the top of the blank module type
Public WithEvents xlApp as Application

(c) Now replace (General) in the top left dropdown with xlApp and you
will see the right hand dropdown populate with Events for the
application. Choose SheetActivate. Now you can write

Private Sub xlApp_SheetActivate(ByVal Sh As Object)
Call aTest
End Sub

(d) You must now make an instance of your class module and activate
it. In a general code module type
Public xlApplication As New Class1

and in the Workbook_Open event of ThisWorkbook (see 1 above) type
Private Sub Workbook_Open()
Set xlApplication.xlApp = Application
End Sub

When Thisworkbook is open, then aTest will fire if any sheet is
activated in any workbook.

NOTE: Any error in code execution will destroy the instance of the
class module and things will stop working. You will need to close and
reopen ThisWorkbook.
If you require this level of sheet activation, it would also make
sense to have Thisworkbook as an AddIn. Click on the ThisWorkbook
module and in the properties section set IsAddin to True. In the Excel
front end (not the editor) go to Tools, AddIns and browse to the file.
Now when you open Excel your workbook with the code will open without
you seeing it and aTest will fire when you activate sheets on other
open workbooks.

regards
Paul