July 6, 2008 01:06am pdt

1. rorya 1,781,588
2. zorvek 1,432,450
3. saurabh726 990,802
4. chris_bo... 574,207
5. byundt 494,593
6. jeverist 371,758
7. patrickab 332,948
8. nutsch 332,699
9. matthews... 271,097
10. ahammar 271,071
11. RobSampson 225,894
12. webtubbs 208,050
13. jpaulino 193,583
14. spattewar 181,064
15. dj88 175,341

1. zorvek 8,736,941
2. byundt 7,237,682
3. rorya 5,476,319
4. matthews... 2,834,225
5. jeverist 2,782,027
6. patrickab 2,088,511
7. roos01 1,898,139
8. mvidas 1,529,253
9. webtubbs 1,304,442
10. fanpages 1,234,522
11. saurabh726 1,084,381
12. MalicUK 1,059,396
13. brettdj 1,042,576
14. chris_bo... 763,713
15. mgh_mgha... 645,610

02.19.2008 at 11:43PM PST, ID: 23176854

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.2

Using "Track Changes" in Excel VBA

Zone: Microsoft Excel Spreadsheet Software

Tags: Microsoft, Excel, 2003

I've setup Track Changes in Excel to monitor changes by users for a spreadsheet. Was wondering, besides the Worksheet_Change Event, is it possible to write some VBA which at any point in time can look through a range (or UsedRange) and return the values of the changed cells?

Thanks

Start your free trial to view this solution

Question Stats

Zone: Software

Question Asked By: james_daley

Solution Provided By: patrickab

Participating Experts: 3

Solution Grade: B

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

02.20.2008 at 12:40AM PST, ID: 20935981

Rank: Guru

jell:

Not really. There is nothing within Excel VBA that can keep track of previous values to my knowledge. An event on worksheet_change would even be tricky because this only triggers once a cell has already changed, thus losing the previous value.

The only VBA solution as I see it would be to combine a worksheet_selectionchange event (to capture the original value), with a worksheet_change event to capture the new value. You could then write this information plus the username & date/time to a log file, or even a replicate spreadsheet.

Jell

Assisted Solution

02.20.2008 at 03:00AM PST, ID: 20936563

Rank: Genius

rorya:

Are the values you are interested in being changed by manual entry or as the result of formula calculations?
Regards,
Rory

Assisted Solution

02.20.2008 at 03:00PM PST, ID: 20942948

Rank: Genius

patrickab:

james_daley,

Below is a macro that records any changes that are made to the range Sheet1A1:A10 and stores all the changes in the Record sheet. Attached is a file with the macro working. Change any value in the range Sheet1A1:A10 and then check the data retained in the Record sheet.

Change the range to suit your requirements.

Hope it helps

Patrick

                       
             Sub worksheet_change(ByVal target As Range)
Dim myrng As Range
 
Set myrng = Range(Sheets("Sheet1").[A1], Sheets("Sheet1").[A10])
 
If Not Intersect(myrng, target) Is Nothing Then
    Sheets("Record").Cells(65536, 1).End(xlUp).Offset(1, 0) = Date & " " & Time
    Sheets("Record").Cells(65536, 1).End(xlUp).Offset(0, 1) = target.Address
    Sheets("Record").Cells(65536, 1).End(xlUp).Offset(0, 2) = target.Value
    
End If
 
End Sub

Open in New Window

Accepted Solution

02.20.2008 at 03:01PM PST, ID: 20942953

Rank: Genius

patrickab:

Oops pressed the wrong button before attaching the file...

james-daley-01.xls (27 KB) (File Type Details)

Excel file

02.20.2008 at 03:04PM PST, ID: 20942976

Rank: Genius

patrickab:

Note: If a number is deleted from the range Sheet1A1:A10 the macro will record a time and address but no value - which effectively states that the value was deleted. If you want something more obvious let me know.

02.20.2008 at 03:07PM PST, ID: 20942993

Rank: Genius

patrickab:

>If you want something more obvious let me know.

See below...

                       
             Sub worksheet_change(ByVal target As Range)
Dim myrng As Range
 
Set myrng = Range(Sheets("Sheet1").[A1], Sheets("Sheet1").[A10])
 
If Not Intersect(myrng, target) Is Nothing Then
    Sheets("Record").Cells(65536, 1).End(xlUp).Offset(1, 0) = Date & " " & Time
    Sheets("Record").Cells(65536, 1).End(xlUp).Offset(0, 1) = target.Address
    Sheets("Record").Cells(65536, 1).End(xlUp).Offset(0, 2) = target.Value
    If target.Value = "" Then
        Sheets("Record").Cells(65536, 1).End(xlUp).Offset(0, 2) = "Value deleted"
    Else
        Sheets("Record").Cells(65536, 1).End(xlUp).Offset(0, 2) = target.Value
    End If
End If
 
End Sub

Open in New Window

02.20.2008 at 04:02PM PST, ID: 20943553

Rank: Genius

patrickab:

Oops that should have been:

Sub worksheet_change(ByVal target As Range)
Dim myrng As Range

Set myrng = Range(Sheets("Sheet1").[A1], Sheets("Sheet1").[A10])

If Not Intersect(myrng, target) Is Nothing Then
Sheets("Record").Cells(65536, 1).End(xlUp).Offset(1, 0) = Date & " " & Time
Sheets("Record").Cells(65536, 1).End(xlUp).Offset(0, 1) = target.Address
If target.Value = "" Then
Sheets("Record").Cells(65536, 1).End(xlUp).Offset(0, 2) = "Value deleted"
Else
Sheets("Record").Cells(65536, 1).End(xlUp).Offset(0, 2) = target.Value
End If
End If

End Sub

02.20.2008 at 04:13PM PST, ID: 20943665

james_daley:

All Experts,
Thanks for your help with the problem. Will stick to my solution which is pretty similar to patrickab and jell. The values are in a fixed range (so this will work) and they use a function to retrieve data from Reuters.

I'll split the points to all anyway.

Thanks

patrickab

02.20.2008 at 04:39PM PST, ID: 20943868

james_daley,

Attached is a file which:

1. Records the user's name along with the changes made

2. Hides the Record worksheet

3. Only allows a password holder to view the Record worksheet
password - 'open sesame' - without the 's

4. Hides all the code from users that do not know the VBA project code
password - 'jamesdaley' - without the 's, there's no space in the password.

By the way it is not totally secure but it will prevent all but the determined hacker from gaining access to the code.

Hope that helps

Patrick

james-daley-02.xls (36 KB) (File Type Details)

Excel file - p/w protected

rorya

02.21.2008 at 05:36AM PST, ID: 20947541

How come I got points for just asking a question? Seems odd!
Did we mention that shared workbooks are bad?!

patrickab

02.21.2008 at 08:17AM PST, ID: 20949147

james_daley,

As you are not new to Experts Exchange it is not unreasonable to ask these questions/make these observations:

1. jell didn't provide you with a solution - only a suggestion for how a solution might be implemented.

2. My solution did not use jell's rather more elaborate suggested solution.

3. Is there a reason why you have split the points when only one person has provided you with a working solution (me)?

4. Please explain why you have only awarded a B grade for a complete answer that goes way beyond what you had originally asked for - if you wanted to adopt every feature offered.

I look forward to hearing your explanations

Patrick

james_daley

02.25.2008 at 12:05AM PST, ID: 20973536

No i'm not new to experts and given i'm the one who is asking the question then don't you think that i would see the most appropriate solution. If you actually read my question i said "IS IT POSSIBLE...." not give me a solutions, and Jell answered for me. Rorya also asked a question related question about manual calculations which given i've received multiple responses from him in the past was aware that he was about to suggest the Worksheet_Change event. And then there's your response which is the same answer as Jell (Change Event) except you gave me code which i already had and didn't actually ask for.

patrickab

02.25.2008 at 01:33AM PST, ID: 20973868

james_daley,

>is it possible to write some VBA which at any point in time can look through a range (or UsedRange) and return the values of the changed cells?

On the basis if your explanation then the answer to your question is "Yes".

I cannot be bothered with such an unconvincing argument as you have put forward to justify your actions and illogical decisions.

For your future questions I am clear about my decision - I won't bother to contribute.

jell

02.26.2008 at 05:04AM PST, ID: 20983832

lol,

Just to add my bit, my first comment is always largely dependant on the asker's self-classified expertise. In this case, it was intermediate, so I tried to answer the question without 'spoon-feeding' the solution. If further clarification is needed then it will be provided on request, or indeed, as in this case, another expert provides it.

I personally don't think it's unreasonable to give me assist points on that basis. Often I get no points for trying to 'answer' the question that is asked, as distinct from answering a question that I'd wished had been asked. I would happily give them up if my opinion is in the minority however.

Jell

james_daley

03.18.2008 at 01:13AM PDT, ID: 21149092

Thankyou Jell for your thoughts. I agree with your comments which is why i gave you assisted points for your contribution. In response to patrickab's comment, if you would rather not answer my questions then don't because it really doesn't bother me and i would rather give points to expert's who are there to help others out rather than to those, like yourself, who obviously do it for the points and competition to get ahead in rankings.

If you were given no points then i could understand your reaction but where i'm standing (and by the sounds of it - Jell) your comment comes across as greedy and because you're not getting your own way with the points you're going to have a little tantrum.

I would be happy to hear other members *who contributed to this solution) on their thoughts about the issue.

rorya

03.18.2008 at 03:04AM PDT, ID: 21149486

My opinion, FWIW, is that they're your points and you give them out as you see fit, within certain boundaries; grading is always a contentious issue so I have (now) resolved to just leave that alone! :)
On both counts, experts can agree/disagree with your reasoning but can't tell you what to do, unless you are being malicious, which clearly you are not. Our main recourse is simply to ignore questions if we so wish (I suspect a few of us have people on our mental blacklists).
I just didn't think that my question added anything to the solution and it might confuse others seeing it as an Assisted Solution.
Regards,
Rory

20080236-EE-VQP-29 / EE_QW_2_20070628