Application.OnKey Not Working Properly (?)

[WalkerEvans]

I have the following code that is not working:

Sub Interest()
Worksheets("Debt w_Interest").Visible = True
Worksheets("Sheet1").Visible = False
Worksheets("Debt No Interest").Visible = False
Dim Msg, Title, Style, Response
Msg = "Press  ""END""  Key when done"
Title = "TO CALCULATE WHEN FINISHED ENTERING DATA"
Style = vbOKOnly + vbExclamation
Response = MsgBox(Msg, Style, Title)
Application.OnKey "{END}", "finish"
End Sub

Everything is fine until the last line. I'm getting an error that says the macro "finish" cannot be found ... but it is the very next subroutine. This is probably something really easy and obvious, but I've been looking at it for too long. BTW, I've used this syntax with OnKey before with no problems.

Does anybody out there have a clue where I've gone wrong?



----------------------------------------------------------------------------------
[small]"Did you hear about the guy who refused to pay his exorcist?[/small]
He was repossessed."

 

[combo]

Is the workbook still opened when you press {END} key? Is the macro in standard module?

combo

 

[WalkerEvans]

combo,

The workbook is still open. This code is attached to the worksheet (ie, ThisWorkbook) and is a subroutine under Private Sub Workbook_Open(). It was developed at user request to handle debt calculations. The entire macro does (should do) the following:

1. Displays a blank worksheet with a MsgBox letting the user choose between a debt with or without interest
2. Displays either the Interest or No_Interest sheets depending on the user choice
3. For No_Interest calculates the monthly repayment amount based on amount of debt and number of months for liquidation
4. For Interest calculates the monthly repayment amount w/interest and total interest that will be paid based on amount of debt and number of months for liquidation

The No_Interest logic works just fine, and the Interest calculations work fine ... if you can get to them. Left to my own inclinations the problem code wouldn't even be a part of this, but it is a user requirement so I have to leave it in.

Does this help any?




----------------------------------------------------------------------------------
[small]"Did you hear about the guy who refused to pay his exorcist?[/small]
He was repossessed."

 

[GlennUK]

So, your answer to combo's query ...

Is the macro in standard module?

is "No, it's in the Workbook module"?

If so, how about moving it to a standard module?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[FaneDuru]

Try to change the subroutine name from "finish" in something else... I do not understand why but in this way works for me.

Fane Duru

 

[SkipVought]

Why even bother to display a message box for a fleeting moment. The user will never see the message unless you include a loop to delay the last statement.

And that begs the question, why a message box at all? Why not just a MESSAGE in a TextBox and a delay before calling finish?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[WalkerEvans]

Skip:
The message box displays when the user first opens the "With Interest" form and stays displayed until the user clicks on the OK button. The user then fills out all the necessary data, confirms it, then hits "END" when completely done. The "No Interest" form calculates just from formulas on the sheet itself, while the "With Interest" sheet calls a subroutine that does all of the calculations for interest, principle, and remaining balance and displays a table showing those figures for each month (anywhere between 6 and 60). Also displayed is the total interest charged over the course of repayment and the total amount repaid. This is the way this has to be done. <off topic> How are you doing? Hopefully no post-op problems. </off topic>

FaneDuru:
I've also had things that work in VBA, but make no sense in any rational way. When I get to the office in the morning I'll give this a try and let you know how it turns out.

Apologies to all for not answering sooner. I was stuck in an all day training class yesterday (physical effort, not just class work) and came home worn out - dinner and early bed without turning on a compute at all.




----------------------------------------------------------------------------------
[small]"Did you hear about the guy who refused to pay his exorcist?[/small]
He was re-Possessed."

 

[SkipVought]

Walker,

Doing fine after 3 weeks of recovery at home and 2 weeks back to work. Thanks for asking.

Why not a Textbox rather than a Message Box, requiring a user response that you are attempting to circumvent, (the only Knight of the Round Table working on anger management with other knights)

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[WalkerEvans]

Skip,

Thanks. I'll give that a try. In the meantime I'll try to prevent Sir Cumlocution (the knight in charge of hyperverbosity) away from this keyboard.

Glad to hear you're doing well. That 3 and 2 schedule matches what I did back in September. It still took me another 2 weeks to adjust to the 8 hours a day bit before I stopped coming home exhausted.




----------------------------------------------------------------------------------
[small]"Did you hear about the guy who refused to pay his exorcist?[/small]
He was re-Possessed."

 

[WalkerEvans]

FaneDuru: In this case, making the change had no impact at all. Thanks for trying.

Skip: Your solution does indeed allow the code to continue on to the proper sub once the "OK" button is clicked, but it has a drawback that makes it (so far) unusable. Here is what I need to accomplish:

1) User chooses "With Interest"
2) The Interest sheet is displayed
3) The user is given an instruction on what to do once they have finished entering data
4) They enter the data, correcting as necessary
5) They push the given key and the code runs

The problem I have with the Textbox is that it requires an answer before the user can do anything else; this makes #4 above impossible to do.

Hold the Presses!! It just occurred to me that using a balloon set to modeless might be the solution. I've never used balloons before but I know the basic concepts. Before I waste a bunch of time running after this White Rabbit (or Red Herring, if you don't like Carroll), do you think this is viable, or am I about to run headlong into another brick wall?




----------------------------------------------------------------------------------
[small]"Did you hear about the guy who refused to pay his exorcist?[/small]
He was re-Possessed."

 

[combo]

To solve your initial problem (find the macro) you need to move it to standard module (Glenn's direct post).
Instead of 'OnKey' maybe modeless userform could be useful, it could contain some help and END button.
The risk with assistant is that it can be closed by the user, the code:

Sub ShowBalloon()
With Assistant.NewBalloon
    .Icon = msoIconTip
    .Mode = msoModeModeless
    .BalloonType = msoBalloonTypeButtons
    .Button = msoButtonSetOkCancel
    .Callback = "finish"
    .Heading = "Press  ""OK""  Key when done"
    .Show
End With
End Sub

Sub finish(bln As Balloon, lbtn As Long, lPriv As Long)
bln.Close
If lbtn = msoBalloonButtonOK Then '-1
    MsgBox "OK"
ElseIf lbtn = msoBalloonButtonCancel Then '-2
    MsgBox "cancel"
Else
    MsgBox lbtn
End If
End Sub

combo