Workbook_Open ... Doesn't! 2

[WalkerEvans]

There is something wrong with this code, but I can't seem to find it.

Private Sub Workbook_Open()
Dim Msg, Style, Title, Response
Msg = "Is this an Interest bearing debt?"
Style = vbYesNo + vbInformation + vbDefaultButton2
Title = "CHOOSE TYPE of DEBT - Interest/No Interest"
Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then
        Call Interest
    Else
        Call NoInterest
    End If
End Sub

The entire thing works fine if I just run the code for the message box, but when I try to set it up to auto-run on Event_Open it fails. There is something (probably embarrassingly obvious) wrong, but at this point I just can't see it.

Any assistance will be gratefully accepted.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[PHV]

it fails
Any error message ? On which line ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Chance1234]

Is the functions/sub Interest and Nointerest in the workbook ?

Is this code sitting in Thisworkbook ?

What are your security settings set to ?

Chance,

Filmmaker, gentleman and ROMAN!

 

[WalkerEvans]

PV: I should have been more explicit. It simply does not display the message box when the sheet is opened. No error messages, just nothing at all. If the code is run one line at a time, it works.

Chance: All of the subroutines are in the same workbook as the Workbook_Open sub; security is set to "Medium". I don't believe my problem lies in these areas, but I appreciate the thoughts.

I just can't see why the "On Open" event isn't working. This is frustrating.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[Chance1234]

does your spreadsheet compile ?

Chance,

Filmmaker, gentleman and ROMAN!

 

[PHV]

How is the wookbook open ?
If by code then have a look to the .RunAutoMacros method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WalkerEvans]

The sheet does compile, and it is opened manually by the user ... or will be, if I can ever get the #@%* thing fixed!!

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[Chance1234]

what happens if you just put

Private Sub Workbook_Open()

msgbox "Hello"

end sub

Chance,

Filmmaker, gentleman and ROMAN!

 

[combo]

Please reply to one of Chance's questions: "Is your code in ThisWorkbook module?" (as only there you can write workbook-level event macros).

combo

 

[WalkerEvans]

Commenting-out everything and adding your suggestion as a test yeilds the exact same result ... zip! For some reason the Open Event is just not working. I've explored alternate ways of setting this up using the online Help file, but nothing has worked. It's almost as if the entire Open Event has been disabled on this machine.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[combo]

You can try in immediate window or any procedure to execute:

Application.EnableEvents=True

and next open the workbook. Maybe another code disables events during excel session.

combo

 

[Chance1234]

another test ,add the following to thisworkbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

MsgBox "Hello"


End Sub


and type something in any cell , does the word hello come up ?




Chance,

Filmmaker, gentleman and ROMAN!

 

[WalkerEvans]

Combo & Chance,

I was about to reply "Of course that's where it is!" when it occurred to me that looking would be a good idea. Sure enough, it was in the "ThisWorkbook" module, so there was no good, valid reason why it shouldn't work. However, this whole thing has been so frustratingly weird that I decided to try a little weird myself. I copied all of the code into a Word document, deleted it all from ThisWorkbook, saved the workbook without any code at all, reopened it, then copied the code back into the module from Word and recompiled. Now, the darn things works!!

I have no idea what was wrong in the first place, but I can finally give this to the second-level supervisor that requested it. Now all I have to do is explain why this "easy" spreadsheet I told her could be done in a couple of hours yesterday morning didn't get to her until late today.

Super "Thanks!" to all who helped. I hope I never see anything like this again.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long