Excel has a facility to record changes to individual cells and places a black triangular mark in the upper left corner, similar to cell comments which uses a red triangle. If you changed content and had a cell comment, you will see both when you hover over the cell to see cell comments. (below)
If Highlight changes is in effect, your workbook is shared, and you will see “[Shared]” on the Title bar.
//See warning about not being able to access macros, format options grayed out//
Help topic: Remove a workbook from shared use
Tools --> Share Workbook --> Editing (tab) --> uncheck Allow changes by more than one person --> OK --> Yes
You may use it alone or with the simple macro to mark cells changed.
Turning off the option turns a rather verbose dialog box (below).
To invoke/remove use Highlight Changes
( pink sheet with pencil)
That button is found during menu customize (view, tools, customize)
Tools, move buttom to toolbar
For Excel 2007 try "Review" tab, and "Changes" box. "Track Changes" is in the lower right
Click on Highlight Changes button
check each of these on
track changes, when: all, who: everyone, highlight change on screen
make some changes, save
click on Highlight Changes button
now check the List changes on a new sheet
(generates History sheet at end of worksheet tabs)
The first time I did the last one, 1/3 of my toolbar turned blank (white) as well as all the menus area. I recycled Excel and okay, but perhaps could have waved another window over it to repaint (??).
HELP (F1) --> answer wizard --> highlight changes Topic: Troubleshoot change highlighting and the History worksheet
something new to try, look at the messages below, you will not be able to change macros while this is in effect. When you turn option off it will have to start all over again. So you don't want to keep your macros in same workbook if want to change them.
example of the cell comment/change tooltip
This action will remove the workbook from shared use. The change
history will be erased, and the other users who are
editing this workbook will not be able to save their changes, even
if you share this workbook again.
Remove the workbook from shared use?
This workbook contains macros recorded or written in Visual Basic.
Macros cannot be viewed or edited in shared workbooks.
Since having this problem have learned about:
Tools --> Share Workbook --> uncheck option
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) '--Sub auto_close() 'Changed from Auto_Close to ThisWorkBook '-- If ThisWorkbook.Saved = True Then Exit Sub '-- 'since auto_close would miss if saved before closed Dim currentsheet As String, currentaddress As String currentsheet = ActiveSheet.Name currentaddress = ActiveCell.Address(1, 1) Application.EnableEvents = False Err.Clear On Error Resume Next Worksheets("SaveHistory").Activate If Err.Number <> 0 Then With ActiveWorkbook .Worksheets.Add(after:= _ .Sheets(.Sheets.Count)).Name = "SaveHistory" [a1] = "LastSaved" [b1] = "Active when saved" [c1] = "active Cell" [d1] = "UserName" [A2].Activate End With Else Range("A1").End(xlDown).Offset(1, 0).Activate End If On Error GoTo 0 ActiveCell.Offset(0, 0) = Now ActiveCell.Offset(0, 1) = currentsheet ActiveCell.Offset(0, 2) = currentaddress ActiveCell.Offset(0, 3) = Application.UserName If ActiveCell.Row < 3 Then Cells.EntireColumn.AutoFit Sheets(currentsheet).Select Application.EnableEvents = True End SubSomething similar can be done if just interested in who opened the workbook see posting by Sandy V.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True 'Get out of edit mode on error resume next 'skip if not found Sheets(Target.Value).Select End Sub
Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved