Thread: Is this macro even possible?

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?

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?
>
>
>
>
> --
> 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
>
>

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 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).Offset(0, -3) =
sUserName
Worksheets("Sheet3").Range("E65536").End(xlUp).Offset(0, -2) = _
"Modified cell " & Target.Address & " on " & ActiveSheet.Name
Worksheets("Sheet3").Range("E65536").End(xlUp).Offset(0, -1) = "from
" & oldVal & " to"
Worksheets("Sheet3").Range("E65536").End(xlUp).Offset(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

Thank Christian!

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

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

Thanks again.

Can anyone help me with this macro?