Raghav Chamadiya
New Member
- Joined
- May 31, 2020
- Messages
- 13
- Office Version
- 2016
- Platform
- Windows
So, I have a userform which I am using for both submit and editing the database. The requirement is to track the edit changes in a sheet like this:
I am struggling with the columns C, D, and E which are edited parameters, their old and new values. For that this is the code I have so far,
The problem I have with this code is that it is giving an object required error everytime I try to edit something at the line:
Please help me, I know it might be a minor silly mistake, but I am stuck on it from way too long. Thanks in advance
I am struggling with the columns C, D, and E which are edited parameters, their old and new values. For that this is the code I have so far,
VBA Code:
Option Explicit
Global oldValues, newValues, titles
Global sep
Sub Edit()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.AutomationSecurity = msoAutomationSecurityLow
Dim id As Range
Set id = Worksheets("Transport Assesment").Range("A:A").Find(what:=frm3.TextBox1.Value, LookIn:=xlValues)
oldValues = ""
newValues = ""
titles = ""
Worksheets("Transport Assesment").Unprotect "Ramu@lt12"
If Not id Is Nothing Then
LogChanges id.Offset(, 1).Value, frm3.TextBox13.Value
LogChanges id.Offset(, 2).Value, frm3.DTPicker2.Value
LogChanges id.Offset(, 4).Value, frm3.ComboBox7.Value
LogChanges id.Offset(, 3).Value, frm3.ComboBox8.Value
LogChanges id.Offset(, 5).Value, frm3.ComboBox6.Value
LogChanges id.Offset(, 25).Value, frm3.TextBox15.Value
LogChanges id.Offset(, 6).Value, frm3.ComboBox1.Value
LogChanges id.Offset(, 7).Value, frm3.TextBox2.Value
LogChanges id.Offset(, 8).Value, frm3.ComboBox2.Value
LogChanges id.Offset(, 9).Value, frm3.TextBox3.Value
LogChanges id.Offset(, 10).Value, frm3.ComboBox3.Value
LogChanges id.Offset(, 11).Value, frm3.TextBox4.Value
LogChanges id.Offset(, 12).Value, frm3.ComboBox4.Value
LogChanges id.Offset(, 13).Value, frm3.TextBox5.Value
LogChanges id.Offset(, 14).Value, frm3.ComboBox5.Value
LogChanges id.Offset(, 15).Value, frm3.TextBox6.Value
LogChanges id.Offset(, 16).Value, frm3.DTPicker3.Value
LogChanges id.Offset(, 17).Value, frm3.TextBox14.Value
LogChanges id.Offset(, 18).Value, frm3.DTPicker4.Value
LogChanges id.Offset(, 19).Value, frm3.TextBox9.Value
LogChanges id.Offset(, 20).Value, frm3.DTPicker1.Value
LogChanges id.Offset(, 26).Value, frm3.TextBox16.Value
If Len(titles) > 0 Then
MsgBox (titles)
End If
Worksheets("Transport Assesment").Protect "Ramu@lt12"
Else
MsgBox "No match found!"
End If
End Sub
Sub LogChanges(c As Range, vNew)
With c
sep = IIf(Len(titles) > 0, ";", "") 'need a separator?
If .Value <> vNew Then
'track the changes
titles = titles & sep & .Parent.Cells(1, .Column).Value 'column titles in Row1
oldValues = oldValues & sep & ValueOrBlank(.Value) 'track old value
newValues = newValues & sep & ValueOrBlank(vNew) 'track new value
.Value = vNew 'update the cell
End If
End With
End Sub
Function ValueOrBlank(v)
ValueOrBlank = IIf(Len(v) > 0, v, "[blank]")
End Function
The problem I have with this code is that it is giving an object required error everytime I try to edit something at the line:
VBA Code:
LogChanges id.Offset(, 1).Value, frm3.TextBox13.Value
Please help me, I know it might be a minor silly mistake, but I am stuck on it from way too long. Thanks in advance