Passing a Parameter to the workbook open macro

[joel]

I have a workbook which has a workbook open event and asks the year to
run a report. Everything is great when opened individually. the
problem is the workbooks also get opened from a summary workbook that
opens 50 workbooks in the same folder. the question asking the year
keeps on coming up 50 times (once for each workbook)

I don't remember if it is possible to disable the workbook open macro.
It seem lie my only solution is to disable the workbook open and have
the users run the report manually. To have the report run automatically
when another workbook opens the macro would require adding a short macro
to the workbook excepting the year as a parameter and then calling the
report macro.

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=175816

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

[Dave Peterson]

You can disable the workbook_Open event (and all events!) by:

application.enableevents = false
set wkbk = workbooks.open(filename:="c:\...."
application.enableevents = true

You may want to remember that sequence when you close (workbook_beforeclose or
before you're doing anything with that workbook).

I think the easiest thing to do is to add a function to the workbook that's
opening (simple for a few, simple, but boring for 50!).

I used this in the workbook that was opening (book2.xls in my testing):

Option Explicit
Public myPublicVariable As Variant
Function SetTheVariable(myPassedVariable As Long)
myPublicVariable = myPassedVariable

Application.Run "'" & ThisWorkbook.Name & "'!ThisWorkbook.Workbook_Open"
'or I could remove the "Private" from the workbook_Open procedure
'and use Call
'Call ThisWorkbook.Workbook_Open
End Function

Then I had to modify the workbook_open event to know if it should use the passed
variable (now in the myPublicVariable) or ask the user:

Option Explicit
Sub Workbook_Open()
Dim myYear As Long
If IsEmpty(myPublicVariable) Then
'user opening the workbook
myYear = CLng(Application.InputBox(Prompt:="What Year", Type:=1))
Else
'opening from somewhere else!
myYear = myPublicVariable
End If

MsgBox myYear
End Sub

And in the workbook that's doing the opening, I used this:

Option Explicit
Sub testme999()
Dim wkbk As Workbook
Application.EnableEvents = False
Set wkbk = Workbooks.Open(Filename:="c:\my documents\excel\book2.xls", _
ReadOnly:=True)
Application.EnableEvents = True
Application.Run "'" & wkbk.Name & "'!SetTheVariable", 2010
End Sub

--

Dave Peterson