Creating an Excel activity log?

[dkmidi]

Does anyone know if there is a way to create a macro that stores any changes to cell to a second worksheet in the same workbook for log/activity purposes?

IE. A Basic example:

Name - Col A Notes - Col B

Joe Working out of office1.
Stan Lateral transfer to region2.
Jan Maternity leave.

So what I'm wondering is, everytime I make changes to a person's notes, is there a way to store the previous note as a new log entry so that I can have a history of what's happened to a particular person?

Thanks,

dk

 

[SkipVought]

Hi, dkmidi ,

Yes, you need to use a combination of Worksheet_SelectionChange event and Worksheet_Change event.

When the selection changes to the column you want to log, you assign the value to a variable

Then if the user changes the value, you can then store the variable in your log.

When interrogating the Target value in the SelectionChange and Change events, be aware that Target can be a range of cells.

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[dkmidi]

Thanks SKip, I'll do some detective work and try to figure it out.

Now the second part...

So when pasting new entries into the second worksheet, how do i get the macro to check for the next available empty cell in the column to paste into?

ie. it needs to check A1 for content, if full, check A2, etc... until it finds an empty cell to paste in.

I'm thinking I'll need a For loop? to incrment the row count of the column? ie. For i=0, increment by 1, check "A"&i?

thanks!

DK

 

[SkipVought]

dkmidi,
Lets say that you are storing your log in Column A. And that the log value is sLogValue.
Then here's how to store your logged value...

If IsEmpty(Range("A1")) Then
   Range("A1").Value = sLogValue
Else
   Range("A1").End(xldown).Offset(1,0).Value = sLogValue
End If

Hope this helps

If you have never worked with events, I can help work you thru that. Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

Here's how to activate and make the event skeletons visible...

There are various Chart, Worksheet and Workbook Events in Excel 97+. There is a skeleton procedure already set up for each event. Here's how to find these Event Procedure Skeletons...

1. alt+F11 - activates the Visual BASIC Editor

2. ctr+R - activates the Project Explorer

3. In the Project Explorer, Open Microsoft Excel Objects

4. Right click the object of choice (Chart, Worksheet or Workbook) and select View Code from the Pop-Up Menu

5. Select the Object DropDown in the upper left-hand corner of the Code Window (F7 activates Code Window)
6. From the Object DropDown, select either Chart, Workbook or Worksheet Object.

7. Select the Procedure DropDown in the upper right-hand corner of the Code Window
8. From the Procedure DropDown, select the Event that you want to control.

9. In the Event Skeleton in the Code Window, enter your code.
Skip,
metzgsk@voughtaircraft.com

 

[dkmidi]

Thanks Skip, I will give it a try!