Tags: cell, default, designed, detect, excel, form, microsoft, msdn, sheet, software, values, whenever

Detect Cell Changes

On Microsoft » Microsoft Excel

6,185 words with 4 Comments; publish: Fri, 23 May 2008 02:25:00 GMT; (30691.80, « »)

I have an excel sheet that I designed to look like a form. That form has

default values. Now, what I want to do is that, whenever there is a

change in any cell, let's say for example, someone change the value of

A4, that change will be recorded in C5 for example.

Let's have a working example,

A4 default value = 5

When someone change the value of A4, C5 will show A4. Meaning, A4 has

been changed.

So goes with other cells with default values.

Let's say for example, the value of A5 has been changed, then C5 will

now show "A4, A5" meaning, these cells were changed.

Thank you so much for your assistance!

--

aga2957

---

aga2957's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25346

View this thread: http://www.excelforum.com/showthread.php?threadid=573561

All Comments

Leave a comment...

  • 4 Comments
    • What if the user changes the value but then changes it back to the orginal

      (default) value ?

      Would you still want to log that ?

      Tim

      "aga2957" <aga2957.2cunxh_1156096210.1688.excel.todaysummary.com.excelforum-nospam.com> wrote in

      message news:aga2957.2cunxh_1156096210.1688.excel.todaysummary.com.excelforum-nospam.com...

      > I have an excel sheet that I designed to look like a form. That form has

      > default values. Now, what I want to do is that, whenever there is a

      > change in any cell, let's say for example, someone change the value of

      > A4, that change will be recorded in C5 for example.

      > Let's have a working example,

      > A4 default value = 5

      > When someone change the value of A4, C5 will show A4. Meaning, A4 has

      > been changed.

      > So goes with other cells with default values.

      > Let's say for example, the value of A5 has been changed, then C5 will

      > now show "A4, A5" meaning, these cells were changed.

      > Thank you so much for your assistance!

      >

      > --

      > aga2957

      > ---

      > aga2957's Profile:

      > http://www.excelforum.com/member.php?action=getinfo&userid=25346

      > View this thread: http://www.excelforum.com/showthread.php?threadid=573561

      >

      #1; Fri, 23 May 2008 02:26:00 GMT
    • Starter for 10:

      In the Worksheet Class Module (the code behind the worksheet)

      Private Sub Worksheet_Change(ByVal Target As Range)

      If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

      Application.EnableEvents = False

      If Range("C5") = "" Then

      Range("C5").Value = Target.Address(False, False)

      Else

      Range("C5").Value = Range("C5").Value _

      & ", " & Target.Address(False, False)

      End If

      Application.EnableEvents = True

      End Sub

      Regards

      Trevor

      "aga2957" <aga2957.2cunxh_1156096210.1688.excel.todaysummary.com.excelforum-nospam.com> wrote in

      message news:aga2957.2cunxh_1156096210.1688.excel.todaysummary.com.excelforum-nospam.com...

      > I have an excel sheet that I designed to look like a form. That form has

      > default values. Now, what I want to do is that, whenever there is a

      > change in any cell, let's say for example, someone change the value of

      > A4, that change will be recorded in C5 for example.

      > Let's have a working example,

      > A4 default value = 5

      > When someone change the value of A4, C5 will show A4. Meaning, A4 has

      > been changed.

      > So goes with other cells with default values.

      > Let's say for example, the value of A5 has been changed, then C5 will

      > now show "A4, A5" meaning, these cells were changed.

      > Thank you so much for your assistance!

      >

      > --

      > aga2957

      > ---

      > aga2957's Profile:

      > http://www.excelforum.com/member.php?action=getinfo&userid=25346

      > View this thread: http://www.excelforum.com/showthread.php?threadid=573561

      >

      #2; Fri, 23 May 2008 02:27:00 GMT
    • As long as you are not intending to use these sheets in HTML, you can use

      the ID property of the cell like the .Tag property of some controls.

      You could set the default value = to this then compare the .Value to the .ID

      to see if you need to log the change or not.

      With Target

      If .Value <> .ID Then

      'Log it

      Else

      'clear log for that cell

      End If

      End With

      However, these ID values are not stored with the workbook, so you would have

      fill them will when you (re-)open the WB each time.

      If this is from a template, you can loop through the cells in question,

      setting .ID=.Value.

      Otherwise it may be of limited use and it may be better to store the default

      values elsewhere, possibly on a hidden sheet.

      NickHK

      "aga2957" <aga2957.2cur65_1156100408.9851.excel.todaysummary.com.excelforum-nospam.com> wrote in

      message news:aga2957.2cur65_1156100408.9851.excel.todaysummary.com.excelforum-nospam.com...

      > If possible, it shouldn't be log since it was changed to its original

      > state or value. But if not, it's would be fine.

      > Thank you so much!

      >

      > --

      > aga2957

      > ---

      > aga2957's Profile:

      http://www.excelforum.com/member.php?action=getinfo&userid=25346

      > View this thread: http://www.excelforum.com/showthread.php?threadid=573561

      >

      #4; Fri, 23 May 2008 02:29:00 GMT