Disable macro after running once 2

[AWithers]

I have a legal document with a message box that pops up when the document opens to remind staff to do various things. The trouble is I only want to show this once.

I want a simple method of removing this message box before they send it on to clients etc. I would prefer that they didnt have to go into the code I think it may only confuse them

Hope you can help

Andy

 

[GlennUK]

Well, I remember how to do it in Excel 4 macros, if that's any help.

Insert an Excel macro sheet by right clicking any sheet tab, and choosing Insert from the pop-up menu, and choosing a sheet type of MS Excel 4.0 Macro.

Now type these instructions ...
in cell A1: Autpen
in cell A2: =ALERT("Text of all the reminders",3)
in cell A3: =DELETE.NAME("Autpen&quot
in cell A4: =RETURN()

Now, make cell A1 the active cell, and use menu command Insert/Name/Define ... which will show the "Define Name" dialog box, with a guess of name of Autpen ( which is a reserved name in Excel, in that it runs automatically when the workbook is opened ), and then choose the Command option in the Macro section. Press OK.

This macro will only run once, at the next time the workbook is opened. After that it won't run again.

To hide the macro sheet ( just for tidyness ), simply choose menu commmand Format/Sheet/Hide. This can be done straight after creating the macro, as it will still run when hidden.

Hope that helps, Glenn.

 

[rmikesmith]

Here is another way to do this using VBA only:

'TAG: Run Msg
Private Sub Workbook_Open()
Dim wkb As Workbook

Set wkb = ThisWorkbook

If wkb.VBProject.VBComponents("ThisWorkbook") _
.codemodule.Lines(2, 1) = "'TAG: Run Msg" Then
  ' Add msgbox line here.  Could also call other procedures
  ' that only need to run once.
  wkb.VBProject.VBComponents("ThisWorkbook") _
  .codemodule.ReplaceLine Line:=2, String:="'TAG: Disable Msg"
End If

Set wkb = Nothing
End Sub

Notes: This procedure is the workbook OnOpen event handler and must reside in the ThisWorkbook code module. The key to making this work is the dummy comment 'TAG: Run Msg which must be placed on Line 2 of the same module. This string can be anything as long as it matches the one in the IF THEN test. The replacement comment, 'TAG: Disable Msg, could be simply an empty string. The workbook will need to be saved to make the change permanent.

Thanks to Rob Broekhuis, who's idea to modify VBA code on the fly I borrowed.

Regards,
M. Smith

 

[AWithers]

Thanks for those messages I'm actually using Word 97 but the VBA code probably works in a similar way I will give it a try and let you know

Thanks

Andy

 

[AWithers]

THANKS VERY MUCH...THAT WORKED GREAT

Here is the code I used in the end if anyone else should need it

Andy

'TAG: Run Msg
Private Sub Document_Open()

Dim doc As Document

Set doc = ThisDocument

If doc.VBProject.VBComponents("Thisdocument&quot.CodeModule.Lines(2, 1) = "'TAG: Run Msg" Then
MsgBox "Test Message"
doc.VBProject.VBComponents("Thisdocument&quot.CodeModule.replaceLine Line:=2, String:="'TAG: Disable msg"
End If
Set doc = Nothing

End Sub