Is this macro even possible?
 

All of my macros have been recorded so I don't have a clue how to writ
one from scratch but I haven't been able to record this one.

I would like a macro that does the following things upon execution:

1. Check to see if there is any data in the row above it.
2. If there is, insert a new blank row.
3. Beginning in that blank row, list the date, the values for al
changed data, and the username of the person who made the changes an
continue to insert rows and list that information for all the change
that have been made since the macro was last run.

Is this possible?

:confused

--
eagle_eye
-----------------------------------------------------------------------
eagle_eyes's Profile: http://www.excelforum.com/member.php...fo&userid=3525
View this thread: http://www.excelforum.com/showthread.php?threadid=55031

Is this macro even possible?
 

Yes it is possible but it is not a great project for the faint of heart.
There is a lot to it. You essentially need to write to a storage location (a
blank sheet) all of the changes as they are made (run from the change event).
Your macro then reads that data and inserts it where appropriate (If i
understand what you are trying to do). Like I said though this is NOT a great
project for a first timer or someone who has no experience writing macro's
from scratch...
--
HTH...

Jim Thomlinson


"eagle_eyes" wrote:


All of my macros have been recorded so I don't have a clue how to write
one from scratch but I haven't been able to record this one.

I would like a macro that does the following things upon execution:

1. Check to see if there is any data in the row above it.
2. If there is, insert a new blank row.
3. Beginning in that blank row, list the date, the values for all
changed data, and the username of the person who made the changes and
continue to insert rows and list that information for all the changes
that have been made since the macro was last run.

Is this possible?

:confused:


--
eagle_eyes
------------------------------------------------------------------------
eagle_eyes's Profile: http://www.excelforum.com/member.php...o&userid=35259
View this thread: http://www.excelforum.com/showthread...hreadid=550318

Is this macro even possible?
 

Would it be easier to run the change event and then I could copy th
data over manually? Is that something you could help me with

--
eagle_eye
-----------------------------------------------------------------------
eagle_eyes's Profile: http://www.excelforum.com/member.php...fo&userid=3525
View this thread: http://www.excelforum.com/showthread.php?threadid=55031

Is this macro even possible?
 

eagle_eyes wrote:

Would it be easier to run the change event and then I could copy the
data over manually? Is that something you could help me with?


--
eagle_eyes
------------------------------------------------------------------------
eagle_eyes's Profile: http://www.excelforum.com/member.php...o&userid=35259
View this thread: http://www.excelforum.com/showthread...hreadid=550318

Hi

The code below copies any change in worksheet(this can be all sheets)
to sheet3 (you can make it any sheet you like just change the
apropriate lines) with the date the username the old cell value and the
new one.
I have used a function to get the username by Jake Marx

Copy the function to a standard module and the Worksheet_Change and
Worksheet_SelectionChange events to the sheets you want to monitor.

Public oldVal

Public Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error Resume Next
If oldVal = "" Then
Exit Sub
ElseIf oldVal < Target.Value Then
Target.Copy
Destination:=Worksheets("Sheet3").Range("E65536"). End(xlUp).Offset(1,
0)
Worksheets("Sheet3").Range("E65536").End(xlUp).Off set(0, -3) =
sUserName
Worksheets("Sheet3").Range("E65536").End(xlUp).Off set(0, -2) = _
"Modified cell " & Target.Address & " on " & ActiveSheet.Name
Worksheets("Sheet3").Range("E65536").End(xlUp).Off set(0, -1) = "from
" & oldVal & " to"
Worksheets("Sheet3").Range("E65536").End(xlUp).Off set(0, -4) = Date
End If
Worksheets("Sheet3").Range("A:E").Columns.AutoFit
End Sub

Public Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
oldVal = Target.Value
End Sub


Jake Marx wrote:

To get the username from the operating system, you can use the
GetUserName() function from the Windows API.
Here's an example (just paste this code into
a standard module to use it):

Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, _
nSize As Long) As Long


Public Function sUserName() As String
Dim sName As String * 256
Dim nNullPos As Integer


On Error GoTo ErrHandler


If GetUserName(sName, 256) Then
nNullPos = InStr(sName, vbNullChar)
If nNullPos Then
sUserName = Left$(sName, nNullPos - 1)
Else
sUserName = sName
End If
End If


ExitRoutine:
Exit Function
ErrHandler:
Resume ExitRoutine
End Function



Hope this helps

Christian

Is this macro even possible?
 

Thank Christian!

I opened the VB editor and double-clicked my sheet name and pasted th
function at the bottom onto the blank page. Then I clicke
"Insert--Module" and pasted the other portion of your post onto tha
blank page. I saved them both and closed the editor. Now when I wen
back to the worksheet and clicked "Tools--Macros", there was nothin
in the list.

Did I do something wrong? How do I run these?

Thanks again.:confused

--
eagle_eye
-----------------------------------------------------------------------
eagle_eyes's Profile: http://www.excelforum.com/member.php...fo&userid=3525
View this thread: http://www.excelforum.com/showthread.php?threadid=55031

Is this macro even possible?
 

Can anyone help me with this macro

--
eagle_eye
-----------------------------------------------------------------------
eagle_eyes's Profile: http://www.excelforum.com/member.php...fo&userid=3525
View this thread: http://www.excelforum.com/showthread.php?threadid=55031