Worksheet_Change problem

[noob987]

Hi everyone,

I've written a little code in vba but since I'm totally new to vba, it doesn't really do yet what i want.

The code is meant to track changes in a document that is used over different companies. (I know there are better options like office online but this is impossible for several reasons). Now, when someone changes a cell I want to make sure they give a reasoning for their change or otherwise they can't go on.

The problem with this code is that when I double click a cell and don't change anything, the code still asks for a reasoning. Which I would like to avoid.

Here's what I've got already:

Private Sub Worksheet_Change(ByVal Target As Range)    Dim KeyCells As Range
    Dim rng As Range
    Dim MyInput As Variant
    Dim CellContent As Variant
    Set KeyCells = Range("D4:D35")
        
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        
        'ask for reasoning and put it in the comment of the cell.
        MyInput = InputBox("Give a reasoning please.")
        Set rng = Range(Target.Address)
        With rng.AddComment
            .Visible = False
            .Text MyInput
         
            While MyInput = ""
               MyInput = InputBox("Give a reasoning please.")
            Wend
            
        End With
        'Highlight changed cell
        Target.Interior.ColorIndex = 6
    End If
End Sub

My first idea was to read in the entire range in a variable or table or something (before any edits are made) and then compare the new value of the cell with the old one that is stored. But it seems to me that there should be easier solutions...

Does anyone have an idea?
Thank you!

 

[Joe4]

Welcome to the Board!

The problem with this code is that when I double click a cell and don't change anything, the code still asks for a reasoning. Which I would like to avoid.

Why not just add another line of code that checks to see if the cell actually has anything in it, i.e.

If Target <>"" Then

That way, if they double-click, but don't enter anything, it will not ask for reasoning.

 

[noob987]

Thank you for your answer

The problem is when there's already something in the cell. So for example cell C4 contains the number 10. Now someone double clicks the cell by accident, but doesn't want to change the number 10, my code will still ask for a reasoning for his change.

 

[Joe4]

You could check the cell to see if it already has a reasoning.
However, if you want to add a new reasoning if they change an already changed value, then you need to capture the previous value of the cell to see if it has changed. That gets a little bit tricky.
See this thread on how to do that: https://stackoverflow.com/questions...-the-old-value-of-a-changed-cell-in-excel-vba

So basically, you are declaring a global value, capturing the value of a cell as you enter it, and then in your Worksheet_Change code, you would need to compare the current value of Target to the global variable to see if it has changed.

 

[noob987]

Ah nice!

thank you

 

[Joe4]

You are welcome!