Z
Zigball
Ok, I have been working on a project that I would not have been able to
start if it wasn't for google groups so I have to say thanks to all of
you guys especially the ones that do the most you know who you are.
Back to buisness, I need to track changes to a workbook and I also need
to email a summary sheet of data inputed on a daily basis is this
possible? I want to be able to track changes and keep a record of it
stored on the network or my computer, email or whatever. It is
imparative that I be able to identify the user by grabbing the name of
the owner of the computer. I have some tracking Vba code but it needs
to be modified. The other issue is jow to send emails as you click the
ok button on a userform? I want to be able to send the inputed
information to a couple of email addresses as it is inputed without out
using MS Outlook, I have a diffferent email program how can I
accomplish these tasks is there anyone out there that knows how.
EXAMPLE OF TRACKING CODE IN A MODULE:
Option Explicit
Dim X As New clsApp
Public EventNum
Sub StartTrackingEvents()
Set X.XL = Excel.Application
EventNum = 0
UserForm1.lblEvents.Caption = "Event Monitoring Started " & Now
UserForm1.Show 0
End Sub
Sub StopTrackingEvents()
Set X = Nothing
Unload UserForm1
End Sub
EXAMPLE OF TRACKING CODE IN A CLASS MODULE:
Option Explicit
Public WithEvents XL As Excel.Application
Sub LogEvent(txt)
EventNum = EventNum + 1
With UserForm1
With .lblEvents
.AutoSize = False
.Caption = .Caption & vbCrLf & txt
.Width = UserForm1.FrameEvents.Width - 20
.AutoSize = True
End With
.FrameEvents.ScrollHeight = .lblEvents.Height + 20
.FrameEvents.ScrollTop = EventNum * 20
End With
End Sub
Private Sub XL_NewWorkbook(ByVal Wb As Excel.Workbook)
LogEvent "NewWorkbook: " & Wb.Name
End Sub
Private Sub XL_SheetActivate(ByVal Sh As Object)
LogEvent "SheetActivate: " & Sh.Name
End Sub
Private Sub XL_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target
As Excel.Range, Cancel As Boolean)
LogEvent "SheetBeforeDoubleClick: " & Target.Address(False, False)
& " in " & Sh.Name
End Sub
Private Sub XL_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target
As Excel.Range, Cancel As Boolean)
LogEvent "SheetBeforeRightClick: " & Target.Address(False, False) &
" in " & Sh.Name
End Sub
Private Sub XL_SheetCalculate(ByVal Sh As Object)
LogEvent "SheetCalculate: " & Sh.Name
End Sub
Private Sub XL_SheetChange(ByVal Sh As Object, ByVal Target As
Excel.Range)
LogEvent "SheetChange: " & Target.Address(False, False) & " in " &
Sh.Name
End Sub
Private Sub XL_SheetDeactivate(ByVal Sh As Object)
LogEvent "SheetDeactivate: " & Sh.Name
End Sub
Private Sub XL_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As
Hyperlink)
LogEvent "SheetFollowHyperlink: " & Target.Name & " in " & Sh.Name
End Sub
Private Sub XL_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Excel.Range)
LogEvent "SheetSelectionChange: " & Target.Address(False, False) &
" in " & Sh.Name
End Sub
Private Sub XL_WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As
Excel.Window)
LogEvent "WindowActivate: " & Wn.Caption & " in " & Wb.Name
End Sub
Private Sub XL_WindowDeactivate(ByVal Wb As Excel.Workbook, ByVal Wn As
Excel.Window)
LogEvent "WindowDeactivate: " & Wn.Caption & " in " & Wb.Name
End Sub
Private Sub XL_WindowResize(ByVal Wb As Excel.Workbook, ByVal Wn As
Excel.Window)
LogEvent "WindowResize: " & Wn.Caption & " in " & Wb.Name
End Sub
Private Sub XL_WorkbookActivate(ByVal Wb As Excel.Workbook)
LogEvent "WorkbookActivate: " & Wb.Name
End Sub
Private Sub XL_WorkbookAddinInstall(ByVal Wb As Excel.Workbook)
LogEvent "WorkbookAddinInstall: " & Wb.Name
End Sub
Private Sub XL_WorkbookAddinUninstall(ByVal Wb As Excel.Workbook)
LogEvent "WorkbookAddinUninstall: " & Wb.Name
End Sub
Private Sub XL_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, Cancel
As Boolean)
LogEvent "WorkbookBeforeClose: " & Wb.Name
End Sub
Private Sub XL_WorkbookBeforePrint(ByVal Wb As Excel.Workbook, Cancel
As Boolean)
LogEvent "WorkbookBeforePrint: " & Wb.Name
End Sub
Private Sub XL_WorkbookBeforeSave(ByVal Wb As Excel.Workbook, ByVal
SaveAsUI As Boolean, Cancel As Boolean)
LogEvent "WorkbookBeforeSave: " & Wb.Name
End Sub
Private Sub XL_WorkbookDeactivate(ByVal Wb As Excel.Workbook)
LogEvent "WorkbookDeactivate: " & Wb.Name
End Sub
Private Sub XL_WorkbookNewSheet(ByVal Wb As Excel.Workbook, ByVal Sh As
Object)
LogEvent "WorkbookNewSheet: " & Sh.Name & " in " & Wb.Name
End Sub
Private Sub XL_WorkbookOpen(ByVal Wb As Excel.Workbook)
LogEvent "WorkbookOpen: " & Wb.Name
End Sub
THIS IS IN THE USERFORM
Private Sub CancelButton_Click()
Call StopTrackingEvents
End Sub
Private Sub FrameEvents_Click()
End Sub
Private Sub MarkButton_Click()
EventNum = EventNum + 1
With UserForm1
.lblEvents.AutoSize = False
.lblEvents.Caption = .lblEvents.Caption & vbCrLf & String(40,
"-")
.lblEvents.Width = .FrameEvents.Width - 20
.lblEvents.AutoSize = True
.FrameEvents.ScrollHeight = .lblEvents.Height + 20
.FrameEvents.ScrollTop = EventNum * 20
End With
End Sub
IT WORKS FOR ITS PURPOSE BUT MY PURPOSE IS DIFFERENT MORE ADVANCED I
NEED TO MODIFY IT SOME HOW JUST DON'T SEE HOW. I WANT IT TO BE ABLE TO
GRAB THE USERNAME OF THE PERSON MAKING CHANGES FOR ONE AND ALSO TO BE
ABLE TO STORE THE INFORMATION WITHOUT ANYONE EVER KNOWING THEY WERE
BEING RECORDED!
start if it wasn't for google groups so I have to say thanks to all of
you guys especially the ones that do the most you know who you are.
Back to buisness, I need to track changes to a workbook and I also need
to email a summary sheet of data inputed on a daily basis is this
possible? I want to be able to track changes and keep a record of it
stored on the network or my computer, email or whatever. It is
imparative that I be able to identify the user by grabbing the name of
the owner of the computer. I have some tracking Vba code but it needs
to be modified. The other issue is jow to send emails as you click the
ok button on a userform? I want to be able to send the inputed
information to a couple of email addresses as it is inputed without out
using MS Outlook, I have a diffferent email program how can I
accomplish these tasks is there anyone out there that knows how.
EXAMPLE OF TRACKING CODE IN A MODULE:
Option Explicit
Dim X As New clsApp
Public EventNum
Sub StartTrackingEvents()
Set X.XL = Excel.Application
EventNum = 0
UserForm1.lblEvents.Caption = "Event Monitoring Started " & Now
UserForm1.Show 0
End Sub
Sub StopTrackingEvents()
Set X = Nothing
Unload UserForm1
End Sub
EXAMPLE OF TRACKING CODE IN A CLASS MODULE:
Option Explicit
Public WithEvents XL As Excel.Application
Sub LogEvent(txt)
EventNum = EventNum + 1
With UserForm1
With .lblEvents
.AutoSize = False
.Caption = .Caption & vbCrLf & txt
.Width = UserForm1.FrameEvents.Width - 20
.AutoSize = True
End With
.FrameEvents.ScrollHeight = .lblEvents.Height + 20
.FrameEvents.ScrollTop = EventNum * 20
End With
End Sub
Private Sub XL_NewWorkbook(ByVal Wb As Excel.Workbook)
LogEvent "NewWorkbook: " & Wb.Name
End Sub
Private Sub XL_SheetActivate(ByVal Sh As Object)
LogEvent "SheetActivate: " & Sh.Name
End Sub
Private Sub XL_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target
As Excel.Range, Cancel As Boolean)
LogEvent "SheetBeforeDoubleClick: " & Target.Address(False, False)
& " in " & Sh.Name
End Sub
Private Sub XL_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target
As Excel.Range, Cancel As Boolean)
LogEvent "SheetBeforeRightClick: " & Target.Address(False, False) &
" in " & Sh.Name
End Sub
Private Sub XL_SheetCalculate(ByVal Sh As Object)
LogEvent "SheetCalculate: " & Sh.Name
End Sub
Private Sub XL_SheetChange(ByVal Sh As Object, ByVal Target As
Excel.Range)
LogEvent "SheetChange: " & Target.Address(False, False) & " in " &
Sh.Name
End Sub
Private Sub XL_SheetDeactivate(ByVal Sh As Object)
LogEvent "SheetDeactivate: " & Sh.Name
End Sub
Private Sub XL_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As
Hyperlink)
LogEvent "SheetFollowHyperlink: " & Target.Name & " in " & Sh.Name
End Sub
Private Sub XL_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Excel.Range)
LogEvent "SheetSelectionChange: " & Target.Address(False, False) &
" in " & Sh.Name
End Sub
Private Sub XL_WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As
Excel.Window)
LogEvent "WindowActivate: " & Wn.Caption & " in " & Wb.Name
End Sub
Private Sub XL_WindowDeactivate(ByVal Wb As Excel.Workbook, ByVal Wn As
Excel.Window)
LogEvent "WindowDeactivate: " & Wn.Caption & " in " & Wb.Name
End Sub
Private Sub XL_WindowResize(ByVal Wb As Excel.Workbook, ByVal Wn As
Excel.Window)
LogEvent "WindowResize: " & Wn.Caption & " in " & Wb.Name
End Sub
Private Sub XL_WorkbookActivate(ByVal Wb As Excel.Workbook)
LogEvent "WorkbookActivate: " & Wb.Name
End Sub
Private Sub XL_WorkbookAddinInstall(ByVal Wb As Excel.Workbook)
LogEvent "WorkbookAddinInstall: " & Wb.Name
End Sub
Private Sub XL_WorkbookAddinUninstall(ByVal Wb As Excel.Workbook)
LogEvent "WorkbookAddinUninstall: " & Wb.Name
End Sub
Private Sub XL_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, Cancel
As Boolean)
LogEvent "WorkbookBeforeClose: " & Wb.Name
End Sub
Private Sub XL_WorkbookBeforePrint(ByVal Wb As Excel.Workbook, Cancel
As Boolean)
LogEvent "WorkbookBeforePrint: " & Wb.Name
End Sub
Private Sub XL_WorkbookBeforeSave(ByVal Wb As Excel.Workbook, ByVal
SaveAsUI As Boolean, Cancel As Boolean)
LogEvent "WorkbookBeforeSave: " & Wb.Name
End Sub
Private Sub XL_WorkbookDeactivate(ByVal Wb As Excel.Workbook)
LogEvent "WorkbookDeactivate: " & Wb.Name
End Sub
Private Sub XL_WorkbookNewSheet(ByVal Wb As Excel.Workbook, ByVal Sh As
Object)
LogEvent "WorkbookNewSheet: " & Sh.Name & " in " & Wb.Name
End Sub
Private Sub XL_WorkbookOpen(ByVal Wb As Excel.Workbook)
LogEvent "WorkbookOpen: " & Wb.Name
End Sub
THIS IS IN THE USERFORM
Private Sub CancelButton_Click()
Call StopTrackingEvents
End Sub
Private Sub FrameEvents_Click()
End Sub
Private Sub MarkButton_Click()
EventNum = EventNum + 1
With UserForm1
.lblEvents.AutoSize = False
.lblEvents.Caption = .lblEvents.Caption & vbCrLf & String(40,
"-")
.lblEvents.Width = .FrameEvents.Width - 20
.lblEvents.AutoSize = True
.FrameEvents.ScrollHeight = .lblEvents.Height + 20
.FrameEvents.ScrollTop = EventNum * 20
End With
End Sub
IT WORKS FOR ITS PURPOSE BUT MY PURPOSE IS DIFFERENT MORE ADVANCED I
NEED TO MODIFY IT SOME HOW JUST DON'T SEE HOW. I WANT IT TO BE ABLE TO
GRAB THE USERNAME OF THE PERSON MAKING CHANGES FOR ONE AND ALSO TO BE
ABLE TO STORE THE INFORMATION WITHOUT ANYONE EVER KNOWING THEY WERE
BEING RECORDED!