Application.OnKey prblm in Excel 2003

I have this small piece of vba code in ThisWorkbook object:

Private Sub Workbook_Open()
Application.OnKey "%{z}", "OptionListBox"
End Sub

Private Sub OptionListBox()
Userform1.Show
End Sub

What it does is the user can press Ctrl-z at any moment to launch a form from my workbook. Easy enough.

Everything is kosher except that, if I save the workbook (WB1) under a new name (WB2), then press ctrl-z, it loads the previous file(WB1) and runs the code from there. In other word, the call is being make from that old file.Grrr..

Ive tried to incorporate some With ActiveWorkbook in some key places in the code, but no success whatsoever.

I don't know how to attach this to a specific file, can't I run two instances for seperate workbooks on my screen?
Can anyone help me plz?

Thanx

Ok, I have dealt with something similar in Word before so its practically the same thing.

What you need to be doing is when you do your saveas you need to copy over the ThisWorkbook class code to
the new workbook, since the saveas doesnt copy the vba code modules/classes.

Or you could add your code to the xlt template so its centralized and all your saveas workbooks will reference this template.

Is this a local app or are you supporting other users?

Hi there Rob!

I know that when I save the workbook under another name, all the code is being included as well. So say I have Data2004.xls with bunch of code in it, if I save it under Data2005.xls, I will now have 2 files with the same code in it.

Now keep in mind that I'm still fairly a beginner with coding, so perhaps I'm not grasping what you're saying.

My problem is that the onkey event seems to be only launching from the 1st workbook. But I think I just solved it. I will post my result and the code if it works.

But If you could give me a quick insight on how to post my code, on this board, in the vbe format like you guys do, that'd be great. You click Code or VBCode?

Tx

For future reference here's what I've done:

VB Code:

Code in ThisWorkbook:
===========================
Private Sub Workbook_Activate()
TrapKey
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "%{z}"
End Sub
 
Code in Module:
===========================
Sub TrapKey()
    If ActiveWorkbook Is ThisWorkbook Then _
        Application.OnKey "%{z}", "LoadMyForm" 
End Sub