Macro help

[T.C]

Hi there,

Really appreciate all the help I can get. Thanks a bunch in advance!!

On sheet 3, I have entered the following macro to keep track of the people
who modifies the file.
Private Sub Workbook_Open()
Dim UName, RecPointer
UName = InputBox("Name", "Enter your Name:")
RecPointer = Sheet3.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Sheet3.Cells(RecPointer, 1) = UName
Sheet3.Cells(RecPointer, 2) = Date
Sheet3.Cells(RecPointer, 3) = Time
End Sub

The data is in sheet 2.

There are 3 questions,
1) What macro could be used to keep track of changes in different cells of 4
columns, say "x", "Y", "Z" and "S" from sheet 2 on 4 different coulmns in
sheet 3. The data I would like to track has to do with the item #, source
name, item code and status (specially items that are closed by the modifier).
2) How could we make the name entry on the pop-up window mandatory because
right now if we hit the cancel button it just stores the date only.
3) How can we make the tracking info permanent so that no one can delete that.

Tina

 

[Dave Peterson]

In order of easy to less easy.

#3. There's not much you can do to stop anyone who really wants to destroy that
data.

You could hide the sheet.
You could protect the workbook's structure and protect the worksheet (if it has
to stay visible).

But all of those things can be circumvented if you have a dedicated user who
wants to do damage to the data.

#2. Instead of asking the user for help, you may be able to rely on their
username. And if you don't trust that, you may be able to rely on the excel's
username. (I'd use both.)

I'd also put the date and time in a single cell. It could make doing things
with that data easier (if you have to).

Anyway...

Put this in a General Module:

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function

And this is the alternative Workbook_open event (behind ThisWorkbook):

Option Explicit
Private Sub Workbook_Open()
Dim NextCell As Range

With Sheet3
Set NextCell = .Cells(.Rows.Count, "A").End(xlUp)
If IsEmpty(NextCell.Value) Then
'keep it where it is
Else
Set NextCell = NextCell.Offset(1, 0)
End If

With NextCell
.NumberFormat = "mmm dd, yyyy hh:mm:ss"
.Value = Now
.Offset(0, 1).Value = fOSUserName
.Offset(0, 2).Value = Application.UserName
End With
End With
End Sub

(you could add .unprotect and .protect, if you wanted--but remember worksheet
passwords are easily broken.)

#1. I'd want the same kind of thing as in the workbook_open event. I'd want to
know the date and time, the name of the user, the new value, and the address
that changed.

So I'd use 4 columns to monitor each column that could change.

If that's ok, this is the code that would go in sheet2's module:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Dim myIntersect As Range
Dim DestCell As Range
Dim myAddresses As Variant
Dim aCtr As Long 'address counter
Dim oCol As Long

myAddresses = Array("x:x", "y:y", "z:z", "s:s")

oCol = -3 'It'll start with 1 when 4 is added!
For aCtr = LBound(myAddresses) To UBound(myAddresses)
'using 4 columns per cell change
'1-4, 5-8, 9-12, ...
oCol = oCol + 4
Set myIntersect = Intersect(Target, Me.Range(myAddresses(aCtr)))
If myIntersect Is Nothing Then
'not in that column, do nothing
Else
For Each myCell In myIntersect.Cells
With Sheet4
Set DestCell _
= .Cells(.Rows.Count, oCol).End(xlUp).Offset(1, 0)
With DestCell
.NumberFormat = "mmm dd, yyyy hh:mm:ss"
.Value = Now
.Offset(0, 1).Value = fOSUserName
With .Offset(0, 2)
.NumberFormat = myCell.NumberFormat
.Value = myCell.Value
End With
.Offset(0, 3).Value = myCell.Address(0, 0)
End With
End With
Next myCell
End If
Next aCtr

End Sub


And remember that none of this logging data will be saved if the user doesn't
save the workbook.

 

[T.C]

Thanks Dave! You are a genius! I wanted to write "Dave Peterson, please
help!" in my subject line because I knew you are so good at this but then
thought what if you don't read my question then no one else will help me.

thanks a lot again for helping me and for all you do for all of us!
Tina

 

[Dave Peterson]

I've never thought asking a particular person for help in the subject line was a
good idea, either--for the reason you wrote.

Hope it worked!

 

[T.C]

Had to leave work early so don't know yet but I'm sure it will work! Dave,
do you have any suggestions for a good Visual basic book? I still have a lot
of questions about automatically updating data from websites and other files
stored in shared drives and so on.
Have a great day
Tina

 

[Dave Peterson]

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's books are very good to start.

See if you can find them in your local bookstore/internet site and you can
choose what one you like best.

Maybe someone will jump in with some recommendations for your specific topics.

Had to leave work early so don't know yet but I'm sure it will work! Dave,
do you have any suggestions for a good Visual basic book? I still have a lot
of questions about automatically updating data from websites and other files
stored in shared drives and so on.
Have a great day
Tina

 

[T.C]

Thanks once again Dave for your help.

Tina

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's books are very good to start.

See if you can find them in your local bookstore/internet site and you can
choose what one you like best.

Maybe someone will jump in with some recommendations for your specific topics.