Excel Data Manipulation

[Khairul Islam]

We group of users are reqularly updating an excel sheet, and information in one or two column is very very important. We want to put some control over these columns, so that once data is given, the data should not be excluded. If any one user once to manipulates the given data, the original data should show beside the updated data.

The image is for easy understanding.

Kindly assist.
Thank you

 

[Joe4]

That is most likely going to require VBA. I think you would be best off locking the worksheets, and forcing the user to update the data via a User Form or "prompts", so you can more easily control exactly what is being updated where.

Here is one the prompt way, where they select the cell they want to update then run this code.
Just protect the sheet first to prevent them from editing it directly. You can add a password, if you like (but then will just need to include the password in the Protect/Unprotect statements (see here for those edits: How to PROTECT and UNPROTECT a Sheet using VBA in Excel).
You can also assign the code below to a button, if you wish.

Sub EnterNewValue()

    Dim oldStr As String
    Dim oldLn As Long
    Dim newStr As String
    Dim newLn As Long
   
'   Unprotect sheet
    ActiveSheet.Unprotect
   
'   Store old value
    oldStr = ActiveCell.Value
'   Get length
    oldLn = Len(oldStr)
   
'   Prompt user for new value
    newStr = InputBox("Enter new value")
'   Get length
    newLn = Len(newStr)
   
'   If no previous value, enter new value
    If oldLn = 0 Then
        ActiveCell = newStr
    Else
'       Format old value
        ActiveCell.Value = oldStr & Chr(10) & newStr
        ActiveCell.Characters(Start:=1, Length:=oldLn).Font.Strikethrough = True
        ActiveCell.Characters(Start:=oldLn + 1, Length:=newLn).Font.Strikethrough = False
    End If

'   Protect sheet
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
   
End Sub

 

[Khairul Islam]

Sub EnterNewValue() Dim oldStr As String Dim oldLn As Long Dim newStr As String Dim newLn As Long ' Unprotect sheet ActiveSheet.Unprotect ' Store old value oldStr = ActiveCell.Value ' Get length oldLn = Len(oldStr) ' Prompt user for new value newStr = InputBox("Enter new value") ' Get length newLn = Len(newStr) ' If no previous value, enter new value If oldLn = 0 Then ActiveCell = newStr Else ' Format old value ActiveCell.Value = oldStr & Chr(10) & newStr ActiveCell.Characters(Start:=1, Length:=oldLn).Font.Strikethrough = True ActiveCell.Characters(Start:=oldLn + 1, Length:=newLn).Font.Strikethrough = False End If ' Protect sheet ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub

Thanks for your support.

However, what am looking for this is something else. Like you are maintaining a Excel file, where you are keeping some data (amount), and this the same excel file used by other user, and he is also updating the excel file. Now if the other user internationally revise your given data and changes the data that you have added, then this will be difficult to know whether you put wrong data and this is happened by someone else. To protect this, I would like to add some security over the typing, so if someone once to revise the data which given already, then revised data and first input data should be visible together, and the number of times if the data is revised, the particular cell will show all whole revision history .. can it be possible

 

[Joe4]

Take a look here:

Track changes in a Shared Workbook - Microsoft Support

Use Track Changes to track, maintain, and display information about the changes that are made to a shared workbook.

support.microsoft.com

and here:

How to track changes in Excel

The tutorial explains the basics of Excel Track Changes and shows how to highlight changes on screen, list changes in a separate sheet, accept and reject changes, as well as monitor the last changed cell.

www.ablebits.com

Other than that, VBA (something like I showed you) is the only other method I can think of, where you create your own tracking method.