Execute program (macro?) upon opening a workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to program (macro or VB script - I'm not sure which) whenever I open a specific workbook file. I am very experienced in most functions of excel but have never "programmed" in it (i.e., macros or VB script - which do I use). I need some initial guidance to get me started in how to program the following task..

DESCRIPTION OF TASK TO ACCOMPLISH: I want the workbook file to be usable only during a certian trial period. So, when that workbook is opened I want the program to check if the current date is less than the expiration date (stored in a hidden cell) and greater than the most recent used date/time (stored in another hidden cell that is updated by the program each time the file is opened)

Thanks in Advance
Warren
 
Hi
you will need to run a macro on opening, and also one on closing tha
saves the file automatically if you want to record the time of the mos
recent access (though I'm not sure why - won't the time now always b
after the time the file was last accessed?).

the auto-open and auto_close macros run automatically whenever the fil
opens and closes. The attached sheet includes a sort of worked example
with the sheet named "hid" containing data you would hide, and th
macros running to check dates on opening, then closing if the expir
date has passed unless the correct passwor "excelforum" is entered.

The macros are:

Sub AUTO_OPEN()
If Date > Sheets("hid").Range("expiry_date").Value Then
resp = InputBox("sorry, your trial has expired." & Chr(10) & "You mus
enter a password to continue working in this workbooket", "Ente
password", resp)
If LCase(resp) <> LCase(Sheets("hid").Range("password").Value) The
ActiveWorkbook.Close
End If
End Sub

Sub AUTO_Close()
Sheets("hid").Range("last_opened").Value = Now()
Sheets("hid").Range("times_opened").Value = 1
Sheets("hid").Range("times_opened").Value
If ActiveWorkbook.Saved = False Then ActiveWorkbook.Save
End Su

Attachment filename: test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=44732
 
First, VBA is the "language" for writing code. VBA is
Visual Basic for Applications. The word macro is a
hangover from the early days of spreadsheets when macro
code was actually just keystrokes. When writing code one
uses subroutines ... also known as procedures and macros.

Excel has two methods that the code writer can use to
automatically run code when the wrkbook is opened.
One is to use the Workbook.Open method and the other is
to use a procedure ( aka sub) called Auto_Open
Both will run on opening the workbook.

If you want to secure the workbook, then I'd suggest that
you have one worksheet to use as a "splash screen" and
have all the others set to XlSheetVeryHidden.
Your code can test the date and set the sheets to visible
if the date test succeeds.

This is because if the user sets excel security to haign,
he can open a workbook with macros disabled...in
otherwords your code won't run, but the sheets will be
visible. So by hiding the sheets and only allowing code
to make them visible gives you some protection.

If you're used to VBScript, then you'll find VBA quite
simple. Suggest you turn on hte macro recorder, hide &
unhide some sheets, then examine the code wirtten. It
won't be ideal, but will give you a good handle.


HTH
Patrick Molloy
Microsoft Excel MVP



-----Original Message-----
I want to program (macro or VB script - I'm not sure
Click to expand...
which) whenever I open a specific workbook file. I am
very experienced in most functions of excel but have
never "programmed" in it (i.e., macros or VB script -
which do I use). I need some initial guidance to get me
started in how to program the following task...
DESCRIPTION OF TASK TO ACCOMPLISH: I want the workbook
Click to expand...
file to be usable only during a certian trial period.
So, when that workbook is opened I want the program to
check if the current date is less than the expiration
date (stored in a hidden cell) and greater than the most
recent used date/time (stored in another hidden cell that
is updated by the program each time the file is opened).
 
Thanks Nicky. I will try what you recommend
The reason I want to record the last used date and check against it is to secure against people temporarily setting back the date on their computer
-Warre

----- Nicky > wrote: ----

H
you will need to run a macro on opening, and also one on closing tha
saves the file automatically if you want to record the time of the mos
recent access (though I'm not sure why - won't the time now always b
after the time the file was last accessed?).

the auto-open and auto_close macros run automatically whenever the fil
opens and closes. The attached sheet includes a sort of worked example
with the sheet named "hid" containing data you would hide, and th
macros running to check dates on opening, then closing if the expir
date has passed unless the correct passwor "excelforum" is entered

The macros are

Sub AUTO_OPEN(
If Date > Sheets("hid").Range("expiry_date").Value The
resp = InputBox("sorry, your trial has expired." & Chr(10) & "You mus
enter a password to continue working in this workbooket", "Ente
password", resp
If LCase(resp) <> LCase(Sheets("hid").Range("password").Value) The
ActiveWorkbook.Clos
End I
End Su

Sub AUTO_Close(
Sheets("hid").Range("last_opened").Value = Now(
Sheets("hid").Range("times_opened").Value = 1
Sheets("hid").Range("times_opened").Valu
If ActiveWorkbook.Saved = False Then ActiveWorkbook.Sav
End Su

Attachment filename: test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=44732
 
Back
Top