Workbook_Open, Problem with event

[DavidCarrJaxFL]

I'll preface this by saying that I'm in no way trying to make history for having the weirdest Excel problem...

I have a rather complicated time tracking spreadsheet that contains 12 worksheets (one for each month) and a summary worksheet that compiles statistical data, such as average hours worked, allocation toward different projects in which I'm involved, average days per month, etc. It also tracks my business travel and does some stuff there too.

I keep all monthly worksheets locked (with selection of locked cells disabled) and run a Workbook_Open macro in order to (a) lock the worksheets in case it was left unlocked at the end of the last session and (b) put me on the worksheet for the current month based on the current date in the computer's clock.

I recently reinstalled Windows and Office 2003, and the Workbook_Open event on this spreadsheet stopped working. I have unloaded the only add-in that I have outside the scope of what ships with Excel (John's PUP6) to rule out that it might have been affecting it. Through a little testing, here's what I learned:

1. I can create a new spreadsheet and add a Workbook_Open procedure to ThisWorkbook and it runs fine, so it's not my actual Excel installation.
2. I can drop this spreadsheet on the LAN and move to my girlfriend's computer, open it, and the Workbook_Open event runs fine, so it's not the spreadsheet itself.
3. I can manually run the code from the VBE, so it's not a syntax issue that miraculously escaped the error process during compilation.

Beyond that, all I can determine is that this problem is limited to the Workbook_Open event and only in this spreadsheet and only on my machine.

As I mentioned, the spreadsheet is extremely complex and the last thing I want is to rebuild it. If anyone has any suggestions as to how I can remedy this, I'd love nothing more to try them!

Thanks,
Dave

 

[Smitty]

Welcome to the Board!

Just a thought: is macro security set to High? If I recall with our recent transition to 2003, High was the default. On High you'll get no warnings...

HTH,

Smitty

 

[DavidCarrJaxFL]

Hi, there and thank you!

No, I should've pointed out a couple of other things:

First, the procedure is contained in ThisWorkbook, not the code layer for one of the worksheets. I know that in searching for other instances of this behavior, I've seen where quite a few people were going wrong by putting the code somewhere other than in ThisWorkbook. My code is in the correct location.

Also, my macro security is set to Medium, and I've also tried opening this spreadsheet with security set to Low, but to no avail. I've since changed it back to Medium.

Finally, I've also removed the code completely from ThisWorkbook and created a new Workbook_Open event with just a message box in it (Msgbox "Hello world!") and nothing happens when I close the spreadsheet and reopen it. Not suprisingly, again, the code runs fine if I fire it off manually from the VBE.

-Dave

 

[Legacy 98055]

I don't have a clue??? Hit Debug>Compile VB Project

See what happens there if anything.

For now,

Replace:
Private Sub Workbook_Open()

With:
Private Sub TempWorkbook_Open()

In a standard module, add this procedure that will run when the file is opened:

Public Sub Auto_open()
Application.Run "ThisWorkbook.TempWorkbook_Open"
End Sub

 

[DavidCarrJaxFL]

Tom,

Thanks for taking time to respond.

Compile VBA Project is greyed out once I've opened the project and am in ThisWorkbook. However, the code runs fine if I click somewhere in it and then click the Run button.

The addition of TempWorkbook_Open as a module seems to do the trick, and given that it should work on my business laptop (since Workbook_Open no longer appears), it's certainly a formiddable workaround. I thank you for that.

On the other hand, I'm still completely bamfoozled by the fact that Workbook_Open seems to be broken only in this particular spreadsheet and only on my computer. Talk about frustrating!

Regardless, and in light of the fact that we may never actually know what is causing it, I didn't want to rebuild the spreadsheet into a new document and your solution has prevented me from having to do so.

Thanks again-
Dave

 

[xapie128]

David,

I know this is stale thread, but I recently had a very similar thing happen, and your mention of the macro dealing with "the current month based on the current date in the computer's clock" and it running on one computer but not the other was exactly what I had happen. So I thought I'd post what I believe is the solution, hopefully if you don't see this then someone else might benefit...

In the two different computers, go to the Control Panel and open "Regional and Language Options." Click Customize, then the Date tab. I suspect that the Short Date Format on one will have a 2-digit month (e.g., April = 04) while the other one will have a 1-digit month (e.g., April =4).

If you only run this on those two computers, then the easy way to fix it is to change the setting on one computer to match the other. But the best way is to update your macro code so it will parse any of the choices in the pull-down from the Short Date Format.

~