メインコンテンツへスキップ

ブックを共有せずに変更を追跡するにはどうすればよいですか？

通常、Excelで変更の追跡機能を適用すると、ブックが同時に共有されます。これは、Excelの一部の機能が無効になるため、煩わしいものになります。 ブックを共有せずに変更を追跡するにはどうすればよいですか？ ここでは、VBAコードをお勧めします。

ブックをVBAコードと共有せずに変更を追跡する

矢印青い右バブル Outlookでテキストを表に変換する

この問題を直接解決する方法はありませんが、柔軟なVBAコードを適用して解決することができます。次のようにしてください。

1。 変更されたセルを追跡するシートタブを右クリックして、 コードを表示 コンテキストメニューから、ポップアウトで アプリケーション向け Microsoft Visual Basic ウィンドウの場合は、次のコードをコピーして空白に貼り付けてください モジュール:

VBAコード：ブックを共有せずに変更を追跡します。

Private Sub Worksheet_Change(ByVal Target As Range)
  'Updateby Extendoffice
    Const xRg As String = "A1:Z1000"
    Dim strOld As String
    Dim strNew As String
    Dim strCmt As String
    Dim xLen As Long
    With Target(1)
        If Intersect(.Cells, Range(xRg)) Is Nothing Then Exit Sub
        strNew = .Text
        Application.EnableEvents = False
        Application.Undo
        strOld = .Text
        .Value = strNew
        Application.EnableEvents = True
        strCmt = "Edit: " & Format$(Now, "dd Mmm YYYY hh:nn:ss") & " by " & _
        Application.UserName & Chr(10) & "Previous Text :- " & strOld
        If Target(1).Comment Is Nothing Then
            .AddComment
        Else
            xLen = Len(.Comment.Shape.TextFrame.Characters.Text)
        End If
        With .Comment.Shape.TextFrame
            .AutoSize = True
            .Characters(Start:=xLen + 1).Insert IIf(xLen, vbLf, "") & strCmt
        End With
    End With
End Sub

共有せずにドキュメントトラックを変更1

Note：上記のコードでは、 A1：Z1000 変更を追跡するデータ範囲です。

2。 次に、このコードウィンドウを保存して閉じます。これで、コードで設定した指定範囲内のセルの値を変更すると、セルが追跡され、ブックは共有されません。 スクリーンショットを参照してください：

共有せずにドキュメントトラックを変更2

最高のオフィス生産性向上ツール

🤖 Kutools AI アシスタント: 以下に基づいてデータ分析に革命をもたらします。 インテリジェントな実行   |  コードを生成  |  カスタム数式の作成  |  データを分析してグラフを生成する  |  Kutools関数を呼び出す...
人気の機能: 重複を検索、強調表示、または識別する   |  空白行を削除する   |  データを失わずに列またはセルを結合する   |   数式なしのラウンド ...
スーパールックアップ: 複数の基準の VLookup  |   複数の値の VLookup  |   複数のシートにわたる VLookup   |   ファジールックアップ ....
詳細ドロップダウン リスト: ドロップダウンリストを素早く作成する   |  依存関係のドロップダウン リスト   |  複数選択のドロップダウンリスト ....
列マネージャー: 特定の数の列を追加する  |  列の移動  |  Toggle 非表示列の表示ステータス  |  範囲と列の比較 ...
注目の機能: グリッドフォーカス   |  デザインビュー   |   ビッグフォーミュラバー   |  ワークブックとシートマネージャー   |  リソースライブラリ (自動テキスト)   |  日付ピッカー   |  ワークシートを組み合わせる   |  セルの暗号化/復号化   |  リストごとにメールを送信する   |  スーパーフィルター   |   特殊フィルター (太字/斜体/取り消し線をフィルター...) ...
上位 15 のツールセット:  12 テキスト ツール (テキストを追加, 文字を削除する、...）   |   50+ チャート 種類 (ガントチャート、...）   |   40+ 実用的 式 (誕生日に基づいて年齢を計算する、...）   |   19 挿入 ツール (QRコードを挿入, パスから画像を挿入、...）   |   12 変換 ツール (数字から言葉へ, 通貨の換算、...）   |   7 マージ＆スプリット ツール (高度な結合行, 分割セル、...）   |   ... もっと

Kutools for Excel で Excel スキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、生産性を向上させ、時間を節約するための300以上の高度な機能を提供します。  最も必要な機能を入手するにはここをクリックしてください...

説明

Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
Comments (14)
No ratings yet. Be the first to rate!
Henry Chaves
Henry Chaves
This comment was minimized by the moderator on the site
Does this MAcro capture changes on formula values everytime my data model is updated?
Reply
0
0
jfjoyner3
jfjoyner3
This comment was minimized by the moderator on the site
Is there a way to record the changes for all tabs in a workbook, and record the changes to a new spreadsheet? THAT would be awesome ... and I can't find any information across the internet with this type of tracking process.
Reply
Report
0
0
skyyang
skyyang     jfjoyner3
This comment was minimized by the moderator on the site
Hello, jfjoyner,To change this code for all sheets, and record the track changes to another sheet, please use the below code:
Note: Please put this code into the ThisWorkbook module.
Option Explicit
Dim mStrRgAddress As String
Dim mStrRgValue As String

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Const xRg As String = "A1:Z1000"
Const xSheetName As String = "Record sheet" 'The sheet that you want to put the track changes, please change the sheet name to your own.
Dim strOld As String
Dim strNew As String
Dim strCmt As String
Dim xLen As Long
Dim xSheet As Worksheet
Dim xRgCell As Range
Dim xRgCell2 As Range
On Error Resume Next
Set xSheet = Application.Sheets.Item(xSheetName)
If mStrRgAddress <> "" Then
Set xRgCell = Range(mStrRgAddress)
If xRgCell.Text <> mStrRgValue Then
strCmt = mStrRgAddress & " : " & Format$(Now, "dd Mmm YYYY hh:nn:ss") & " by " & _
Application.UserName & Chr(10) & "Previous Text :- " & mStrRgValue
Set xRgCell2 = xSheet.Range("a1048576").End(xlUp)
If xRgCell2.AddressLocal = xSheet.Range("A1").AddressLocal Then
If xRgCell2.Value <> "" Then
Set xRgCell2 = xRgCell2.Offset(1, 0)
End If
Else
Set xRgCell2 = xRgCell2.Offset(1, 0)
End If
xRgCell2.Value = strCmt
End If
End If
If xSheet.Name = Sh.Name Then Exit Sub
mStrRgValue = Target.Text
mStrRgAddress = Target.AddressLocal(False, False, , True)
End Sub
View Attachments (1 / 5)
Reply
Report
1
0
jfjoyner3
jfjoyner3     skyyang
This comment was minimized by the moderator on the site
Thanks, this is great, but I can't get it to work. I assume it runs automatically, meaning no need to click on "Run"? I renamed a worksheet to say {--TRACK_CHANGES--} to follow the instructions you left in green. So far, it is not recording anything. Thanks.
Reply
Report
0
0
MandyZhou
MandyZhou     jfjoyner3
This comment was minimized by the moderator on the site
Hello jfjoyner3,First, after you copy our VBA Code into the ThisWorkbook module, the code runs automatically.Second, our VBA code does works. Plesae see the two screenshots I uploaded in this comment.
View Attachments (2 / 5)
Reply
Report
0
0
jfjoyner3
jfjoyner3     MandyZhou
This comment was minimized by the moderator on the site
Mandyzhou, Thank you again for this detailed guidance. I am getting a Syntax Error and it points me to this line:

If mStrRgAddress <> "" Then

Are you able to guide to modify this and remove the Syntax Error?
Thank you!
Reply
Report
0
0
jfjoyner3
jfjoyner3     jfjoyner3
This comment was minimized by the moderator on the site
Thanks again, skyyang.I also noticed that when I pasted this into the module for This Workbook, my large spreadsheet began to calculate continuously and would not stop.  I know nothing about VBA programming, but I found this discussion about the same topic. It might explain why the calculation goes on endlessly. https://www.mrexcel.com/board/threads/continuous-calculation-wont-stop.1179541/page-6#posts 
The issue on this web site was: <span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit;">But once the continuous-calculation issue starts, it's persistent. Pressing the Esc key does interrupt the calculation, but it starts right back up again unless I switch to Manual Calc. Manual calc mode works fine until I run any of the macros, which then ends by re-enabling AutoCalc... I've been through each worksheet in the model and run error-checking to no avail.</span>
Their conclusion was: This is true: once you refer to a form object or its property (frmBudget.startupposition = 3) - the object is then loaded until you unload it or reset your project (State loss - At this point ALL variables are reset and any values lost)
Reply
0
0
MandyZhou
MandyZhou     jfjoyner3
This comment was minimized by the moderator on the site
Hello jfjoyner3,How are you. As you can see in the two screenshots, I changed the "record sheet" into "sheet2" in the VBA Code. 
Then I returned to Excel workbook. After I made some changes in the sheet1, all these changes are recorded in the sheet2.
As for the continuous-calculation issue, could you please send us the screenshots or video of your problem? So we can fully understand what is going on here. Thanks! 
Sincerely,Mandy
View Attachments (2 / 5)
Reply
Report
1
0
jfjoyner3
jfjoyner3     MandyZhou
This comment was minimized by the moderator on the site
MandyZhou, thank you. Does this macro start automatically when I open the spreadsheet? Or must I start it manually? 
The continuous calculation problem is related to another app. 
Report
0
0
jfjoyner3
jfjoyner3     MandyZhou
This comment was minimized by the moderator on the site
Thank you very much!
Report
0
0
MandyZhou
MandyZhou     jfjoyner3
This comment was minimized by the moderator on the site
Hello jfjoyner3,You are welcome. After you save the spreadsheet with the macro VBA code, the macro will start automatically every time you open the spreadsheet. No need to start it manually. Any question, please feel free to contact us. Have a nice day!Best regards,Mandy   
Report
0
0
Kuldeep
Kuldeep
This comment was minimized by the moderator on the site
I tried using this VBA code in my excel sheet. But it gave me errors. I don't know from where this macro should be called and what is the argument to the function you have provided when it is called.
Reply
0
0
Sam
Sam
This comment was minimized by the moderator on the site
Great work. Unfortunately, there are some issues with your code. - It will add a comment even on the first entry of the cell. How can I make it track changes from the second entry not the first one? - Once I enter a value in a cell I can't do "Undo". - It doesn't work with tables. Try to use on a table then try to add or delete a raw and the code will crash. I really wish I have the knowledge to get the code to work the way I want it as described above.
Reply
0
0
Gabe
Gabe     Sam
This comment was minimized by the moderator on the site
I have the same issue. "Undo" and "Redo" buttons don't work anymore. Is there any solution for this?
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations