Keeping data the way it should be...

[IkEcht]

Hi,

I'm basicly looking for pointers more then code this time. I have a quite large workbook full of data, mostly fetched from other excell-files and one access- database. Where the data from these sources is correct most of the time, errors can occur and they are repaired manualy.
Now the data in some of the source-excel-files and the access-database are updated. I want to update the data in my own workbook as well. But how do I create a system that does not overwrite the manualy corrected data or more preferably warns me when I try to do so and lets me choose what I want to do?

I shouldn't be the only one with this kinds of problems, but probably due to the fact I don't know where to look can't find anything about this subject.

thanks,
Jaap

[Bob Phillips]

Protect those cells.

[IkEcht]

Probably a very good solution. I'll report back later on probably. Will have to look into this, how to make sure all users that change data manualy protect these cells (probably some kind of auto-macro for the whole workbook. Then again how does a macro behave if it tries to put data into a protected cell? etc.

I'll start searching, and as said will probably report later on.

[Bob Phillips]

If you have a macro to write to protected cells, you have to unprotect it, change it, then put the protection back on.

[IkEcht]

This gives some nice opportunities to put messageboxes in between warning about overwriting manually repaired data. So yes should do the trick, though as far as I can see now it also takes a quite large change of my current workbook. And still looking for the best way to use the onchange event (probably) to only protect those cells that are manually changed (and only values, layout changes I don't care about).

[Bob Phillips]

You can't use onchange because if they are protected, the message saying so fires and onchange doesn't.

[IkEcht]

Yes but I'm thinking about having them unprotected at start and only switch to protected onchange, so when someones overrides a value manually (and there is another problem coming up I only want this to happen when the changes are made manualy, if the changes are triggered by a macro I don't want any protection to change).

[Bob Phillips]

That should work then.

[IkEcht]

Right now I'm looking into ways to turn off the onchange-event when running a macro. This is tricky though and a way to check if a change is done manualy instead of by macro would be better I think.

Can someone tell me if it is even possible to detect if a change was made manualy and if so where I should look to find out how?

[Bob Phillips]

You would know if a change is made manually because as soon as the change is made you are going to protect it, thus only one change can be made.

[IkEcht]

But how would that save me if I have an onchange fire to protect the cell. It would fire as well when the macro runs and adds or changes data, which is what I don't want to happen.

And I don't want to protect everything that is added by the macro as in the onchange I will ask the person changing things to document the change in a comment-field, which will also give me a chance to count all the changes made to the document manualy.

[Bob Phillips]

The onchange would check which cells are being changed and just react to the particular cells.

[IkEcht]

Yes but would still react to all cells being changed, independently of how the change is made. And I don't want to protect the cells that are being changed through a macro.

Right now my best bet would be to have the onchange trigger protection and the question to insert a comment
and
to have macro's disable the onchange when they are run

That is going to be quite a lot of programming to do as there are quite a lot of macros that can be run.. then again might only do it to those associated with my form first.

[Bob Phillips]

No, you check them like so



If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then

... do something
End If


so the change will only work on cells H1:H10 in this example.

[IkEcht]

Just tested, but this still fires on changes done manualy and through macros.

Might well be I just don't understand what you mean. I did paste the code into a private sub worksheet_change .

Is this what you meant? Or should I do it some other way. And is there another way to catch the difference between cells changed by macro and cells changed manualy?

[Bob Phillips]

Why not post the whole workbook, we are guessing at how you implemented it.

[IkEcht]

It is way too big. Then again there is no implementation yet...
I started this threat to discuss ways to do this, which worked fine, I've got an idea on how to do it now. I think I'll give some of my ideas a try and will report back later.

But the one question that still stands is the question wether there is a way to distinguish between manual input and input through a macro.

[IkEcht]

A new problem arises. Just like the example in the vba-help on "locked" I tried this bit of code on an empty worksheet (the worksheet has it's protection on).

Sub bvaf()

Worksheets("Blad1").Range("b1:b10").Locked = False

End Sub

But though it basicly copies code from the example I get an error 1004 Property Locked of Range Class can not be set (bad translation by the author of this post).

Does anyone know how the locked property works and wether it can be set?

[IkEcht]

It allready solved itself, the property can only be set when sheet protection is off. Too bad for the way i wanted to use it, but last question can be ignored.

[IkEcht]

Well I managed to do a fair bit on this subject today. Think I got most problems tackled (onchange on worksheets that gets disabled by application.enableevents when running macro's from a module).

Now on to a new problem that probably was a problem for more people then just me. Error handling. Is there a way to get the adress of the cell that triggered the error (in my case it always is a protected cell that triggers the error), so I can use the cell-reference in the error-handler msgbox (basicly asking would you like to keep the manual results or overwrite them)

[Bob Phillips]

In the worksheet change event, target is the cell being changed.

[IkEcht]

Alas these changes are outside of the worksheet change as they are made when .enableevents = false .

But as there doesn't seem to be a way (without very much coding) to get the adress of the cell raising the error along to the error handler (and I've looked around quite a bit allready), I think I will have to rethink the way I'm trying to get my security-issues solved.

But just to get a bit more clarity in this discussion, what I'm trying to do is:
Fill the worksheet with data by macro.
In general all cells where data is placed are unlocked.
When a cell has been changed manualy before, that cell is locked.
Pasting a value into this cell by macro now raises an error
I catch that error and try to work with it by letting the user decide wether they want to keep the manual input or overwrite it.

The problem is that the pasting of data by macro is typically done by a statement like:
ActiveWorkbook.Worksheets("Bronnen").Range("b11") = rst!G02Gemsnelheidexcllopen
(where the rst!G02etc is a value taken from an access database).

When this cell "b11" in this case is protected the error gets raised, but I can't pass along the cell where it all happened, so I can't describe the data the user has to choose between.

And yes to make this a bit more clear xld I'll try to make an example workbook (the original is way too big and not that easy to strip, but it shouldn't be too hard to make a workbook that encounters the same problems).

[Bob Phillips]

Without seeing the code/book, it is very hard to be any more constructive.

[IkEcht]

And here is a workbook that has the same problem, should clarify things I hope.