Excel

Run Once Code (With Option)

Ease of Use

Intermediate

Version tested with

2000, 2003 

Submitted by:

johnske

Description:

On first opening a workbook, your code is run and the event can be marked so that it never runs again. 

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 the user to have the option to 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, a very literal no-options 'run once' code that deletes itself after it's been run (as shown here > http://www.vbaexpress.com/kb/getarticle.php?kb_id=511) The code given here is a reversible alternative that creates a log file on the hard-drive (or wherever else you choose) to mark the event... 

Code:

instructions for use 

			
 '********************************************
 '<< BASIC PRINCIPLE FOR A RUN ONCE CODE >>
 
 '//This procedure is all that's required to run your own
 '//code once. Just put your own code below to only
 '//run it the very 1st time the file is opened.
 
Option Explicit 
 
Private Sub Workbook_Open() 
     
    Dim RunOnceFile$ 
     
     '*****************************************
     'set your own path and file-name
    RunOnceFile = "C:\" & "RUN THIS ONCE.Log" 
     '*****************************************
     
    If Dir(RunOnceFile) = Empty Then 
         'create a file as a 'marker' of the event
        Open RunOnceFile For Output As #1 
        Close #1 
         '< PUT WHATEVER CODE YOU WANT RUN HERE >
    Else 
         'the code above has been run before, so exit sub
        Exit Sub 
    End If 
End Sub 
 '********************************************
 
 
 
 
 
 '********************************************
 
 '<< RUN ONCE CODE EXAMPLE >>
 
Option Explicit 
 
Private Sub Workbook_Open() 
     
    Dim RunOnceFile$, User$, Msg As VbMsgBoxResult 
     
    User = Application.UserName 
     
     '*****************************************
     'set your own path and file-name
    RunOnceFile = "C:\" & "DONT SHOW AGAIN.Log" 
     '*****************************************
     
    If Dir(RunOnceFile) = Empty Then 
         'insert you own welcome message and instructions below
        Msg = MsgBox(" Welcome " & User & _ 
        " The time is now " & Format(Now, _ 
        "hh:mm AMPM") & vbLf & _ 
        "" & vbLf & _ 
        "The instructions for using this file are as follows:" & vbLf & _ 
        "Blah, blah, blah, and" & vbLf & _ 
        "blah, blah, blah" & vbLf & _ 
        "" & vbLf & _ 
        "Do you want this message shown each time you" & vbLf & _ 
        "open this file ?", _ 
        vbYesNo, "Hi " & User) 
        If Msg = vbNo Then 
             'create an empty log file named DONT SHOW AGAIN as a marker
            Open RunOnceFile For Output As #1 
            Close #1 
            Exit Sub 
        End If 
    Else 
         'user has chosen DONT SHOW AGAIN, so exit sub
        Exit Sub 
    End If 
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 code for RUN ONCE CODE EXAMPLE into this Module
  6. Now select File/Close and Return To Microsoft Excel
  7. Save your changes and close the workbook...
 

Test the code:

  1. Open the workbook and you'll get a message with a Yes/No option
  2. Click No and you won't be shown this message next time the book is opened
  3. (To get the message back again, go to your C drive then find and delete the log file named DONT SHOW AGAIN)
 

Sample File:

Initialize.zip 15.12KB 

Approved by mdmackillop


This entry has been viewed 244 times.

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