Track Changes & Macro's
Apr 23, 2009
I have a work sheet that has 113 tabs and I use two macros (both written with help from this forum - thanks), one to protect all of the worksheets and the other one breaks the workbook into 113 separate files. I want to use the tracking feature because I will be sending out these budget templates and want to identify the changes when I receive them back.
Problem is once I enable the tracking I can no longer run my macro’s; I get a VB dialog box with a red X and the number 400. Anyone know how to work around this problem?
View 9 Replies
ADVERTISEMENT
Jun 19, 2008
I am using the following track changes code on a worksheet;
Track/Report User Changes on an Excel Worksheet/Workbook
in the this Workbook. It involves two different VBA solutions I had gotten form Ozgrid. The top part is VBA code to track changes in the workbook, THe instructions are to put the statement at the top of the module which I did. When it gets to the second VBA code {Starting with Option Explicit} below, I get an error message that "Only comments may appear after End Sub, End Function or End Property.
Also, I would like to get the VBA course offered on this website, any comments?
Dim vOldVal 'Must be at top of module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim bBold As Boolean
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
View 4 Replies
View Related
Jun 17, 2014
I have a spreadsheet that has two Columns.
Column (B) has names of flowers
Column (C) has price
I want to at the top of each hour have those two columns be moved to Col(D) and Col(E) . However if D and E are full I want them in F and G and so on. Essentially i want them to go to the closest empty column to the right.
How to do this automatically in a macro ?
This would allow me to keep track of my employees.
Also is there a way that I could automatically generate a graph of price vs. name for each hour when they are moved ?
View 10 Replies
View Related
Mar 27, 2009
After all the awesome macros I've obtained with the help of all of you, I now have over 30 macros, each in its own module. I have tried without success to re-name the modules with no luck. How is everyone organizing these?
View 2 Replies
View Related
Oct 21, 2009
So I got this code from http://www.ozgrid.com/VBA/track-changes.htm i followed the directions and pasted it in the workbook module but does not seem to be working. I am thinkning the copy and paste feature is messing something up anyone know what?
I have added a worksheet named Log
View 5 Replies
View Related
Aug 6, 2007
I have a list of items that I keep track of when they were last used.
For example:
Item# Last Used
Item#1 8/27/06
Item#2 5/2/07
Item#3 6/30/07
What I would like to do is when I enter a date, it automatically tracks the changes so that I can not only see the most current date but all previous dates if I need to.
The ideal would be to transfer it to another worksheet so that is looks like the original except that is shows multiple dates after it instead of just the most recent.
For example:
Item#1 5/3/05 7/23/06 8/27/06
View 9 Replies
View Related
Dec 15, 2008
We have an excel file that I've developed that people in our department are supposed to be using - not that they want to - but it is an edict from our bosses. Is there a way to track who is actually using that file so I can verify that they are using it rather than just relying on them saying that they are?
View 9 Replies
View Related
Aug 7, 2007
I have found another user's code that will track changes for a single sheet, if that code is in the sheet. This code uses the Worksheet_Change function. However, I have just stumbled acroos the Workbook_Change function. The workbook function is exactly what I need, since I have multiple tabs that need to be tracked. However, the code does not seem to work. I can switch between the two functions, using the same code afterwards, but with the new workbooks phrase it does not work. I tried placing the Workbooks_Change in the "ThisWorkbook" object in VBA, but it does not seem to work there or anywhere else.
I have attached the file. The code is in the "thisworkbook" function.
For quick reference:
Private Sub Workbook_Change(ByVal Target As Range)
Dim r As Long, OutSht As Worksheet
Set OutSht = Sheets("Log")
r = OutSht.Cells(Rows.Count, 1).End(xlUp).Row + 1
OutSht.Cells(r, 1) = Target.Address
OutSht.Cells(r, 2) = Now
OutSht.Cells(r, 3) = Environ("UserName")
OutSht.Cells(r, 4) = Target.Value
OutSht.Columns("A:D").Columns.AutoFit
End Sub
View 7 Replies
View Related
Jun 10, 2014
I am currently trying to find a better way to track quantities of delivered material from multiple suppliers. I have a attached a sample of what I am trying to do. In the "totals" sheet Row 3 is working as planned. There are a couple of issues that I am having with my formatting as is. First off in my "raw" data sheet there is an empty row between each row of numbers (this is they way I am sent the information from the supplier). This empty row causes my totals to place a Q everywhere column A on "Raw" is not filled with "B". Is there a way to have the totals sheet only pull from the cells with values in them?
My other question is how I would be able to get the values for material, Net wt., and Charges to populate if Column A in "Raw" is Q?
Quantity Tracking.xlsx
View 5 Replies
View Related
Jun 17, 2014
I was wondering if there is a function to be able to track when the last time a hyperlink was clicked and display the date in a new cell?
View 1 Replies
View Related
Apr 24, 2009
i am a builder and i want to make a spread sheet to keep track of what i have been paid and the labour costs i have paid out
so i am working on 20 houses that all have 7 stages of payment each, and 5 to 8 men working on them who receive a price for each stage, but are paid a portion of that price each week while they are working on that stage
what i am trying to do is set up a sheet to keep track of what has been paid and the balance remaining for each stage and what has been received
i have tried doing it a few different ways but im none to clever with spread sheets and what is causing me the problem is next to each payment made i need to put the date and the name of the person who was paid so it always seems to just look a cluttered mess
have any of you got any ideas of how i could simplify this and make it look neat and easy
or alternatively is there some other software that may suit this application better?
View 10 Replies
View Related
Jul 2, 2009
So if we take first line the defect id is 10. The date entered is 21-08-2007 and the status is 1-NY ( means new)
NExt line the defect ID is still 10 the date is changed to 22-08-2007 and the previous status is was 1-Ny and the new status is 03- under vurdering (to be
evalueted)
Third line the defect ID is still 10. The date is changed to 04-01-2008. The previous status was 03- Under vurdering ( to be evalueted). The new status is 10-lukket.
I want to be able to track the time from 1-NY to 10-Lukket.
Now the even more tricky part. As you can see other defects go over different statuses e.g defect number 1000 here.
The sheet I have is 13000 rows and I have a total of 2300 defects
1021-08-2007 10:491-Ny
1022-08-2007 15:381-Ny03- Under vurdering
1004-01-2008 13:4703- Under vurdering10-Lukket
10029-08-2007 10:051-Ny
10003-09-2007 14:121-Ny04- Afventer tilretning
10012-09-2007 15:2204- Afventer tilretning10-Lukket
100006-02-2008 11:2901-Ny
100012-02-2008 15:0501-Ny04- Afventer tilretning
100015-08-2008 08:5304- Afventer tilretning10-Lukket
100015-08-2008 22:1310-Lukket04- Afventer tilretning
100004-02-2009 14:0404- Afventer tilretning10-Lukket
View 11 Replies
View Related
Jan 7, 2010
i m trying to use Excel program to keep track of the hours he is working. I know there is a way to do it but it has been many years since I used Excel and can not remember how to do it. He would like to insert the time (hours & minutes) he works in column A and have a sum in the next column.
View 7 Replies
View Related
Oct 7, 2011
If I wanted to use excel to track inventory in/out, is there any way I can do the following?
I have a barcode scanner and I wanted to be able to quickly add and subtract quantities.
So, I want to create three fields at the top of list, one with the function to Add 1 to quantity, one to Subtract 1, and one simply to find (well I guess I can use excels find feature, but that would be an extra step)
When I scan a barcode into the "Add 1 to quantity" field (which only appears at the top of the page), excel will automatically add 1 to the corresponding field next to the item that has that UPC number. The same for Subtracting 1.
View 14 Replies
View Related
Mar 14, 2014
I tried to use Formulas -> Trace Dependents to find which cells is linked to the current cell.
Excel only shows it is linked to the other tab, but don't tell me which tab exactly. How to find the tab which is linked to current cell?
View 3 Replies
View Related
Apr 12, 2008
I'm trying to make a worksheet to track inventory, not for sales but for tracking how many supplies are on hand in storage rooms, closets, etc. The worksheet will be used by as many as 20 different users probably on a network.
Because it will be used by many people, I'm trying to see if there is a way that the worksheet can be updated after every save. In particular, the amount that are "on hand".
For example, say there are 10 of a particular item on hand to start. Joe takes 2 and goes to record it on the worksheet. He inputs the 2 he takes, which now show that there are 8 on hand remaining. He saves the worksheet. Then, Jerry comes and takes 2 and goes to record it on the worksheet.
I can't figure out how, or if it's possible, to have the worksheet show Jerry that there are 8 on hand to start, not 10. So that when he takes his 2, it should show that there are now 6 on hand remaining. I've attached an example worksheet.
View 6 Replies
View Related
Aug 31, 2009
How to track the value of last item in CountIf and work backwards? I am trying to do inventory valuation at the end of the month. For example I have 7 purchases in a month for 1000 units in each purchase order, my on hand inventory is 1500 units. from the purchase details I did a countif to tell me there are 7 PO's in a month for the item, but if i want to find how much each units costed starting from last PO in the CountIf is there a way to pull that number? I have total purchase amount by PO & quantity by PO also in separate columns, so I know cost per unit in each PO but can't figure out how to go backwards (starting from last in)
View 14 Replies
View Related
Feb 7, 2012
I am trying to track installation of equipment in excel, I know what I want done, but not how to do it. I have 8 columns, and I want a yes in each column to equal 12.5% and a column to show progress at the end, I also want to show total progress for installation of all equipment at the bottom of the page
View 4 Replies
View Related
Dec 21, 2006
I've been looking around for a way for Track Changes to reference the loggedin username of the workstation rather than the name in Excel registration.
I found this
Function UserNameWindows() As String
UserNameWindows = Environ("USERNAME")
End Function
: on this board and another blog.
But I don't know how to enter it to make this work. I had read you make it a new module and assign a formula, but the most I could get through was opening the VBA editor for the workbook, creating a new module, and pasting the code. Changes would still reference the Excel registration name, not the Windows' logged in username.
View 9 Replies
View Related
Jun 11, 2007
How can I track date of every last change made in certain cells so my boss can monitor if I am keep working on a project?
View 9 Replies
View Related
Jul 25, 2007
I was wondering if VBA or Excel has the capability to add and total Row Height or Pixels? For example I have my Rows set to a height of 14.25 (19 pixels). My Range that I am working with is A2:T41 for a TOTAL ACCUMULATED ROW HEIGHT of 570 (40 Rows X 14.25) and 760 Pixels (40 X 19).
What I would like to accomplish is that once the TOTAL ACCUMULATED ROW HEIGHT of my Range reaches 570 then Row 1 appears. If the TOTAL is less then 570 then Row 1 remains Hidden.
Does anyone know IF and HOW this can be done? I need this due to the fact that the Rows will Autosize and I need to have my header Row appear. I can't just Freeze Row 1 because my actual Headers are on Row 16.
Hope this makes sense and hope that someone has a solution.
Bye 4 Now,
Mar
View 9 Replies
View Related
Oct 1, 2007
I have created a spreadsheet for my students on a shared drive. They will be able to go there and make comments..My question is this: I know they can have their name set in Excel...How long will the name stay in it. I have to change the speedsheet daily. Does that mean the students will have to enter their name daily. Also how can I track each student comments on the spreedsheet?
View 9 Replies
View Related
Sep 15, 2008
I'm trying to put together a basic spreadsheet to track offensive plays for high school football. I'd like a formula that will change the LOS (Line of Scrimmage) to reflect the yards gained on the previous play. The problem is that you'd probably need negative numbers up to 49, and then back down to 0.
View 10 Replies
View Related
Feb 21, 2009
I created a financial forecast model from scratch. The model is self-contained and has no external links.
Backgrounder
Before firing a question at you, let me explain briefly how the model works. I have an assumptions tab, several costs tab from various departments that ultimately feed the Results tab (target price on a monthly basis) . Let's assume that my target price is on line 200. To me more specific, for Jan09, the target price is on B200, Feb09 on C200 and so on.
Management will feed the model themselves, given that they are assigned specific areas that they can input their forecast.
Issue or question to you
I would like to track historical changes to the price on line 200 on my target page. That is for every individual change and their impact on price.
To illustrate this let assume the following:
Let say User A made several changes on his forecast, I would like the macro to automatically record the individual change that has been made and the impact it had on the target price on line 200.
Assume that User A made two changes on his forecasting sheet. He increased the cost of supplies by $50,000 (on one cell) and increased the cost of labour by $10,000 (on another cell).
What I would love to have is a macro that would record the changes that User A changed; (i.e. cell, tab, user) after he entered the $50,000 and after he entered the $10,000. This would be recorded on a separate sheet.
Thus, immediately he entered the $50,000 on the cell and press it would record the change and the impact it had on price on the target page. Then when he entered the $10,000, it would do the same.
The format of the "changes sheet" that would contain the changes would look something like this:
USER..Date..Cell changed...Tab..From value..To value. Price Before Price after Impact (Price before less price after)
I am aware that there's a lot of details but if you don't understand the issue, it's very difficult for you to resolve.
I assume that it requires a VBA code of some sort to set this up. Given that I am not familiar (a true dummy) with VBA, I would need like a step by step instruction.
View 9 Replies
View Related
Jul 12, 2006
I am running a contest for my office and the theme is 'nascar'. Anyway - we'd like to chart the contestants using a racetrack. Is this possible in Excel or any other MS Office tool?
Basically I'd like to show the placement of ten contestants on a circular racetrack. It would be updated weekly.
View 3 Replies
View Related
Jul 29, 2006
Is there any way to capture the comment that comes when we use "Track Changes", the date & time when the cell content changes should be retrieved in the adjacent automatically. Is there any way to do it using formulas/ VB code.
View 7 Replies
View Related
Sep 13, 2006
How would I write this in vb. If a row or column deleted in sheet1 insert in sheet2 the date it was deleted, the coloumn or row cell name?
View 9 Replies
View Related
Nov 14, 2006
I have written an array formula which you can see in the, "Done something cool in excel" bit on here. Part of this project was to changed/replace three diffence cells on each pass of the loop. I used some code off here which allowed me to display ALL changed on a seperate sheet, but there way way to much data on here and really confused the user. What i would like to do is; in 1 column, for it to say "CHANGED" if the data has changed "EXSISTING" if it hasnt changed and "NEW" if its been added, this will be referencing Column I.
View 7 Replies
View Related
Jun 26, 2007
I believe that someone is using excel's coding features to sabotage a file by entering blank and/or false data into a speadsheet using macros / vb code, and the project is locked for viewing so I cannot view the code. Is it possible to track the changes of vb code by using the 'track changes' feature in Excel? I have pictures of the history sheet and a copy of the file if any of the experts here want to take a look at it.
View 9 Replies
View Related
Feb 1, 2008
The module that you wrote on tracking changes made in a workbook was fabulous I had been looking for so long for that. I was just wondering if there was a way to find out WHO made the changes to the workbook. I am on a network and everyone in my office can make changes to this particular workbook, therefore there are some mistakes. I would like to know who is making the mistakes.
Dim vOldVal 'Must be at top of module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim bBold As Boolean
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
With Application
. ScreenUpdating = False
.EnableEvents = False
End With
If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
bBold = Target.HasFormula
With Sheet1.............................
View 4 Replies
View Related
