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

 ActiveSheet.Name + "_SINGLE_STATUS" + Format(Date, "_DDMMYYYY") + ".xls"
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