Macro in personal worbook, attempting to reference an On Action in another XLS

Hi,

I have a macro in my personal workbook that makes lots of XLS based on a school number. (one spreadsheet per school)

I create a macro button in each XLS that allows the users to lock the XLS and check for any errors as they work in it.

The button runs a macro ProtectWorksheet_Initiated_By_School which sits in sheet5 of each XLS created.

The section in the macro (In the personalised workbook that makes the XLS with the macro button within) is

Selection.OnAction = "Sheet5.ProtectWorksheet_Initiated_By_School"

It used to be in the personal workbook but obviously it cant stay here because all different uses will be using the XLS created, not me. It originally look like this.....

Selection.OnAction = "PERSONAL.XLS!ProtectWorksheet_Initiated_By_School"

Basically, the PERSONAL.XLS! needs to be replaced by a variable of whatever the XLS is going to be called (Its not actually been created yet.)

Later on at the end I save each file as

Please Login or Register  to view this content.

So for example 1111_SINGLE_STATUS_16052013.xls

So many question is how on earth do I reference the above XLS name into the OnAction bit.

When I tried just not including it and leaving it as "Sheet5.ProtectWorksheet_Initiated_By_School" I just get the error .PERSONAL.XLS!Sheet5.ProtectWorksheet_Initiated_By_School which its clearly doing because the macro that makes all the XLS is in my personalised workbook so its just the default.

If anyone has any ideas what I should be doing I would be extremely grateful. Im currently very stuck.

Debbie

The code doesnt work if its not in the worksheet module.
Ive tried it in a standard module and it doesnt work. Its on change so if the user changes something the macro does checks to make sure other columns are still valid.

Im not sure what your asking about selection but thats just the bit of code that fixes the macro to the macro button, created by the inital Macro in my personal workbook. Im hoping for something like Selection.OnAction = "ActiveSheet.Name + "_SINGLE_STATUS" + Format(Date, "_DDMMYYYY") + ".xls"!ProtectWorksheet_Initiated_By_School"

Ive actually tried to add the above code but obviously it errors