wrkbook_Open event, Help??

[Fherrera]

Problems w/ Workbook_Open event
Hi, my events work. Just not the

workbook_open, auto_open, or workbook_activate ones that i'm trying t
use to accomplish a task. Other events work just fine though...such a
workbook_beforeclose()

When I open the workbook nothing happens (other than ii openning).
put some msgbox commands in the workbook_open() but it doesn't ru
them.. Could this be because I have the entire workbook protected (Rea
only). Has anyone experienced this or knows what may be causing it?

Thank

 

[pikus]

Can we see your code? Please also confirm where the code is located i
your project. - Piku

 

[Fherrera]

Sure. I have the events procedure's in the "ThisWorkbook" portion o
the the VBA project. I'd like to mention that the BeforeClose even
seems to work fine.


Code
-------------------


Private Sub Workbook_Open()

MsgBox "Workbook opening"
' Application.EnableEvents = False

' Application.StatusBar = "Initializing workbook... Please standby"

' Call Worksheets("Sheet1").refreshFooterSheet1 'refresh footer
MsgBox "Workbook opening2"
' Call Worksheets("Sheet1").Sheet1Protection 'redo protection on Unit 1

' Worksheets("Unit 1").Protect password:="password", userinterfaceonly:=True

' Application.StatusBar = False

' Application.EnableEvents = True

End Sub

-------------------


As you can see i've commented out most of the procedure calls as I wa
afraid they may be causing a problem. I left only the MsgBox to attemp
to "see" it. But no dice. ALso, i'm not sure how to test this othe
than closing the workbook and opening it again. (if i just run th
procedure it works fine, but what I want is it to work when th
workbook is open).

The entire workbook is protected with the read-only option. Could thi
have an effect? I don't think calling the procedures in the Sheet
code is a problem (it was an attempt to keep specific code to it
respective sheet) since I have a beforeClose event that works and look
like:


Code
-------------------

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call Worksheets("Sheet1").removeSheet1ToolBar
Application.EnableEvents = True


End Sub

 

[pikus]

The Read Only thing should not affect this. The code works fine for me
but I'm looking at your use of Application.EnableEvents and wondering
though it seems to be implemented correctly, if that could be causin
your problems. I'd say you should try running just the statemen
'Application.EnableEvents = True' immediately before you reopen th
workbook. Try that and let me know what happens... - Piku

 

[Fherrera]

Unfortunately, it still does nothing.

Basically, I have this now:


Code
-------------------

Private Sub Workbook_Open()

Application.EnableEvents = True
MsgBox "Workbook opening"
End Sub

-------------------


Hrm, other things I have the code locked so you need a pw to view it..
The sheet itself isn't protected yet, I will do that after but I doub
this has any effect. When I CLOSE the workbook THAT event works fine.

Is there an option somewhere to ensure that EnableEvents is True (
mean before this event is 'executed')?? I couldn't find one myself bu
i read somewhere it is set to TRue by default...

Thank

 

[pikus]

It is true by default, but what I'm shooting for is for you to enabl
events from another workbook fefore you open that one. That should b
the last thing you do before opening it. - Piku

 

[Fherrera]

I see I see. I created a new workbook called test2.xls And jus
basically copied the open event into it. If I close the first workboo
(the one in question) and open test2.xls the open event runs. If th
workbook in question is already opened and I open test2.xls, the ope
event doesn't work. (workbook_open)

Now, I had test2.xls open and I made sure the EnableEvents was tru
using the immediate window to display it as well as setting it.
opened the workbook in question and the open even does not run.

So it must be something in the workbook, since it's even causing othe
workbooks_open event not to fire!! But what??!?!. grrr

 

[Fherrera]

Also, I commented out EVERY EnableEvents = False in the code. But stil
no dice... What could be causing this?

I copied everything and put it into a new workbook (just one sheet) an
tried it again but still nothing...

 

[pikus]

Alright, try closing Excel and then reopening the file..

 

[Fherrera]

Tried that a few times already. But no dice. I first thought th
file might be corrupted because I had read that debugging, writing
etc. VBA code sometimes causes this and to just copy it all into a ne
workbook and you'd be ok. But that didn't help either

 

[pikus]

And check your security level. I should have thought about that first..
Go to Tool-->Macro-->Security... and set it to "LOW" - Piku

 

[Fherrera]

That's not availavle in Excel 97. I did change the 'check for macr
virus protection' Option in the TOols-options-general tab Whic
basically just stopped asking if i want to enable macro's... But i
still does not work...

BTW, thanks for all the help thus far pikus

 

[pikus]

Any time! Would you be cool with posting a copy of your workbook? I'l
try it on my machine and see if that makes a difference. - Piku

 

[Fherrera]

I've narrowed it down..... I copied and pasted bits of code a bit at
time until I narrowed it down that it was the code in Module 1 that wa
causing this to not occur. Started taking out method's at a time no
and it was this Function I use in cells that caused the problem: (wh
it causes it I have no clue, maybe there's an error the syntax checke
etc. didn't find)


Code
-------------------

Function hoursUnavailable(hours As Long, d1 As Date, d3 As Date, d2 As Date, d4 As Date)
' hours is the hours of the down time.
' d1 is the date of when to begin calculating hours "yyyy/mm/dd"
' d3 is the time of the date for d1, "hh:mm"
' d2 is the date of when to end calculating hours "yyyy/mm/dd"
' d4 is the time of the date for d1, "hh:mm"
'
' Function returns a percentage of the hours/(difference between two dates in hours)
'
' use long as these hours are not > max(long)
'
Dim timeDiff, t1, t2, dayHours, timeHours, timeMinutes, totalHours As Long
Dim fracMinutes As Single

dayHours = (DateDiff("d", d1, d2) - 1) * 24

If Minute(d3) > 0 Then
t1 = 24 - Hour(d3) - 1
t2 = 60 - Minute(d3)
Else
t1 = 24 - Hour(d3)
t2 = 0
End If

timeHours = t1 + Hour(d4)
timeMinutes = t2 + Minute(d4)

totalHours = dayHours + timeHours

fracMinutes = timeMinutes / 60

timeDiff = totalHours + fracMinutes

'Debug.Print hours & " - " & timeDiff


If timeDiff = 0 Then
hoursUnavailable = "DIVIDE BY ZERO ERROR"
Else
hoursUnavailable = (hours / timeDiff)
End If

End Function

-------------------


Obviously not the nicest piece of code but it worked (Atleast it di
before) So why would it be causing this probelm of prevent th
Workbook_open() event to not fire...

 

[pikus]

I wish I could be more help. The last thing I can think of is this:

http://www.appspro.com/utilities/cleaner.asp

You can try running it on the workbook and see if it helps... - Piku

 

[Fherrera]

You've used this utility before? Works as expected?

I don't understand, I tried compiling the code it does so fine... If
simply delete that function from the module1 module the open even
works... But I need that function... I'll try changing it up somehow
suppose...something ABOUT it must be causing this...

Thanks again for your hel

 

[pikus]

Oh yeah. That's a good site to explore a little more. Lots of goo
stuff there. That and http://CPearson.com/
Chip Pearson, whose site that is, posts to this newsgroup alot. He'
pretty amazing. - Piku

 

[Fherrera]

Well, can't even do it if I wanted too. Tried installing but the
remember I work at one of those places that doesn't let you instal
anything and has almost everything locked down...

Ugh. Stupid function

 

[pikus]

I feel your pain. I have the same type of thing here at work too.
Works at home though..

 

[Fherrera]

Oh my goodness.... I've spent quite a few hours on this and as it turn
out, the fact that the function was called 'hoursUnavailable' cause
some sort of conflict (who knows where?!) but by simply changing th
functions name to hoursUnavail in the Module it works...(th
workbook_open() event firing that is)

Thanks for your time Pikus!

RE: C. Pearson. Yup i've been to his site, MrExcel, Excel-Tips, etc.
tons of them since i started doing this excel stuff last Monday
Didn't know it was his app. But yes, finally, problem solved