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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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


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


Thank yo

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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...

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

:) 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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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...

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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!

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 :D
Didn't know it was his app. But yes, finally, problem solved

--
Message posted from http://www.ExcelForum.com

Well... Alrighty then. Gald you got it all sorted out! See you later
- Piku

--
Message posted from http://www.ExcelForum.com

FHerrera, i am going through the same thing as you. at some point (
dont remember when since i modify my code all the time) th
workbook_open event ceased to work. all other events do work, but it i
very important that my workbook open works because i need to check i
the user has a license to use the sheet, otherwise close it. my code i
very long now (have like 10 userforms and some other 10 module
including functions i wrote), so its a bit impossible to narrow it dow
i'd take forever.
have you found out a quick way to fix this or do i have to go throug
all that?
thanks!

--
Message posted from http://www.ExcelForum.com