Excel

Run Once Then Delete Code

Ease of Use

Intermediate

Version tested with

2000, 2003 

Submitted by:

johnske

Description:

On first opening a workbook, your code is run and the specified code module is then removed. 

Discussion:

Sometimes you want to (say) convey an important message to a new user when they first open your project, but this message would become really annoying if it were always shown, so you want to just run it once and never show it again. This goal can be accomplished using several methods such as putting an entry in the workbook itself (which is not always feasible) or, putting an entry in the registry, or, to create a log file on the hard-drive (or wherever else you choose) to mark the event as shown here > http://www.vbaexpress.com/kb/getarticle.php?kb_id=510. The alternative given here is a very literal no-options 'run once' code that deletes itself after it's been run... (Another possible reason for a run-once code could be for a users automatic on-line registration of your project when first opened. On-line registration may involve a password and this use of the password would usually be required to have a reasonable degree of in-built confidentiality, hence the need to delete it immediately after use). With this procedure, after opening the workbook there is no code module to read about what has happened and nothing in the ThisWorkbook module to indicate that anything even existed prior to opening the workbook. {CAVEATS: 1) When security is set to medium, Auto_Open or Workbook_Open events can be cancelled by holding down ''Shift'' to cancel the Auto events. So although this provides some additional degree of security, it's advisable to also lock the project for viewing. 2) Unless the workbook is saved after opening the module deletion will not become 'Fixed', so it's advisable to run another procedure to ensure that the user saves the workbook before closing} 

Code:

instructions for use

			

'************************************************ '<< CODE FOR THE RunOnceModule >> Option Explicit Sub Auto_Open() Dim ThisModule As Object '//set a reference to MS Visual Basic '//for Applications Extensibility 5.3 On Error Resume Next ThisWorkbook.VBProject.References.AddFromGuid _ "{0002E157-0000-0000-C000-000000000046}", 5, 3 '//insert your 'Run-once' code below (the example here is a simple message box) '--------------------------------------------------------------------------------------- MsgBox "This project has been registered using code in this module" & vbLf & _ "(Demo: the registration code module will now be deleted)" '--------------------------------------------------------------------------------------- '//now remove this code module Set ThisModule = Application.VBE.ActiveVBProject.VBComponents '<< remove the apostrophe from the start of the next line to delete module >> 'ThisModule.Remove VBComponent:=ThisModule.Item("RunOnceModule") End Sub '************************************************

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Tools/Project Explorer
  4. Select the ThisWorkbook module
  5. Copy and paste the ThisWorkbook module code into this Module
  6. Go to Insert/Module and paste the code for the RunOnceModule into this module (module1)
  7. Go to View/Properties, then select module1 and re-name it to: RunOnceModule
  8. Now select File/Close and Return To Microsoft Excel
  9. Save your changes...
 

Test the code:

  1. Download the attachment
  2. Extract the workbook to the desktop
  3. Open the workbook, you'll get a message.
  4. Open the VBE window and note that there's a RunOnceModule
  5. Remove the leading apostrophe from the last line of code in this module
  6. Save and close the workbook.
  7. Open the workbook again (you'll get the same message as before)
  8. Open the VBE window and note that there is now NO RunOnceModule
  9. Save, Close, and re-open the workbook to see there's now no message or module
 

Sample File:

RunItOnceOnly.zip 8.17KB 

Approved by mdmackillop


This entry has been viewed 509 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express