If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel virus?
Hello,
I work with a s/sheet that a few other users have access to, every now and then really old data seems to come back into the s/sheet - data that had been deleted from it weeks ago?? How is this possible? How do I check to see if the s/sheet has a virus? :-) |
#2
|
|||
|
|||
Excel virus?
You probably have someone working from a LOCAL copy, a local copy is when someone copies the file to their own pc, so working on it does not update the real workbook, every now and then they may be copying the data over, it's the only logical explanation. You could create a macro that lets you know which user accessed last and which cells they changed. NZuser;308511 Wrote: Hello, I work with a s/sheet that a few other users have access to, every now and then really old data seems to come back into the s/sheet - data that had been deleted from it weeks ago?? How is this possible? How do I check to see if the s/sheet has a virus? :-) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86247 |
#3
|
|||
|
|||
Excel virus?
Hmmm, yes that makes sense! Do you know the macro I can run that gives me the
last user and cells changed? "Simon Lloyd" wrote: You probably have someone working from a LOCAL copy, a local copy is when someone copies the file to their own pc, so working on it does not update the real workbook, every now and then they may be copying the data over, it's the only logical explanation. You could create a macro that lets you know which user accessed last and which cells they changed. NZuser;308511 Wrote: Hello, I work with a s/sheet that a few other users have access to, every now and then really old data seems to come back into the s/sheet - data that had been deleted from it weeks ago?? How is this possible? How do I check to see if the s/sheet has a virus? :-) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86247 |
#4
|
|||
|
|||
Excel virus?
You can create a sheet called Usage, make the sheet xlsheetveryhidden, then paste this in the Thisworkbook code module, Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.screenupdating= false Worksheets("usage").Visible = False If Worksheets("Usage").Visible = True Then Exit Sub End If With ThisWorkbook.Worksheets("usage").Cells(Rows.Count, "A").End(xlUp) .offset(1, 0).Value = Target.Text & Target.Address .offset(1, 1).Value = Sh.Name .offset(1, 2).Value = Format(Now, "dd mmm yyyy, hh:mm") .offset(1, 3).Value = Application.UserName End With Application.screenupdating= true End Sub Now when a user changes something the action will be recorded including which cell and content! NZuser;308511 Wrote: Hello, I work with a s/sheet that a few other users have access to, every now and then really old data seems to come back into the s/sheet - data that had been deleted from it weeks ago?? How is this possible? How do I check to see if the s/sheet has a virus? :-) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86247 |
#5
|
|||
|
|||
Excel virus?
It's not working, I added another w/sheet in the same s/sheet called Usage
then right clicked on the 'Usage' w/sheet tab, selected 'view code' and pasted in your code below - is this correct? "Simon Lloyd" wrote: You can create a sheet called Usage, make the sheet xlsheetveryhidden, then paste this in the Thisworkbook code module, Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.screenupdating= false Worksheets("usage").Visible = False If Worksheets("Usage").Visible = True Then Exit Sub End If With ThisWorkbook.Worksheets("usage").Cells(Rows.Count, "A").End(xlUp) .offset(1, 0).Value = Target.Text & Target.Address .offset(1, 1).Value = Sh.Name .offset(1, 2).Value = Format(Now, "dd mmm yyyy, hh:mm") .offset(1, 3).Value = Application.UserName End With Application.screenupdating= true End Sub Now when a user changes something the action will be recorded including which cell and content! NZuser;308511 Wrote: Hello, I work with a s/sheet that a few other users have access to, every now and then really old data seems to come back into the s/sheet - data that had been deleted from it weeks ago?? How is this possible? How do I check to see if the s/sheet has a virus? :-) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86247 |
#6
|
|||
|
|||
Excel virus?
Ah!, its not a worksheet event code its Workbook event *How to Save a Workbook Event Macro* 1. *Copy* the macro using *CTRL+C* keys. 2. Open your Workbook and *Right Click* on any *Worksheet's Name Tab* 3. *Left Click* on *View Code* in the pop up menu. 4. Press *ALT+F11* keys to open the *Visual Basic Editor.* 5. Press *CTRL+R* keys to shift the focus to the *Project Explorer Window* 6. Press the *Down Arrow Key* until *ThisWorkbook* is highlighted in blue. 7. *Press* the *Enter* key to move the cursor to the *Code Window* 8. *Paste* the macro code using *CTRL+V* 9. *Save* the macro in your Workbook using *CTRL+S* NZuser;308620 Wrote: It's not working, I added another w/sheet in the same s/sheet called Usage then right clicked on the 'Usage' w/sheet tab, selected 'view code' and pasted in your code below - is this correct? "Simon Lloyd" wrote: You can create a sheet called Usage, make the sheet xlsheetveryhidden, then paste this in the Thisworkbook code module, Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.screenupdating= false Worksheets("usage").Visible = False If Worksheets("Usage").Visible = True Then Exit Sub End If With ThisWorkbook.Worksheets("usage").Cells(Rows.Count, "A").End(xlUp) .offset(1, 0).Value = Target.Text & Target.Address .offset(1, 1).Value = Sh.Name .offset(1, 2).Value = Format(Now, "dd mmm yyyy, hh:mm") .offset(1, 3).Value = Application.UserName End With Application.screenupdating= true End Sub Now when a user changes something the action will be recorded including which cell and content! NZuser;308511 Wrote: Hello, I work with a s/sheet that a few other users have access to, every now and then really old data seems to come back into the s/sheet - data that had been deleted from it weeks ago?? How is this possible? How do I check to see if the s/sheet has a virus? :-) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('http://www.thecodecage.com' (http://www.thecodecage.com/)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'http://www.thecodecage.com/forumz/member.php?userid=1' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'http://www.thecodecage.com/forumz/showthread.php?t=86247' (http://www.thecodecage.com/forumz/sh...ad.php?t=86247) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86247 |
#7
|
|||
|
|||
Excel virus?
Wow, thanks!! It's nearly working!!! I did everything you said and it is
automatically hiding the usage worksheet and populating the cells with the user and date etc when I make a change, the only thing it's not showing is the actual changes, it's putting in things like $A$4, what does that mean? "Simon Lloyd" wrote: Ah!, its not a worksheet event code its Workbook event *How to Save a Workbook Event Macro* 1. *Copy* the macro using *CTRL+C* keys. 2. Open your Workbook and *Right Click* on any *Worksheet's Name Tab* 3. *Left Click* on *View Code* in the pop up menu. 4. Press *ALT+F11* keys to open the *Visual Basic Editor.* 5. Press *CTRL+R* keys to shift the focus to the *Project Explorer Window* 6. Press the *Down Arrow Key* until *ThisWorkbook* is highlighted in blue. 7. *Press* the *Enter* key to move the cursor to the *Code Window* 8. *Paste* the macro code using *CTRL+V* 9. *Save* the macro in your Workbook using *CTRL+S* NZuser;308620 Wrote: It's not working, I added another w/sheet in the same s/sheet called Usage then right clicked on the 'Usage' w/sheet tab, selected 'view code' and pasted in your code below - is this correct? "Simon Lloyd" wrote: You can create a sheet called Usage, make the sheet xlsheetveryhidden, then paste this in the Thisworkbook code module, Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.screenupdating= false Worksheets("usage").Visible = False If Worksheets("Usage").Visible = True Then Exit Sub End If With ThisWorkbook.Worksheets("usage").Cells(Rows.Count, "A").End(xlUp) .offset(1, 0).Value = Target.Text & Target.Address .offset(1, 1).Value = Sh.Name .offset(1, 2).Value = Format(Now, "dd mmm yyyy, hh:mm") .offset(1, 3).Value = Application.UserName End With Application.screenupdating= true End Sub Now when a user changes something the action will be recorded including which cell and content! NZuser;308511 Wrote: Hello, I work with a s/sheet that a few other users have access to, every now and then really old data seems to come back into the s/sheet - data that had been deleted from it weeks ago?? How is this possible? How do I check to see if the s/sheet has a virus? :-) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('http://www.thecodecage.com' (http://www.thecodecage.com/)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'http://www.thecodecage.com/forumz/member.php?userid=1' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'http://www.thecodecage.com/forumz/showthread.php?t=86247' (http://www.thecodecage.com/forumz/sh...ad.php?t=86247) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86247 |
#8
|
|||
|
|||
Excel virus?
$A$4 will be the cell that was changed, it should show you what the cell was changed to not what it was, the reason for that is, if you tried to capture what the cell value was before change your workbook would slow up as it would be running the code for every cell that was selected. For further help with it why not join our forums (shown in the link below) it's completely free, if you do join you will have the opportunity to add attachmnets to your posts so you can add workbooks to better illustrate your problems and get help directly with them. Also if you do join please post in this thread (link found below) so that people who have been following or helping with this queries can continue to do so. NZuser;308653 Wrote: Wow, thanks!! It's nearly working!!! I did everything you said and it is automatically hiding the usage worksheet and populating the cells with the user and date etc when I make a change, the only thing it's not showing is the actual changes, it's putting in things like $A$4, what does that mean? "Simon Lloyd" wrote: Ah!, its not a worksheet event code its Workbook event *How to Save a Workbook Event Macro* 1. *Copy* the macro using *CTRL+C* keys. 2. Open your Workbook and *Right Click* on any *Worksheet's Name Tab* 3. *Left Click* on *View Code* in the pop up menu. 4. Press *ALT+F11* keys to open the *Visual Basic Editor.* 5. Press *CTRL+R* keys to shift the focus to the *Project Explorer Window* 6. Press the *Down Arrow Key* until *ThisWorkbook* is highlighted in blue. 7. *Press* the *Enter* key to move the cursor to the *Code Window* 8. *Paste* the macro code using *CTRL+V* 9. *Save* the macro in your Workbook using *CTRL+S* NZuser;308620 Wrote: It's not working, I added another w/sheet in the same s/sheet called Usage then right clicked on the 'Usage' w/sheet tab, selected 'view code' and pasted in your code below - is this correct? "Simon Lloyd" wrote: You can create a sheet called Usage, make the sheet xlsheetveryhidden, then paste this in the Thisworkbook code module, Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.screenupdating= false Worksheets("usage").Visible = False If Worksheets("Usage").Visible = True Then Exit Sub End If With ThisWorkbook.Worksheets("usage").Cells(Rows.Count, "A").End(xlUp) .offset(1, 0).Value = Target.Text & Target.Address .offset(1, 1).Value = Sh.Name .offset(1, 2).Value = Format(Now, "dd mmm yyyy, hh:mm") .offset(1, 3).Value = Application.UserName End With Application.screenupdating= true End Sub Now when a user changes something the action will be recorded including which cell and content! NZuser;308511 Wrote: Hello, I work with a s/sheet that a few other users have access to, every now and then really old data seems to come back into the s/sheet - data that had been deleted from it weeks ago?? How is this possible? How do I check to see if the s/sheet has a virus? :-) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('http://www.thecodecage.com' ('The Code Cage' (http://www.thecodecage.com/))) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'http://www.thecodecage.com/forumz/member.php?userid=1' ('The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)) View this thread: 'http://www.thecodecage.com/forumz/showthread.php?t=86247' ('Excel virus? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=86247)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'Excel virus? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=86247) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86247 |
#9
|
|||
|
|||
Excel virus?
Ok, thank you so much for your help!! :-)
"Simon Lloyd" wrote: $A$4 will be the cell that was changed, it should show you what the cell was changed to not what it was, the reason for that is, if you tried to capture what the cell value was before change your workbook would slow up as it would be running the code for every cell that was selected. For further help with it why not join our forums (shown in the link below) it's completely free, if you do join you will have the opportunity to add attachmnets to your posts so you can add workbooks to better illustrate your problems and get help directly with them. Also if you do join please post in this thread (link found below) so that people who have been following or helping with this queries can continue to do so. NZuser;308653 Wrote: Wow, thanks!! It's nearly working!!! I did everything you said and it is automatically hiding the usage worksheet and populating the cells with the user and date etc when I make a change, the only thing it's not showing is the actual changes, it's putting in things like $A$4, what does that mean? "Simon Lloyd" wrote: Ah!, its not a worksheet event code its Workbook event *How to Save a Workbook Event Macro* 1. *Copy* the macro using *CTRL+C* keys. 2. Open your Workbook and *Right Click* on any *Worksheet's Name Tab* 3. *Left Click* on *View Code* in the pop up menu. 4. Press *ALT+F11* keys to open the *Visual Basic Editor.* 5. Press *CTRL+R* keys to shift the focus to the *Project Explorer Window* 6. Press the *Down Arrow Key* until *ThisWorkbook* is highlighted in blue. 7. *Press* the *Enter* key to move the cursor to the *Code Window* 8. *Paste* the macro code using *CTRL+V* 9. *Save* the macro in your Workbook using *CTRL+S* NZuser;308620 Wrote: It's not working, I added another w/sheet in the same s/sheet called Usage then right clicked on the 'Usage' w/sheet tab, selected 'view code' and pasted in your code below - is this correct? "Simon Lloyd" wrote: You can create a sheet called Usage, make the sheet xlsheetveryhidden, then paste this in the Thisworkbook code module, Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.screenupdating= false Worksheets("usage").Visible = False If Worksheets("Usage").Visible = True Then Exit Sub End If With ThisWorkbook.Worksheets("usage").Cells(Rows.Count, "A").End(xlUp) .offset(1, 0).Value = Target.Text & Target.Address .offset(1, 1).Value = Sh.Name .offset(1, 2).Value = Format(Now, "dd mmm yyyy, hh:mm") .offset(1, 3).Value = Application.UserName End With Application.screenupdating= true End Sub Now when a user changes something the action will be recorded including which cell and content! NZuser;308511 Wrote: Hello, I work with a s/sheet that a few other users have access to, every now and then really old data seems to come back into the s/sheet - data that had been deleted from it weeks ago?? How is this possible? How do I check to see if the s/sheet has a virus? :-) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('http://www.thecodecage.com' ('The Code Cage' (http://www.thecodecage.com/))) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'http://www.thecodecage.com/forumz/member.php?userid=1' ('The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)) View this thread: 'http://www.thecodecage.com/forumz/showthread.php?t=86247' ('Excel virus? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=86247)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'Excel virus? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=86247) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86247 |
Thread Tools | |
Display Modes | |
|
|