Create log history for cell value change(Excel Vba)
hi guys I create a log history worksheet and save the change detail of other worksheet
here is my code:
Dim oldValue As Variant
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String
sSheetName = "Data"
If ActiveSheet.Name <> "LogDetails" Then
Application.EnableEvents = False
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & "-" & Target.Address(0, 0)
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Formula
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
Sheets("LogDetails").Columns("A:D").AutoFit
Application.EnableEvents = True
End If
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
oldValue = Target.Formula
End Sub
that's work on single cell.
e.g.: if A1 store "ABC" and change to "123", Log Detail will save the Cell address, old value, new value, username and date time
but a big problem is when i select whole column, such as select whole column(B)
it will get the error out and said "type not match"
i know the problem is
oldValue = Target.Value
but how can i rewrite the code so that it also can save the change of column without any error?
can anybody help me?
excel vba excel-vba excel-2010
|
show 4 more comments
hi guys I create a log history worksheet and save the change detail of other worksheet
here is my code:
Dim oldValue As Variant
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String
sSheetName = "Data"
If ActiveSheet.Name <> "LogDetails" Then
Application.EnableEvents = False
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & "-" & Target.Address(0, 0)
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Formula
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
Sheets("LogDetails").Columns("A:D").AutoFit
Application.EnableEvents = True
End If
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
oldValue = Target.Formula
End Sub
that's work on single cell.
e.g.: if A1 store "ABC" and change to "123", Log Detail will save the Cell address, old value, new value, username and date time
but a big problem is when i select whole column, such as select whole column(B)
it will get the error out and said "type not match"
i know the problem is
oldValue = Target.Value
but how can i rewrite the code so that it also can save the change of column without any error?
can anybody help me?
excel vba excel-vba excel-2010
What do you want to happen? I don't really understand "that it also can save the change of column". Do you want to log a selection change? A modification of multiple cells is not easily done by the user, except maybe for Copy+Pasting. The issue witholdValue = Target.Value
is that when multiple cells are selected,Target.Value
will return an array, which you can't store in a simple string variable. Thus the type mismatch. So you need to decide what exactly you want to happen in case of a multi-cell selection? Maybe force the selection to only the first cell?
– Inarion
Nov 21 '18 at 10:31
Also think of the implications of logging multiple changes at the same time: Should a change in all cells of a column (so 1048576 changes) all be logged on their own? Your log sheet will be instantly filled up. Or do you want to somehow compress these changes into fewer lines? (How so?)
– Inarion
Nov 21 '18 at 10:33
sorry for late response, the thing i want to do is, for example, if i change in range("A1") formula: =C1+D1 and then selection a range such as A1-A20 and then Ctrl+D to replace the formula to whole selection. In my mind the record should show 2 record. first one is i replace the formula in range A1 second is i replace the formula in range A2-A20 but i dun know how to display it....
– Nicawong9147
Nov 22 '18 at 1:25
the first i do this vba is i want the change in all cells of a column would be logged on their own, i know that's will be fill up instantly. but my workbook file sometimes got more 2000 record. that would be a great and long process time to do it, so if possbile, 2 record will be show in it.
– Nicawong9147
Nov 22 '18 at 1:35
there is another problem is, when i change the formula of a cell and logged it. the logdetail only show the change of value, not change of formula.(i hope it show =A1+B1 in target . value, something like that). can it be change the display format?
– Nicawong9147
Nov 22 '18 at 1:38
|
show 4 more comments
hi guys I create a log history worksheet and save the change detail of other worksheet
here is my code:
Dim oldValue As Variant
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String
sSheetName = "Data"
If ActiveSheet.Name <> "LogDetails" Then
Application.EnableEvents = False
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & "-" & Target.Address(0, 0)
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Formula
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
Sheets("LogDetails").Columns("A:D").AutoFit
Application.EnableEvents = True
End If
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
oldValue = Target.Formula
End Sub
that's work on single cell.
e.g.: if A1 store "ABC" and change to "123", Log Detail will save the Cell address, old value, new value, username and date time
but a big problem is when i select whole column, such as select whole column(B)
it will get the error out and said "type not match"
i know the problem is
oldValue = Target.Value
but how can i rewrite the code so that it also can save the change of column without any error?
can anybody help me?
excel vba excel-vba excel-2010
hi guys I create a log history worksheet and save the change detail of other worksheet
here is my code:
Dim oldValue As Variant
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String
sSheetName = "Data"
If ActiveSheet.Name <> "LogDetails" Then
Application.EnableEvents = False
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & "-" & Target.Address(0, 0)
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Formula
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
Sheets("LogDetails").Columns("A:D").AutoFit
Application.EnableEvents = True
End If
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
oldValue = Target.Formula
End Sub
that's work on single cell.
e.g.: if A1 store "ABC" and change to "123", Log Detail will save the Cell address, old value, new value, username and date time
but a big problem is when i select whole column, such as select whole column(B)
it will get the error out and said "type not match"
i know the problem is
oldValue = Target.Value
but how can i rewrite the code so that it also can save the change of column without any error?
can anybody help me?
excel vba excel-vba excel-2010
excel vba excel-vba excel-2010
edited Nov 23 '18 at 2:48
asked Nov 21 '18 at 9:42
Nicawong9147
105
105
What do you want to happen? I don't really understand "that it also can save the change of column". Do you want to log a selection change? A modification of multiple cells is not easily done by the user, except maybe for Copy+Pasting. The issue witholdValue = Target.Value
is that when multiple cells are selected,Target.Value
will return an array, which you can't store in a simple string variable. Thus the type mismatch. So you need to decide what exactly you want to happen in case of a multi-cell selection? Maybe force the selection to only the first cell?
– Inarion
Nov 21 '18 at 10:31
Also think of the implications of logging multiple changes at the same time: Should a change in all cells of a column (so 1048576 changes) all be logged on their own? Your log sheet will be instantly filled up. Or do you want to somehow compress these changes into fewer lines? (How so?)
– Inarion
Nov 21 '18 at 10:33
sorry for late response, the thing i want to do is, for example, if i change in range("A1") formula: =C1+D1 and then selection a range such as A1-A20 and then Ctrl+D to replace the formula to whole selection. In my mind the record should show 2 record. first one is i replace the formula in range A1 second is i replace the formula in range A2-A20 but i dun know how to display it....
– Nicawong9147
Nov 22 '18 at 1:25
the first i do this vba is i want the change in all cells of a column would be logged on their own, i know that's will be fill up instantly. but my workbook file sometimes got more 2000 record. that would be a great and long process time to do it, so if possbile, 2 record will be show in it.
– Nicawong9147
Nov 22 '18 at 1:35
there is another problem is, when i change the formula of a cell and logged it. the logdetail only show the change of value, not change of formula.(i hope it show =A1+B1 in target . value, something like that). can it be change the display format?
– Nicawong9147
Nov 22 '18 at 1:38
|
show 4 more comments
What do you want to happen? I don't really understand "that it also can save the change of column". Do you want to log a selection change? A modification of multiple cells is not easily done by the user, except maybe for Copy+Pasting. The issue witholdValue = Target.Value
is that when multiple cells are selected,Target.Value
will return an array, which you can't store in a simple string variable. Thus the type mismatch. So you need to decide what exactly you want to happen in case of a multi-cell selection? Maybe force the selection to only the first cell?
– Inarion
Nov 21 '18 at 10:31
Also think of the implications of logging multiple changes at the same time: Should a change in all cells of a column (so 1048576 changes) all be logged on their own? Your log sheet will be instantly filled up. Or do you want to somehow compress these changes into fewer lines? (How so?)
– Inarion
Nov 21 '18 at 10:33
sorry for late response, the thing i want to do is, for example, if i change in range("A1") formula: =C1+D1 and then selection a range such as A1-A20 and then Ctrl+D to replace the formula to whole selection. In my mind the record should show 2 record. first one is i replace the formula in range A1 second is i replace the formula in range A2-A20 but i dun know how to display it....
– Nicawong9147
Nov 22 '18 at 1:25
the first i do this vba is i want the change in all cells of a column would be logged on their own, i know that's will be fill up instantly. but my workbook file sometimes got more 2000 record. that would be a great and long process time to do it, so if possbile, 2 record will be show in it.
– Nicawong9147
Nov 22 '18 at 1:35
there is another problem is, when i change the formula of a cell and logged it. the logdetail only show the change of value, not change of formula.(i hope it show =A1+B1 in target . value, something like that). can it be change the display format?
– Nicawong9147
Nov 22 '18 at 1:38
What do you want to happen? I don't really understand "that it also can save the change of column". Do you want to log a selection change? A modification of multiple cells is not easily done by the user, except maybe for Copy+Pasting. The issue with
oldValue = Target.Value
is that when multiple cells are selected, Target.Value
will return an array, which you can't store in a simple string variable. Thus the type mismatch. So you need to decide what exactly you want to happen in case of a multi-cell selection? Maybe force the selection to only the first cell?– Inarion
Nov 21 '18 at 10:31
What do you want to happen? I don't really understand "that it also can save the change of column". Do you want to log a selection change? A modification of multiple cells is not easily done by the user, except maybe for Copy+Pasting. The issue with
oldValue = Target.Value
is that when multiple cells are selected, Target.Value
will return an array, which you can't store in a simple string variable. Thus the type mismatch. So you need to decide what exactly you want to happen in case of a multi-cell selection? Maybe force the selection to only the first cell?– Inarion
Nov 21 '18 at 10:31
Also think of the implications of logging multiple changes at the same time: Should a change in all cells of a column (so 1048576 changes) all be logged on their own? Your log sheet will be instantly filled up. Or do you want to somehow compress these changes into fewer lines? (How so?)
– Inarion
Nov 21 '18 at 10:33
Also think of the implications of logging multiple changes at the same time: Should a change in all cells of a column (so 1048576 changes) all be logged on their own? Your log sheet will be instantly filled up. Or do you want to somehow compress these changes into fewer lines? (How so?)
– Inarion
Nov 21 '18 at 10:33
sorry for late response, the thing i want to do is, for example, if i change in range("A1") formula: =C1+D1 and then selection a range such as A1-A20 and then Ctrl+D to replace the formula to whole selection. In my mind the record should show 2 record. first one is i replace the formula in range A1 second is i replace the formula in range A2-A20 but i dun know how to display it....
– Nicawong9147
Nov 22 '18 at 1:25
sorry for late response, the thing i want to do is, for example, if i change in range("A1") formula: =C1+D1 and then selection a range such as A1-A20 and then Ctrl+D to replace the formula to whole selection. In my mind the record should show 2 record. first one is i replace the formula in range A1 second is i replace the formula in range A2-A20 but i dun know how to display it....
– Nicawong9147
Nov 22 '18 at 1:25
the first i do this vba is i want the change in all cells of a column would be logged on their own, i know that's will be fill up instantly. but my workbook file sometimes got more 2000 record. that would be a great and long process time to do it, so if possbile, 2 record will be show in it.
– Nicawong9147
Nov 22 '18 at 1:35
the first i do this vba is i want the change in all cells of a column would be logged on their own, i know that's will be fill up instantly. but my workbook file sometimes got more 2000 record. that would be a great and long process time to do it, so if possbile, 2 record will be show in it.
– Nicawong9147
Nov 22 '18 at 1:35
there is another problem is, when i change the formula of a cell and logged it. the logdetail only show the change of value, not change of formula.(i hope it show =A1+B1 in target . value, something like that). can it be change the display format?
– Nicawong9147
Nov 22 '18 at 1:38
there is another problem is, when i change the formula of a cell and logged it. the logdetail only show the change of value, not change of formula.(i hope it show =A1+B1 in target . value, something like that). can it be change the display format?
– Nicawong9147
Nov 22 '18 at 1:38
|
show 4 more comments
1 Answer
1
active
oldest
votes
I'm not entirely sure if that solves your problem, but try the following modification to your Workbook_SheetSelectionChange
procedure:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then
Target(1).Select
Exit Sub
End If
oldValue = Target.Value
oldAddress = Target.Address
End Sub
Every time the user selects more than one cell the event procedure will change that selection (which raises another change event, with a single-cell target this time) and exit without doing anything else. The criterion for when this selection change should happen can of course be refined to allow for a more specific behavior.
This should make it much harder for the average user to intentionally or accidentally modify more than one cell at a time.
To address questions from your comments:
the undo function of excel cannot be used
This is true. Excel doesn't know how to reverse the actions your code has taken. You need to build this functionality yourself. See this question + accepted answer.
the formula change show in log sheet cannot not show properly, it will show
0
or#Value!
Yes, that's by design. With the line
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Formula
you tell Excel to set that cell's value to a formula. Which Excel then automatically tries to evaluate. (Leading to the errors you're experiencing)
Try the following:
' Prepend the formula with an apostrophe
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = "'" & Target.Formula
This will force Excel to treat the cell value as text and thus it will simply show the formula without evaluating it.
copy and paste a range only show the first cell changes, is it cannot be fix?
This is due to oldValues
being an array while you only ever access its first value. See my implementation:
Option Explicit
Dim oldValues As Variant
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const LogSheet As String = "LogDetails"
If Sh.Name = LogSheet Then Exit Sub
Application.EnableEvents = False
With Worksheets(LogSheet)
Dim idxRows As Long
For idxRows = 1 To Target.Rows.Count
Dim idxCols As Long
For idxCols = 1 To Target.Columns.Count
Dim ChangedCell As Range
Set ChangedCell = Target.Rows(idxRows).Columns(idxCols)
Dim LogRow As Long
LogRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
Dim LogRange As Range
Set LogRange = .Range(.Cells(LogRow, 1), .Cells(LogRow, 5))
LogRange(1).Value = Sh.Name & "!" & ChangedCell.Address(False, False)
LogRange(2).Value = "'" & oldValues(idxRows, idxCols) ' error here when pasting a range of different size than has been selected before pasting
LogRange(3).Value = ChangedCell.Formula
LogRange(4).Value = Environ("username")
LogRange(5).Value = Now
Next idxCols
Next idxRows
.Columns("A:E").AutoFit
End With
Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
oldValues = Target.Formula
End Sub
This has the weakness that when a user copies multiple cells and then selects a single cell and pastes, it will error out, due to mismatching indices. (It works when you copy, e.g. 3 cells in a row, then select 3 other cells in a row and paste.) Not sure how to avoid that. We'd need to capture the size of the pasted range to update oldValues
accordingly. As Excel doesn't expose a Workbook_SheetBeforePaste
event, that seems rather tricky.
Sorry for late response @Inarion, I have assignment these 2 days, really sorry for late. i have tested your code, but when i input something in a black cell, it shows me the error. "type mismatch", what's wrong with it?
– Nicawong9147
Nov 26 '18 at 3:51
@Nicawong9147 I'm not sure what a cell's color has to do with any of this? Can you make sure there's no other property of that cell that could be the culprit?
– Inarion
Nov 27 '18 at 16:39
Sorry, Wrong typing, should be blank cell, not black cell.i put your code in the Thisworkbook and build the LogDetails sheet. No matter what i am input in Sheet1, Sheet2 or Sheet3, just show me the type mismatch.
– Nicawong9147
Nov 29 '18 at 1:19
The error code show in this code: LogRange(2).Value = "'" & oldValues(idxRows, idxCols)
– Nicawong9147
Nov 29 '18 at 1:22
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53409158%2fcreate-log-history-for-cell-value-changeexcel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I'm not entirely sure if that solves your problem, but try the following modification to your Workbook_SheetSelectionChange
procedure:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then
Target(1).Select
Exit Sub
End If
oldValue = Target.Value
oldAddress = Target.Address
End Sub
Every time the user selects more than one cell the event procedure will change that selection (which raises another change event, with a single-cell target this time) and exit without doing anything else. The criterion for when this selection change should happen can of course be refined to allow for a more specific behavior.
This should make it much harder for the average user to intentionally or accidentally modify more than one cell at a time.
To address questions from your comments:
the undo function of excel cannot be used
This is true. Excel doesn't know how to reverse the actions your code has taken. You need to build this functionality yourself. See this question + accepted answer.
the formula change show in log sheet cannot not show properly, it will show
0
or#Value!
Yes, that's by design. With the line
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Formula
you tell Excel to set that cell's value to a formula. Which Excel then automatically tries to evaluate. (Leading to the errors you're experiencing)
Try the following:
' Prepend the formula with an apostrophe
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = "'" & Target.Formula
This will force Excel to treat the cell value as text and thus it will simply show the formula without evaluating it.
copy and paste a range only show the first cell changes, is it cannot be fix?
This is due to oldValues
being an array while you only ever access its first value. See my implementation:
Option Explicit
Dim oldValues As Variant
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const LogSheet As String = "LogDetails"
If Sh.Name = LogSheet Then Exit Sub
Application.EnableEvents = False
With Worksheets(LogSheet)
Dim idxRows As Long
For idxRows = 1 To Target.Rows.Count
Dim idxCols As Long
For idxCols = 1 To Target.Columns.Count
Dim ChangedCell As Range
Set ChangedCell = Target.Rows(idxRows).Columns(idxCols)
Dim LogRow As Long
LogRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
Dim LogRange As Range
Set LogRange = .Range(.Cells(LogRow, 1), .Cells(LogRow, 5))
LogRange(1).Value = Sh.Name & "!" & ChangedCell.Address(False, False)
LogRange(2).Value = "'" & oldValues(idxRows, idxCols) ' error here when pasting a range of different size than has been selected before pasting
LogRange(3).Value = ChangedCell.Formula
LogRange(4).Value = Environ("username")
LogRange(5).Value = Now
Next idxCols
Next idxRows
.Columns("A:E").AutoFit
End With
Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
oldValues = Target.Formula
End Sub
This has the weakness that when a user copies multiple cells and then selects a single cell and pastes, it will error out, due to mismatching indices. (It works when you copy, e.g. 3 cells in a row, then select 3 other cells in a row and paste.) Not sure how to avoid that. We'd need to capture the size of the pasted range to update oldValues
accordingly. As Excel doesn't expose a Workbook_SheetBeforePaste
event, that seems rather tricky.
Sorry for late response @Inarion, I have assignment these 2 days, really sorry for late. i have tested your code, but when i input something in a black cell, it shows me the error. "type mismatch", what's wrong with it?
– Nicawong9147
Nov 26 '18 at 3:51
@Nicawong9147 I'm not sure what a cell's color has to do with any of this? Can you make sure there's no other property of that cell that could be the culprit?
– Inarion
Nov 27 '18 at 16:39
Sorry, Wrong typing, should be blank cell, not black cell.i put your code in the Thisworkbook and build the LogDetails sheet. No matter what i am input in Sheet1, Sheet2 or Sheet3, just show me the type mismatch.
– Nicawong9147
Nov 29 '18 at 1:19
The error code show in this code: LogRange(2).Value = "'" & oldValues(idxRows, idxCols)
– Nicawong9147
Nov 29 '18 at 1:22
add a comment |
I'm not entirely sure if that solves your problem, but try the following modification to your Workbook_SheetSelectionChange
procedure:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then
Target(1).Select
Exit Sub
End If
oldValue = Target.Value
oldAddress = Target.Address
End Sub
Every time the user selects more than one cell the event procedure will change that selection (which raises another change event, with a single-cell target this time) and exit without doing anything else. The criterion for when this selection change should happen can of course be refined to allow for a more specific behavior.
This should make it much harder for the average user to intentionally or accidentally modify more than one cell at a time.
To address questions from your comments:
the undo function of excel cannot be used
This is true. Excel doesn't know how to reverse the actions your code has taken. You need to build this functionality yourself. See this question + accepted answer.
the formula change show in log sheet cannot not show properly, it will show
0
or#Value!
Yes, that's by design. With the line
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Formula
you tell Excel to set that cell's value to a formula. Which Excel then automatically tries to evaluate. (Leading to the errors you're experiencing)
Try the following:
' Prepend the formula with an apostrophe
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = "'" & Target.Formula
This will force Excel to treat the cell value as text and thus it will simply show the formula without evaluating it.
copy and paste a range only show the first cell changes, is it cannot be fix?
This is due to oldValues
being an array while you only ever access its first value. See my implementation:
Option Explicit
Dim oldValues As Variant
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const LogSheet As String = "LogDetails"
If Sh.Name = LogSheet Then Exit Sub
Application.EnableEvents = False
With Worksheets(LogSheet)
Dim idxRows As Long
For idxRows = 1 To Target.Rows.Count
Dim idxCols As Long
For idxCols = 1 To Target.Columns.Count
Dim ChangedCell As Range
Set ChangedCell = Target.Rows(idxRows).Columns(idxCols)
Dim LogRow As Long
LogRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
Dim LogRange As Range
Set LogRange = .Range(.Cells(LogRow, 1), .Cells(LogRow, 5))
LogRange(1).Value = Sh.Name & "!" & ChangedCell.Address(False, False)
LogRange(2).Value = "'" & oldValues(idxRows, idxCols) ' error here when pasting a range of different size than has been selected before pasting
LogRange(3).Value = ChangedCell.Formula
LogRange(4).Value = Environ("username")
LogRange(5).Value = Now
Next idxCols
Next idxRows
.Columns("A:E").AutoFit
End With
Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
oldValues = Target.Formula
End Sub
This has the weakness that when a user copies multiple cells and then selects a single cell and pastes, it will error out, due to mismatching indices. (It works when you copy, e.g. 3 cells in a row, then select 3 other cells in a row and paste.) Not sure how to avoid that. We'd need to capture the size of the pasted range to update oldValues
accordingly. As Excel doesn't expose a Workbook_SheetBeforePaste
event, that seems rather tricky.
Sorry for late response @Inarion, I have assignment these 2 days, really sorry for late. i have tested your code, but when i input something in a black cell, it shows me the error. "type mismatch", what's wrong with it?
– Nicawong9147
Nov 26 '18 at 3:51
@Nicawong9147 I'm not sure what a cell's color has to do with any of this? Can you make sure there's no other property of that cell that could be the culprit?
– Inarion
Nov 27 '18 at 16:39
Sorry, Wrong typing, should be blank cell, not black cell.i put your code in the Thisworkbook and build the LogDetails sheet. No matter what i am input in Sheet1, Sheet2 or Sheet3, just show me the type mismatch.
– Nicawong9147
Nov 29 '18 at 1:19
The error code show in this code: LogRange(2).Value = "'" & oldValues(idxRows, idxCols)
– Nicawong9147
Nov 29 '18 at 1:22
add a comment |
I'm not entirely sure if that solves your problem, but try the following modification to your Workbook_SheetSelectionChange
procedure:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then
Target(1).Select
Exit Sub
End If
oldValue = Target.Value
oldAddress = Target.Address
End Sub
Every time the user selects more than one cell the event procedure will change that selection (which raises another change event, with a single-cell target this time) and exit without doing anything else. The criterion for when this selection change should happen can of course be refined to allow for a more specific behavior.
This should make it much harder for the average user to intentionally or accidentally modify more than one cell at a time.
To address questions from your comments:
the undo function of excel cannot be used
This is true. Excel doesn't know how to reverse the actions your code has taken. You need to build this functionality yourself. See this question + accepted answer.
the formula change show in log sheet cannot not show properly, it will show
0
or#Value!
Yes, that's by design. With the line
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Formula
you tell Excel to set that cell's value to a formula. Which Excel then automatically tries to evaluate. (Leading to the errors you're experiencing)
Try the following:
' Prepend the formula with an apostrophe
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = "'" & Target.Formula
This will force Excel to treat the cell value as text and thus it will simply show the formula without evaluating it.
copy and paste a range only show the first cell changes, is it cannot be fix?
This is due to oldValues
being an array while you only ever access its first value. See my implementation:
Option Explicit
Dim oldValues As Variant
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const LogSheet As String = "LogDetails"
If Sh.Name = LogSheet Then Exit Sub
Application.EnableEvents = False
With Worksheets(LogSheet)
Dim idxRows As Long
For idxRows = 1 To Target.Rows.Count
Dim idxCols As Long
For idxCols = 1 To Target.Columns.Count
Dim ChangedCell As Range
Set ChangedCell = Target.Rows(idxRows).Columns(idxCols)
Dim LogRow As Long
LogRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
Dim LogRange As Range
Set LogRange = .Range(.Cells(LogRow, 1), .Cells(LogRow, 5))
LogRange(1).Value = Sh.Name & "!" & ChangedCell.Address(False, False)
LogRange(2).Value = "'" & oldValues(idxRows, idxCols) ' error here when pasting a range of different size than has been selected before pasting
LogRange(3).Value = ChangedCell.Formula
LogRange(4).Value = Environ("username")
LogRange(5).Value = Now
Next idxCols
Next idxRows
.Columns("A:E").AutoFit
End With
Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
oldValues = Target.Formula
End Sub
This has the weakness that when a user copies multiple cells and then selects a single cell and pastes, it will error out, due to mismatching indices. (It works when you copy, e.g. 3 cells in a row, then select 3 other cells in a row and paste.) Not sure how to avoid that. We'd need to capture the size of the pasted range to update oldValues
accordingly. As Excel doesn't expose a Workbook_SheetBeforePaste
event, that seems rather tricky.
I'm not entirely sure if that solves your problem, but try the following modification to your Workbook_SheetSelectionChange
procedure:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then
Target(1).Select
Exit Sub
End If
oldValue = Target.Value
oldAddress = Target.Address
End Sub
Every time the user selects more than one cell the event procedure will change that selection (which raises another change event, with a single-cell target this time) and exit without doing anything else. The criterion for when this selection change should happen can of course be refined to allow for a more specific behavior.
This should make it much harder for the average user to intentionally or accidentally modify more than one cell at a time.
To address questions from your comments:
the undo function of excel cannot be used
This is true. Excel doesn't know how to reverse the actions your code has taken. You need to build this functionality yourself. See this question + accepted answer.
the formula change show in log sheet cannot not show properly, it will show
0
or#Value!
Yes, that's by design. With the line
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Formula
you tell Excel to set that cell's value to a formula. Which Excel then automatically tries to evaluate. (Leading to the errors you're experiencing)
Try the following:
' Prepend the formula with an apostrophe
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = "'" & Target.Formula
This will force Excel to treat the cell value as text and thus it will simply show the formula without evaluating it.
copy and paste a range only show the first cell changes, is it cannot be fix?
This is due to oldValues
being an array while you only ever access its first value. See my implementation:
Option Explicit
Dim oldValues As Variant
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const LogSheet As String = "LogDetails"
If Sh.Name = LogSheet Then Exit Sub
Application.EnableEvents = False
With Worksheets(LogSheet)
Dim idxRows As Long
For idxRows = 1 To Target.Rows.Count
Dim idxCols As Long
For idxCols = 1 To Target.Columns.Count
Dim ChangedCell As Range
Set ChangedCell = Target.Rows(idxRows).Columns(idxCols)
Dim LogRow As Long
LogRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
Dim LogRange As Range
Set LogRange = .Range(.Cells(LogRow, 1), .Cells(LogRow, 5))
LogRange(1).Value = Sh.Name & "!" & ChangedCell.Address(False, False)
LogRange(2).Value = "'" & oldValues(idxRows, idxCols) ' error here when pasting a range of different size than has been selected before pasting
LogRange(3).Value = ChangedCell.Formula
LogRange(4).Value = Environ("username")
LogRange(5).Value = Now
Next idxCols
Next idxRows
.Columns("A:E").AutoFit
End With
Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
oldValues = Target.Formula
End Sub
This has the weakness that when a user copies multiple cells and then selects a single cell and pastes, it will error out, due to mismatching indices. (It works when you copy, e.g. 3 cells in a row, then select 3 other cells in a row and paste.) Not sure how to avoid that. We'd need to capture the size of the pasted range to update oldValues
accordingly. As Excel doesn't expose a Workbook_SheetBeforePaste
event, that seems rather tricky.
edited Nov 23 '18 at 16:23
answered Nov 21 '18 at 10:44
Inarion
472211
472211
Sorry for late response @Inarion, I have assignment these 2 days, really sorry for late. i have tested your code, but when i input something in a black cell, it shows me the error. "type mismatch", what's wrong with it?
– Nicawong9147
Nov 26 '18 at 3:51
@Nicawong9147 I'm not sure what a cell's color has to do with any of this? Can you make sure there's no other property of that cell that could be the culprit?
– Inarion
Nov 27 '18 at 16:39
Sorry, Wrong typing, should be blank cell, not black cell.i put your code in the Thisworkbook and build the LogDetails sheet. No matter what i am input in Sheet1, Sheet2 or Sheet3, just show me the type mismatch.
– Nicawong9147
Nov 29 '18 at 1:19
The error code show in this code: LogRange(2).Value = "'" & oldValues(idxRows, idxCols)
– Nicawong9147
Nov 29 '18 at 1:22
add a comment |
Sorry for late response @Inarion, I have assignment these 2 days, really sorry for late. i have tested your code, but when i input something in a black cell, it shows me the error. "type mismatch", what's wrong with it?
– Nicawong9147
Nov 26 '18 at 3:51
@Nicawong9147 I'm not sure what a cell's color has to do with any of this? Can you make sure there's no other property of that cell that could be the culprit?
– Inarion
Nov 27 '18 at 16:39
Sorry, Wrong typing, should be blank cell, not black cell.i put your code in the Thisworkbook and build the LogDetails sheet. No matter what i am input in Sheet1, Sheet2 or Sheet3, just show me the type mismatch.
– Nicawong9147
Nov 29 '18 at 1:19
The error code show in this code: LogRange(2).Value = "'" & oldValues(idxRows, idxCols)
– Nicawong9147
Nov 29 '18 at 1:22
Sorry for late response @Inarion, I have assignment these 2 days, really sorry for late. i have tested your code, but when i input something in a black cell, it shows me the error. "type mismatch", what's wrong with it?
– Nicawong9147
Nov 26 '18 at 3:51
Sorry for late response @Inarion, I have assignment these 2 days, really sorry for late. i have tested your code, but when i input something in a black cell, it shows me the error. "type mismatch", what's wrong with it?
– Nicawong9147
Nov 26 '18 at 3:51
@Nicawong9147 I'm not sure what a cell's color has to do with any of this? Can you make sure there's no other property of that cell that could be the culprit?
– Inarion
Nov 27 '18 at 16:39
@Nicawong9147 I'm not sure what a cell's color has to do with any of this? Can you make sure there's no other property of that cell that could be the culprit?
– Inarion
Nov 27 '18 at 16:39
Sorry, Wrong typing, should be blank cell, not black cell.i put your code in the Thisworkbook and build the LogDetails sheet. No matter what i am input in Sheet1, Sheet2 or Sheet3, just show me the type mismatch.
– Nicawong9147
Nov 29 '18 at 1:19
Sorry, Wrong typing, should be blank cell, not black cell.i put your code in the Thisworkbook and build the LogDetails sheet. No matter what i am input in Sheet1, Sheet2 or Sheet3, just show me the type mismatch.
– Nicawong9147
Nov 29 '18 at 1:19
The error code show in this code: LogRange(2).Value = "'" & oldValues(idxRows, idxCols)
– Nicawong9147
Nov 29 '18 at 1:22
The error code show in this code: LogRange(2).Value = "'" & oldValues(idxRows, idxCols)
– Nicawong9147
Nov 29 '18 at 1:22
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53409158%2fcreate-log-history-for-cell-value-changeexcel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What do you want to happen? I don't really understand "that it also can save the change of column". Do you want to log a selection change? A modification of multiple cells is not easily done by the user, except maybe for Copy+Pasting. The issue with
oldValue = Target.Value
is that when multiple cells are selected,Target.Value
will return an array, which you can't store in a simple string variable. Thus the type mismatch. So you need to decide what exactly you want to happen in case of a multi-cell selection? Maybe force the selection to only the first cell?– Inarion
Nov 21 '18 at 10:31
Also think of the implications of logging multiple changes at the same time: Should a change in all cells of a column (so 1048576 changes) all be logged on their own? Your log sheet will be instantly filled up. Or do you want to somehow compress these changes into fewer lines? (How so?)
– Inarion
Nov 21 '18 at 10:33
sorry for late response, the thing i want to do is, for example, if i change in range("A1") formula: =C1+D1 and then selection a range such as A1-A20 and then Ctrl+D to replace the formula to whole selection. In my mind the record should show 2 record. first one is i replace the formula in range A1 second is i replace the formula in range A2-A20 but i dun know how to display it....
– Nicawong9147
Nov 22 '18 at 1:25
the first i do this vba is i want the change in all cells of a column would be logged on their own, i know that's will be fill up instantly. but my workbook file sometimes got more 2000 record. that would be a great and long process time to do it, so if possbile, 2 record will be show in it.
– Nicawong9147
Nov 22 '18 at 1:35
there is another problem is, when i change the formula of a cell and logged it. the logdetail only show the change of value, not change of formula.(i hope it show =A1+B1 in target . value, something like that). can it be change the display format?
– Nicawong9147
Nov 22 '18 at 1:38