Insert Sheet name formula with VBA

I use the following line to insert the formula for a sheet name:

Sub Sheetname()
' Inserts Sheet name formula into active cell
   ActiveCell.Formula = _
        "=MID(CELL(""FILENAME"",A1),FIND(""]"",CELL(""FILENAME"",A1))+1,255)"
   ActiveCell.Select
End Sub

This works perfectly in a workbook, but when put into an AddIn, it gives an "Argument not Optional" error. I have looked in Help but can't find what argument I am missing, and why it only matters in an AddIn?

Any ideas?

BTW: I realise it is easier to insert sheetname from the sheet property, but I specifically want the formula

Re: Insert Sheet name formula with VBA

Thanks Roy, but I tried:

Sub Sheetname()
'
' Inserts Sheet name formula into active cell
    ThisWorkbook.ActiveCell.Formula = _
        "=MID(CELL(""FILENAME"",A1),FIND(""]"",CELL(""FILENAME"",A1))+1,255)"
    ActiveCell.Select
End Sub

But to no avail...

Re: Insert Sheet name formula with VBA

Hmm?

Well I have it pasted into a standard module and have a simple dropdown menu to fire it up. Always the error.

Will try creating it as a single AddIn, and see if it works.

Thanks anyway,

Mike

Re: Insert Sheet name formula with VBA

Attached is the simple addin. All I have done is assign a shortcut (Ctrl-Shift E) and save it as an xla.

I get "Argument not Optional" every time.

Can you confirm this?

Re: Insert Sheet name formula with VBA

Quote from Richie(UK)

I have just tried your example add-in, called the routine via the shortcut keys and .... it still works!

Nope, still doesn't work for me. I am going to uninstall all other addins and then give it a go. I can only assume there is some kind of conflict...

Quote from Richie(UK)

Is there anything else we should know? Does the example add-in work for you (uninstall the problem add-in and try the example)?

The example addin I created above doesn't work for me, either.

Thanks for your time!