Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

On Event VBA code

Hi,

I use the below code to measure holw long people stay in a spreadsheet for. The only way i can make this work is by making the WB shared and having it saved on exit.

This is causing problems as people are making a mess of reports and leaving the WB like that.

Can anyone, think of a work around.

So i need to save so that i record what time they left, yet i dont want to save any of their changes, they should be allowed to edit the reports (they need to drill pivots etc) but i cant allow them to save the changes, yet i need to save to monitor!!!

Can anyone think of a way around this?

Thanks
Seamus
Dim R As Long
Dim objSht
Dim t As Double



Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Sheets("Monitor")
    .Range("B64000").End(xlUp).Offset(0, 2) = Now()
End With
ActiveWorkbook.Save
End Sub

Private Sub Workbook_Open()
With Sheets("Monitor")
   .Range("B64000").End(xlUp).Offset(1, 0) = Environ("USERNAME")
   .Range("B64000").End(xlUp).Offset(0, 1) = Now()
End With

With Sheets("Monitor")
 
 R = .Range("B" & Rows.Count).End(xlUp).Row

             
        .Range("E2") = "=VLOOKUP(B2,$K$2:$L$5,2,0)"
       
         
        .Range("F2") = "=INT(C2)"
        
        .Range("G2") = "=MOD(C2,1)"
       
        .Range("H2") = "=MOD(D2,1)"
        
        .Range("I2") = "=H2-G2"
        
        .Range("E2:I2").AutoFill Destination:=.Range("E2:I" & R), Type:=xlFillDefault

Sheets("Screen").Select

For Each objSht In ActiveWorkbook.Sheets
    If objSht.Name <> "Screen" Then
    
      objSht.Visible = xlSheetHidden
    End If

   Next objSht
   
   End With

End Sub

Open in new window

0
Seamus2626
Asked:
Seamus2626
  • 5
  • 3
  • 2
  • +2
3 Solutions
 
Rory ArchibaldCommented:
Don't save the duration data in the workbook - write it out to another file somewhere, such as a network text file.
0
 
Seamus2626Author Commented:
Thats above me Rory! How could i go about that?

Thanks
Seamus
0
 
StephenJRCommented:
Could you have the information stored in another workbook and then not save the original file?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
nutschCommented:
Like Rory said, something like this (though I can't seem to get it to fire)


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Debug.Print "closed"
MyFile = "c:\temp\logtime.txt"
'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum
'write project info and then a blank line. Note the comma is required
Print #fnum, Environ("USERNAME") & " left at " & Now()
Write #fnum,
Close #fnum

End Sub

Private Sub Workbook_Open()
Debug.Print "open"
MyFile = "c:\temp\logtime.txt"
'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum
'write project info and then a blank line. Note the comma is required
Print #fnum, Environ("USERNAME") & " entered at " & Now()
Write #fnum,
Close #fnum
End Sub

Open in new window

0
 
Seamus2626Author Commented:
i see what your getting at, save the environ stuff elsewhere. So on open that gets called and hidden possibly?

Then i dont need the WB to be shared. I must shoot now, i will look at this first thing in the morning!
Thanks
Seamus
0
 
gowflowCommented:
Well you can try writing on an external file this info instead a sort of a log file
To Read the info in the file:
Open TxtFile For Input As #1

Input #1, UserName , Time
To Write to the File
Open TxtFile For Output As #1
Print #1, Environ("USERNAME"), Now()

Rgds/gowflow
0
 
gowflowCommented:
Ooops when I wrote my post no one had already answered !! Now I see that my idea was already passed on. Pls understand.
Rgds/gowflow
0
 
Seamus2626Author Commented:
Thanks guys, sorry i could only get back to this now.

WRT to writing to a text file, would this appear int the back ground while a user is in the report, would they see it?

Thanks
Seamus
0
 
Rory ArchibaldCommented:
No - it would be invisible to them unless you chose to show it.
0
 
Seamus2626Author Commented:
Thanks, i tried the code and get caught on

Open MyFile For Output As fnum

It says

Path/file access error

The location i put it to is
C:\Documents and Settings\travseam\Desktop

What would that error indicate?

Cheers,
Seamus
0
 
Rory ArchibaldCommented:
Did you specify a file name?
0
 
Seamus2626Author Commented:
Got it, didnt have a file named!!


Cheers,
Seamus
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now