Forlmula or macro needed
 

When someone opens a workbook it records the day it was opened so I can use
it in a formula.
Example:
User opens on 5-1-05 at 12:00 p.m. = Now ( )
Closes on 5-1-05 at 5:00 pm.
Opens again on 5-2-05 at12:00 p.m.
Closes on 5-2-05 at 2:00 pm
Number of days or hours used = 2 days or 7 hours
Is there a formula that locks the 1st day 5-1-05, 12:00 p.m.
If it is a macro do I need a button to make it work or can I add the code to
an exisiting button or just when the workbook is opened for the first time.
--
Gary Baker

"GWB Direct" wrote:

When someone opens a workbook it records the day it was opened so I can use
it in a formula.
Example:
User opens on 5-1-05 at 12:00 p.m. = Now ( )
Closes on 5-1-05 at 5:00 pm.
Opens again on 5-2-05 at12:00 p.m.
Closes on 5-2-05 at 2:00 pm
Number of days or hours used = 2 days or 7 hours
Is there a formula that locks the 1st day 5-1-05, 12:00 p.m.
If it is a macro do I need a button to make it work or can I add the code to
an exisiting button or just when the workbook is opened for the first time.
--
Gary Baker

I'm not sure if this is what you want but it will give you the amount of time
the book was open and how many times it has been open. It ties up four cells.
You will need to format cells A1:A3 (Custom, h:mm:ss). I'm sure there has got
to be a better way I just don't know it.

Sub Auto_Open()
Dim tm1 As Date
Dim num As Integer
tm1 = Now()
tm1 = Format(Time, ShortTime)
Sheets("Sheet1").Select
Range("A2").Value = tm1
num = Range("B1").Value
Range("B1").Value = num + 1

End Sub

Sub Auto_Close()
Dim tm2 As Date
Dim tm As Date
tm2 = Now()
tm2 = Format(Time, ShortTime)
Sheets("Sheet1").Select
Range("A3").Value = tm2 - Range("A2").Value
tm = Range("A1").Value + Range("A3").Value
Range("A1:A3").ClearContents
Range("A1").Value = tm
Range("A1").Select
ThisWorkbook.Save

End Sub


"GWB Direct" wrote:

When someone opens a workbook it records the day it was opened so I can use
it in a formula.
Example:
User opens on 5-1-05 at 12:00 p.m. = Now ( )
Closes on 5-1-05 at 5:00 pm.
Opens again on 5-2-05 at12:00 p.m.
Closes on 5-2-05 at 2:00 pm
Number of days or hours used = 2 days or 7 hours
Is there a formula that locks the 1st day 5-1-05, 12:00 p.m.
If it is a macro do I need a button to make it work or can I add the code to
an exisiting button or just when the workbook is opened for the first time.
--
Gary Baker

I'll try this approach. The end result will allow me to li,it the amount of
time a user can opn a workbook. How do I add this code to the workbook so it
starts when someone opens the file.
Thanks
--
Gary Baker


"dbaggett" wrote:

I'm not sure if this is what you want but it will give you the amount of time
the book was open and how many times it has been open. It ties up four cells.
You will need to format cells A1:A3 (Custom, h:mm:ss). I'm sure there has got
to be a better way I just don't know it.

Sub Auto_Open()
Dim tm1 As Date
Dim num As Integer
tm1 = Now()
tm1 = Format(Time, ShortTime)
Sheets("Sheet1").Select
Range("A2").Value = tm1
num = Range("B1").Value
Range("B1").Value = num + 1

End Sub

Sub Auto_Close()
Dim tm2 As Date
Dim tm As Date
tm2 = Now()
tm2 = Format(Time, ShortTime)
Sheets("Sheet1").Select
Range("A3").Value = tm2 - Range("A2").Value
tm = Range("A1").Value + Range("A3").Value
Range("A1:A3").ClearContents
Range("A1").Value = tm
Range("A1").Select
ThisWorkbook.Save

End Sub


"GWB Direct" wrote:

When someone opens a workbook it records the day it was opened so I can use
it in a formula.
Example:
User opens on 5-1-05 at 12:00 p.m. = Now ( )
Closes on 5-1-05 at 5:00 pm.
Opens again on 5-2-05 at12:00 p.m.
Closes on 5-2-05 at 2:00 pm
Number of days or hours used = 2 days or 7 hours
Is there a formula that locks the 1st day 5-1-05, 12:00 p.m.
If it is a macro do I need a button to make it work or can I add the code to
an exisiting button or just when the workbook is opened for the first time.
--
Gary Baker

This will only let you know how long it was open. I'm not sure how to make
it close after a certain length of time. Sorry


"GWB Direct" wrote:

I'll try this approach. The end result will allow me to li,it the amount of
time a user can opn a workbook. How do I add this code to the workbook so it
starts when someone opens the file.
Thanks
--
Gary Baker


"dbaggett" wrote:

I'm not sure if this is what you want but it will give you the amount of time
the book was open and how many times it has been open. It ties up four cells.
You will need to format cells A1:A3 (Custom, h:mm:ss). I'm sure there has got
to be a better way I just don't know it.

Sub Auto_Open()
Dim tm1 As Date
Dim num As Integer
tm1 = Now()
tm1 = Format(Time, ShortTime)
Sheets("Sheet1").Select
Range("A2").Value = tm1
num = Range("B1").Value
Range("B1").Value = num + 1

End Sub

Sub Auto_Close()
Dim tm2 As Date
Dim tm As Date
tm2 = Now()
tm2 = Format(Time, ShortTime)
Sheets("Sheet1").Select
Range("A3").Value = tm2 - Range("A2").Value
tm = Range("A1").Value + Range("A3").Value
Range("A1:A3").ClearContents
Range("A1").Value = tm
Range("A1").Select
ThisWorkbook.Save

End Sub


"GWB Direct" wrote:

When someone opens a workbook it records the day it was opened so I can use
it in a formula.
Example:
User opens on 5-1-05 at 12:00 p.m. = Now ( )
Closes on 5-1-05 at 5:00 pm.
Opens again on 5-2-05 at12:00 p.m.
Closes on 5-2-05 at 2:00 pm
Number of days or hours used = 2 days or 7 hours
Is there a formula that locks the 1st day 5-1-05, 12:00 p.m.
If it is a macro do I need a button to make it work or can I add the code to
an exisiting button or just when the workbook is opened for the first time.
--
Gary Baker

This code doesn't work when the workbook opens or closes. It works when I
manually run the macros. Can it start auto open when the workbook is opened
and auto close when the worbook is closed? Does the codes need to be in this
workbook to work. I have it in Sheet1 because I have another code in this
workbook that closes the workbook when the user opens and closes 3 times. I
want to use this code to show how many times a user has left.

Sub Auto_Open()
Dim tm1 As Date
Dim num As Integer
tm1 = Now()
tm1 = Format(Time, ShortTime)
Sheets("Order Form").Select
Range("A2").Value = tm1
num = Range("B1").Value
Range("B1").Value = num + 1
End Sub
Sub Auto_Close()
Dim tm2 As Date
Dim tm As Date
tm2 = Now()
tm2 = Format(Time, ShortTime)
Sheets("Order Form").Select
Range("A3").Value = tm2 - Range("A2").Value
tm = Range("A1").Value + Range("A3").Value
Range("A1:A3").ClearContents
Range("A1").Value = tm
Range("A1").Select
ThisWorkbook.Save

End Sub


--
Gary Baker


"dbaggett" wrote:


This will only let you know how long it was open. I'm not sure how to make
it close after a certain length of time. Sorry


"GWB Direct" wrote:

I'll try this approach. The end result will allow me to li,it the amount of
time a user can opn a workbook. How do I add this code to the workbook so it
starts when someone opens the file.
Thanks
--
Gary Baker


"dbaggett" wrote:

I'm not sure if this is what you want but it will give you the amount of time
the book was open and how many times it has been open. It ties up four cells.
You will need to format cells A1:A3 (Custom, h:mm:ss). I'm sure there has got
to be a better way I just don't know it.

Sub Auto_Open()
Dim tm1 As Date
Dim num As Integer
tm1 = Now()
tm1 = Format(Time, ShortTime)
Sheets("Sheet1").Select
Range("A2").Value = tm1
num = Range("B1").Value
Range("B1").Value = num + 1

End Sub

Sub Auto_Close()
Dim tm2 As Date
Dim tm As Date
tm2 = Now()
tm2 = Format(Time, ShortTime)
Sheets("Sheet1").Select
Range("A3").Value = tm2 - Range("A2").Value
tm = Range("A1").Value + Range("A3").Value
Range("A1:A3").ClearContents
Range("A1").Value = tm
Range("A1").Select
ThisWorkbook.Save

End Sub


"GWB Direct" wrote:

When someone opens a workbook it records the day it was opened so I can use
it in a formula.
Example:
User opens on 5-1-05 at 12:00 p.m. = Now ( )
Closes on 5-1-05 at 5:00 pm.
Opens again on 5-2-05 at12:00 p.m.
Closes on 5-2-05 at 2:00 pm
Number of days or hours used = 2 days or 7 hours
Is there a formula that locks the 1st day 5-1-05, 12:00 p.m.
If it is a macro do I need a button to make it work or can I add the code to
an exisiting button or just when the workbook is opened for the first time.
--
Gary Baker

Auto_Open Subs must be placed into a General Module.

Not sheet or Thisworkbook.

If in This workbook you would name it Sub Workbook_Open()


Gord Dibben Excel MVP

On Wed, 4 May 2005 08:10:02 -0700, "GWB Direct"
wrote:

This code doesn't work when the workbook opens or closes. It works when I
manually run the macros. Can it start auto open when the workbook is opened
and auto close when the worbook is closed? Does the codes need to be in this
workbook to work. I have it in Sheet1 because I have another code in this
workbook that closes the workbook when the user opens and closes 3 times. I
want to use this code to show how many times a user has left.

Sub Auto_Open()
Dim tm1 As Date
Dim num As Integer
tm1 = Now()
tm1 = Format(Time, ShortTime)
Sheets("Order Form").Select
Range("A2").Value = tm1
num = Range("B1").Value
Range("B1").Value = num + 1
End Sub
Sub Auto_Close()
Dim tm2 As Date
Dim tm As Date
tm2 = Now()
tm2 = Format(Time, ShortTime)
Sheets("Order Form").Select
Range("A3").Value = tm2 - Range("A2").Value
tm = Range("A1").Value + Range("A3").Value
Range("A1:A3").ClearContents
Range("A1").Value = tm
Range("A1").Select
ThisWorkbook.Save

End Sub

This worked. Thank you for all your help with this issue.
--
Gary Baker


"Gord Dibben" wrote:

Auto_Open Subs must be placed into a General Module.

Not sheet or Thisworkbook.

If in This workbook you would name it Sub Workbook_Open()


Gord Dibben Excel MVP

On Wed, 4 May 2005 08:10:02 -0700, "GWB Direct"
wrote:

This code doesn't work when the workbook opens or closes. It works when I
manually run the macros. Can it start auto open when the workbook is opened
and auto close when the worbook is closed? Does the codes need to be in this
workbook to work. I have it in Sheet1 because I have another code in this
workbook that closes the workbook when the user opens and closes 3 times. I
want to use this code to show how many times a user has left.

Sub Auto_Open()
Dim tm1 As Date
Dim num As Integer
tm1 = Now()
tm1 = Format(Time, ShortTime)
Sheets("Order Form").Select
Range("A2").Value = tm1
num = Range("B1").Value
Range("B1").Value = num + 1
End Sub
Sub Auto_Close()
Dim tm2 As Date
Dim tm As Date
tm2 = Now()
tm2 = Format(Time, ShortTime)
Sheets("Order Form").Select
Range("A3").Value = tm2 - Range("A2").Value
tm = Range("A1").Value + Range("A3").Value
Range("A1:A3").ClearContents
Range("A1").Value = tm
Range("A1").Select
ThisWorkbook.Save

End Sub