Good Excel Gurus

[Mingase]

I found this code on the internet and twiked a bit to suit my need. The code works 100 fine when I directly make changes on the worksheet.
however when the changes are made by input entries from a Userform, the worksheet is not creating an audit log.

help will be very much appreciated.

This Workbook code:

Option Explicit

Private mObjLogger As csLogger

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Not mObjLogger Is Nothing Then
mObjLogger.LogEventAction ("Close")
Set mObjLogger = Nothing
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Not mObjLogger Is Nothing Then
mObjLogger.LogEventAction ("Save")
End If

End Sub

Private Sub Workbook_Open()

Set mObjLogger = New csLogger
mObjLogger.LogEventAction ("Open")

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Not mObjLogger Is Nothing Then
mObjLogger.LogSheetChangeEvent Sh, Target
End If

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Not mObjLogger Is Nothing Then
mObjLogger.LogSheetSelectionChangeEvent Sh, Target
End If
End Sub

Class module:
Option Explicit
Option Compare Text

Private Type udtLogEntry
Record As String * 24 'Record Number
Date As String * 24
NewCellValue As String * 30
OldCellValue As String * 30
CellRef As String * 15
UserName As String * 14
SheetName As String * 24
NewFormula As String * 30
OldFormula As String * 30
ChangeType As String * 15
End Type

Private mudtEntry As udtLogEntry
Private Const CSTR_CELL_ADJUSTMENT_TYPE As String = "Update"
Private Const CSTR_LOG_FILENAME_SUFFIX As String = "_Audit Logs.txt"

Public Sub LogSheetChangeEvent(ByVal Sh As Object, ByVal Target As Range)

On Error GoTo ERR_HANDLER:
Dim strText As String

If Not ThisWorkbook.ReadOnly Then
If (Target.Rows.Count = 1) And (Target.Columns.Count = 1) Then
mudtEntry.SheetName = CStr(Sh.Name)
mudtEntry.CellRef = CStr(Target.Address)
mudtEntry.ChangeType = CSTR_CELL_ADJUSTMENT_TYPE
mudtEntry.Date = CStr(Now())
mudtEntry.NewCellValue = CStr(Target.Value)
mudtEntry.UserName = Environ("username")
mudtEntry.NewFormula = CStr(Target.Formula)
strText = BuildLogString(mudtEntry.Record, mudtEntry.Date, mudtEntry.NewCellValue, _
mudtEntry.OldCellValue, mudtEntry.CellRef, _
mudtEntry.UserName, mudtEntry.SheetName, mudtEntry.OldFormula, _
mudtEntry.NewFormula, mudtEntry.ChangeType)
Call fnAddToFile(strText)
End If
End If

EXIT_HERE:
Exit Sub
ERR_HANDLER:
GoTo EXIT_HERE
End Sub

Public Sub LogSheetSelectionChangeEvent(ByVal Sh As Object, ByVal Target As Range)

On Error Resume Next
If Not ThisWorkbook.ReadOnly Then
If (Target.Rows.Count = 1) And (Target.Columns.Count = 1) Then

mudtEntry.OldCellValue = CStr(Target.Value)
mudtEntry.OldFormula = CStr(Target.Formula)

Select Case Target.Column
Case Is = 1
mudtEntry.Record = CStr(Target.Columns.Offset(0, 0).Text)
Case Is = 2
mudtEntry.Record = CStr(Target.Columns.Offset(0, -1).Text)
Case Is = 3
mudtEntry.Record = CStr(Target.Columns.Offset(0, -2).Text)
Case Is = 4
mudtEntry.Record = CStr(Target.Columns.Offset(0, -3).Text)
Case Is = 5
mudtEntry.Record = CStr(Target.Columns.Offset(0, -4).Text)
Case Is = 6
mudtEntry.Record = CStr(Target.Columns.Offset(0, -5).Text)
Case Is = 7
mudtEntry.Record = CStr(Target.Columns.Offset(0, -6).Text)
Case Is = 8
mudtEntry.Record = CStr(Target.Columns.Offset(0, -7).Text)
Case Is = 9
mudtEntry.Record = CStr(Target.Columns.Offset(0, -8).Text)
Case Is = 10
mudtEntry.Record = CStr(Target.Columns.Offset(0, -9).Text)
Case Is = 11
mudtEntry.Record = CStr(Target.Columns.Offset(0, -10).Text)
Case Is = 12
mudtEntry.Record = CStr(Target.Columns.Offset(0, -11).Text)
Case Is = 13
mudtEntry.Record = CStr(Target.Columns.Offset(0, -12).Text)
Case Is = 14
mudtEntry.Record = CStr(Target.Columns.Offset(0, -13).Text)
Case Is = 15
mudtEntry.Record = CStr(Target.Columns.Offset(0, -14).Text)
Case Is = 16
mudtEntry.Record = CStr(Target.Columns.Offset(0, -15).Text)
End Select

End If
End If
End Sub

Public Sub LogEventAction(ByVal strEvent As String)
Dim udtEntry As udtLogEntry

udtEntry.Date = vbTab & vbTab & vbTab & Now()
udtEntry.UserName = Environ("username")
udtEntry.ChangeType = strEvent

If Not fnAddToFile(udtEntry.Date & "," & udtEntry.UserName & "," & udtEntry.ChangeType) Then
Debug.Print "Failed to log event"
End If

End Sub

Private Function fnAddToFile(ByVal strText As String) As Boolean

On Error GoTo ERR_HANDLER:
Dim intHandle As Integer
Dim strFileName As String
Dim strFolderName As String

fnAddToFile = False
If ThisWorkbook.ReadOnly Then
fnAddToFile = False
GoTo EXIT_HERE
End If

intHandle = FreeFile
strFileName = Mid(ThisWorkbook.Name, 1, InStr(1, ThisWorkbook.Name, ".") - 1)
strFileName = strFileName & CSTR_LOG_FILENAME_SUFFIX
strFileName = ThisWorkbook.Path & Chr(92) & strFileName

If Not IsLogFilePresent(strFileName) Then
Open strFileName For Append As #intHandle
Dim udtHeader As udtLogEntry
Dim strTitles As String

udtHeader.Record = "Record Name"
udtHeader.Date = "Date & Time"
udtHeader.UserName = "UserName"
udtHeader.ChangeType = "User Action"
udtHeader.SheetName = "Sheet Name"
udtHeader.SheetName = "SheetName"
udtHeader.CellRef = "Cell Ref"
udtHeader.NewCellValue = "New Value"
udtHeader.OldCellValue = "Old Value"
udtHeader.NewFormula = "New Value Formula"
udtHeader.OldFormula = "Old Value Formula"


strTitles = BuildLogString(udtHeader.Record, udtHeader.Date, udtHeader.NewCellValue, _
udtHeader.OldCellValue, udtHeader.CellRef, _
udtHeader.UserName, udtHeader.SheetName, _
udtHeader.OldFormula, udtHeader.NewFormula, _
udtHeader.ChangeType)
Print #intHandle, strTitles
Print #intHandle, strText
Close #intHandle
Else
Open strFileName For Append As #intHandle
Print #intHandle, strText
Close #intHandle
End If

fnAddToFile = True
EXIT_HERE:
Exit Function
ERR_HANDLER:
fnAddToFile = False
GoTo EXIT_HERE
End Function
Private Function BuildLogString(ByVal strRecord As String, ByVal strDate As String, ByVal strNew As String, ByVal strOld As String, _
ByVal strRef As String, ByVal strName As String, ByVal strSheet As String, _
ByVal strOldFormula As String, ByVal strNewFormula As String, ByVal strChangeType As String) As String
Dim strText As String
On Error Resume Next
strSheet = (strSheet) 'Ucase(strSheet)
BuildLogString = _
strRecord & "," & strDate & "," & strName & "," & strChangeType & "," & strSheet & "," & strRef & ", " & strNew & "," & strOld & _
"," & strNewFormula & "," & strOldFormula
End Function

Private Function IsLogFilePresent(ByVal strFile As String) As Boolean
On Error GoTo ERR_HANDLER:
IsLogFilePresent = False
If Trim(Dir(strFile)) <> "" Then
IsLogFilePresent = True
Else
IsLogFilePresent = False
End If
EXIT_HERE:
Exit Function
ERR_HANDLER:
IsLogFilePresent = False
GoTo EXIT_HERE
End Function