Solved

excel vba macro, run only once

Posted on 2011-03-04
5
900 Views
Last Modified: 2012-06-22
I have a server delivering an Excel file to the browser.  

I have included some (novice-level but adequate) macro code to set appropriate attributes on each sheet of the file, to control online display and printing.  I want the macro to run when the file is delivered to the browser, but not to run again if the user saves the document and later re-opens it.

(It's a WebFocus application; the macro comes from a template .mht document stored on the server.)

What mechanicsm can be used?  

If there is a way for the auto_open macro to delete itseft, that would do it.  Otherwise, it will have to sense whether to proceed or quite -- I would want to have the first-time-only detection encapsulated in a function, so I can readily re-use it.

0
Comment
Question by:jbzg
  • 2
5 Comments
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 250 total points
ID: 35039388
I assume these changes the macro performs leave some marks, formats, values, etc that could be tested for when you first the macro, with a structure of

if macro has been run then
exit sub
else
run macro
end if

Do you have a sample file or sample code?

Thomas
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35040069
Just create a hidden protected sheet with a cell having true or false.  Or create a Name with a value initially set to false

Then on auto open check that variable if false do your run once routine and set that variable to true

Next time auto open happens after save the flag will be true so no need to run the macro

Make sense?

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 250 total points
ID: 35040185
You can find this:

Private Sub Workbook_Open()

    If Not [AlreadyRanOnce] Then
        'run your code
        MsgBox "Running your code", vbOK
        ActiveWorkbook.Names("AlreadyRanOnce").Value = True
    Else
        'do nothing, or continue Open processing
        MsgBox "Already Ran - no need to run", vbOK
    End If
End Sub

Open in new window


In the attached file.  I created the NAME called "AlreadyRanOnce" and set it to FALSE, which is your initial state.

Give it a shot!

Dave
RunOnlyOnceEver-r1.xls
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35356997
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
macro for each dropdown 15 44
Why doesn't duplicate values work on this spreadsheet? 6 32
Excel 2016 formulas 5 26
Excel - find text within text? 1 23
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now