Create LOG of an entry and copy it to another file

Hi, I'm trying to develop a module that could help us in the office to have a list with all the monthly task each member of the team has to perform. I've lets say:

I've a file call "Input". From cells O11:AJ200 empty cells that have should be locked if the value in cells AL11:BG200 is "False"

So the cells in within the range O11:AJ200 are unlock, as soon as the task, which is define in column "A" as a entry code, is complete, the member of the tema has to write any value in the cell (it will be nice with VBA that is converted to Monotype Sorts font and the value changed to number 4 in order to show a ticking mark) and automatically a log should be generated into another file.

The other file, let's call it Output, should show in column A: Date/Time and in cell B the name of the user. In column C should show the entry code of the file "Input" and cell "A" concatenated with the name of the column where it was (Range "AL10:BG10"). That means, that when in the same row there are many cells with a ticking mark, in Output there should be the same number of rows including the date, name and entry.

The issue is, the information should come from different worksheets, which I've defined as activeSheet.

Here is something I've tried:

Please Login or Register  to view this content.

Do you have any idea?

Hi, dasle,

the macro you show is static and needs to be triggered by either you or an event. Have you already tried using Worksheet_Change-Event behind the sheet where the changes are being made for triggering the log? This event can also reformat your entries as you wish.

And please add Code-Tags for better readability to the code shown as requested per Forum Rules #3. TIA.

Ciao,
Holger

Hi Holger, thanks for the code tags, I forgot but I've add them.
I know the code is static and I should try what you recommended me, however my biggest problem is that what is happening, this code is just adding them as a line, that means, if I have something like this:

Activities Company 1 Company 2 Company 3
20121211CRM x x x
20121212CRM x
20121213CRM x x

when I apply the code, I'm getting:
DATE USER 20121211CRMCompany 1 20121211CRMCompany 2 20121211CRMCompany 3
20121212CRMCompany 2
20121213CRMCompany 2 20121213CRMCompany 3

and I would like:
DATE USER 20121211CRMCompany 1
DATE USER 20121211CRMCompany 2
DATE USER 20121211CRMCompany 3
DATE USER 20121212CRMCompany 2
DATE USER 20121213CRMCompany 2
DATE USER 20121213CRMCompany 3

Hi, dasle,

if I understand you concern correcty will coud fill up the empty cells in Columns A and B by using a formula and then changing the formula to values.

Why I really pointed out the Worksheet_Change-Event was due to the fact that you can reduce it to single cells and copy each change from there instead of copying whole ranges. If you canīt get the code to work on your own please feel free to come back, I think we could supply a working version of the code.

Ciao,
Hoger

Hi Hoger,
Cells A and B will be filled out with the date/time & Name of the user when an cell between the range

O11:AJ200

has been updated.

What I need is:
1. When a any value (I've restricted it to the cell using data validation) is entered in any cell in the range

O11:AJ200

, in the

Worksheets("Concentrate")

should be writen

in cell A the date/time, in cell B the name and in cell C the code

. If between the range

O11:AJ200

there are values entered into all the columns of the same row, e.g. range

O11:AJ200

; the information entered into QUOTE]Worksheets("Concentrate")[/QUOTE]should be 22 rows having

in cell A the date/time, in cell B the name and in cell C the code

.
2. I will also like to lock any cell in the range range

O11:AJ200

when in the range

AL11:BG200

a condition is not meet "False".

What it make its difficult, is that there might be around 6

ActiveWorkbook.Activesheet

which should feed the

Worksheets("Concentrate")

with just column A to C with information.

Do you think you might have a solution?

Cheers,

Daniel

Hi, Daniel,

maybe you can have a look at this code which goes behind the sheet you want to monitor:

Please Login or Register  to view this content.

I added a column in the survey for the address of the cell which was changed. And I altered Application.Username to Environ("Username") for the name on the system.

I havenīt been on the locking of cells as I would first of all have the copying covered. Question for locking is to either protect the sheet or make the cells unavailable via VBA.

Ciao,
Holger