Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro help
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro help
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 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro help
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" wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro help
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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro help
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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro help
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. T.C wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro help
Thanks once again Dave for your help.
Tina "Dave Peterson" wrote: 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. T.C wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |