How To Make Audit Trail When First Change Is Made Right After Opening Excel File

Jul 30, 2012

Pls see below codes, the codes can record down changes made "from" and "to" and put on the "Log" sheet, however it is unable to capture the original values when I first open the file and make the changes.

Also, how can I put the value of "Now" in column B?

Code:
Dim PreviousValue
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value PreviousValue Then

[Code].....

View 9 Replies


ADVERTISEMENT

Make Chart That Will Constantly Change As Different Selections Made By Person?

Jun 18, 2014

I have created a spreadsheet which uses data validation to allow a user to select a company, start Date, end date, and projected months in order to output different figures. I have attached a spreadsheet to this post in case that was hard to understand.

Since there is data validation, indexing, and matching going on, as different selections are made, various tables change as a result. What I mean as you open the example sheet and start messing around with the data validation boxes.

The problem I am encountering now, is creating a chart that will also keep changing as the user changes their selections in the data validation boxes. I am unsure if this is even possible, but I figured it was worth a shot to ask. On the example sheet, I have included a graph similar to the one I want, but it only contains data in a certain range of cells, rather than dynamically changing to accommodate whatever data is outputted.

Here is what I am looking for in the graph:

1. Whatever dates are selected by the user in the data validation boxes, along with the projected dates will be on the x-axis.

2. The PMPM figures associated with the selected dates, and the projected dates on the y-axis.

3. Ability to make different selections from the data validation boxes and still have a dynamic chart that keeps updating itself.

View 2 Replies View Related

Write VBA Code That Will Generate Text File With ALL Changes Made To Excel File

Jun 4, 2012

Is it possible to write vba code that will generate a text file with ALL changes that were made to an excel file. Ex. If Cell A17 = "Monday, June 4, 2012" and a user updates Cell A17 to "N/A", I would like to know what the value was before and after the udpate was made.

View 8 Replies View Related

Make Auto Backup File When Opening

Apr 10, 2012

I want to make a macro that will generate a backup copy each time the Excel file is opened. I premade a backup and recorded the following macro, placing it in the sub here:

Code:
Private Sub Workbook_Open()
ChDir "C:Usersmy.selfDesktop"
ActiveWorkbook.SaveAs Filename:= _
"C:Usersmy.selfDesktopDaily Backup.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled,_ CreateBackup:=False
ActiveWorkbook.Close
End Sub

names have been changed to protect the innocent. But now either putting in to repoen the original working excel or manually opening it puts me in an infinite loop of backing up. The probelm seems to be that to make a copy it is making the open workbook the backup. So two part question here:

1. Can I make a copy under the same name automatically when the file is opened while staying in the current one?
Example: open the Excel file "Working Copy", which makes a mirror file called "Backup Copy", but stay in "Working Copy".

2. From what I did manage to do, I notice every time it asks if I want to overwrite. I chose "Yes" when recording the macro but this doesn't seem to get captured. can I make it automatically yes without being asked?

View 3 Replies View Related

Macro To Make Backup For A File At Regular Intervals Without Opening It

Dec 19, 2012

Code to save a file C:DesktopFile.xls to a different path say F:New.xls as a backup.

I want this to happen every 1 hour so that if in case my original sheet File.xls is corrupted or deleted, I have a backup data (New.xls) which was last saved.

And also File.xls need not be necessarily open.

View 1 Replies View Related

Export TXT File Into Specific Pre-made Fields In Excel?

Aug 13, 2014

I work for an environmental company and we do emission testing. We specifically use the analysis software CEMsoft and ProRATA. After each test/run we get a text file (.txt) that we print out which then has to put into a specific pre-made excel data file.

I am wondering if there is a way to export the data from the .txt file and into the specific fields within the excel sheet. Can I encode the .txt or excel file to do it for me? Trying to eliminate the need to manually put in the data from the printed out sheet.

View 1 Replies View Related

Change DisplayCommentIndicator Upon Opening File

Aug 6, 2009

I am trying to turn off DisplayCommentIndicator when anyone opens this one file. I started with this not sure why its not working.

Private Sub Workbook_WindowActivate()
Application.DisplayCommentIndicator = xlNoIndicator
End Sub

also after anyone closes the file turn the comment indicator back on.

View 2 Replies View Related

Opening Excel File From List Of File Names Located In Single Sheet?

May 31, 2013

loop and range function to apply in the below code through which I can avoid writing code for all the rows.

I am trying to open excel files located in single folder from files name (along with the path) in single worksheet (Column B and Row 1 to 500).

I have created follwing code which opens the file and then runs a macro in it.

a Sub Test()
Dim strFName As String
strFName = Sheet1.Range("B2").Value

[Code].....

View 4 Replies View Related

Excel Files Opening As Different/new File

Jan 12, 2010

I'm using Excel 2007. I didn't change a single option but for some reason this problem came up. When I open an already saved Excel file it opens up in a newly created file. For example, if I open a file saved as "Monthly Sales" it will open it up and rename it as "Monthly Sales1." When I go to save it again, which would normally just save it where it currently sits, it basically acts as if I hit Save As and pops up the box asking me where I want to save it. This is extremely annoying as most of the places where these files are saved are several folders deep which makes it time consuming every time I open and alter a file. I can't find any options that looks something like this and it only happens in Excel.

View 9 Replies View Related

Excel 2010 :: Keeps Opening Old Version Of File?

Dec 21, 2012

I have a file that is on a server. When I open it on my excel it opens an old version of it and not the newest copy. If I open it on a coworkers computer, it does open the correct newer version. How do I change this?

View 4 Replies View Related

Excel 2007 :: Add-in Not Loading When Opening File

Jun 10, 2013

I have an excel add-in which is a ribbon which houses a great deal of financial tools that I use on a regular basis so I don't have to hunt around excel for things I use regularly. The add-in has always loaded automatically whenever I opened excel - whether I opened excel by creating a new blank document or by double clicking a previously existing file.

Starting a few days ago though, the add-in only opens when opening excel by creating a new blank file. I can't think of anything that changed recently. Using Excel 2007.

Even when opening a file by double clicking, Excel still says the add-in is active (in excel options).

View 5 Replies View Related

Default Excel File Type When Opening?

Mar 21, 2014

is it possible to change the settings on your computer so that when you open Excel, it opens it as a certain type of file? I like working with .xlsb because I work with large sets of data all of the time and find that they open faster and are faster to work with. Currently every time I open Excel, it opens as .xlsx.

View 1 Replies View Related

Closing And Re-opening The Same File In Excel On-click Method

Oct 15, 2008

I was wondering if there is any code using an on-click method. When clicking a button i need excel to exit the file/sheet without saving (no prompts) and then re-open the exact same file/sheet once it has closed.

In a way this is like a refresh button.

View 14 Replies View Related

Opening File Causes: Excel Has Encountered A Problem And Needs To Close

May 23, 2008

I am trying to open an Excel file that was emailed to me in zipped file that requires macros. When it asks me if I want to enable macros it gives me an error and will not open. Is there a setting I need to change?

View 5 Replies View Related

Opening Excel Files In File Path Which Includes Folders?

Nov 7, 2007

I am trying to open excel files in a file path which includes folders which also have excel folders i wish to open there are quite a few.

At the moment i am working with this code but it fails to open excel files which are within the folders in the specified file path. Its fine for excel workbooks in the folder specified by file path.

This is the code

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook

[Code].....

View 9 Replies View Related

Excel 2007 :: Using Parameters While Opening Workbook To Get File To Autoload

Oct 5, 2012

I need to combine AHK and Excel, and in this I came up with the solution to save my parameters in a file, and let the workbook autoload it when I open the workbook. To do this I need to pass a command line parameter, or working directory to the workbook, as I will be using many different work directories for this to make sense.

This is only a matter of how to execute my workbook so that the path is included (and usable) to the working directory

I have tried

..workbook-name.xlsm /p "workdir"

But it results in a "grey" version of Excel aka. Excel is open, but no workbooks are.

I have also tried

excel.exe workbook-name.xlsm /p "workdir"

But this did not work either, and is not an option as we use both 2007 and 2010 at my workplace where this is intended to be used.

I can do it very easily by using absolute paths in the Excel auto-load, but as I have explained already this is just not an option.

View 7 Replies View Related

Excel 2010 :: How To Make Macro Print In Other File

Jun 26, 2014

Excel 2010.I'm using this code to print user's accessing a file:

[Code] .....

However as someone could just delete the printed user log in the Sheet2, and also you can't use this if you protect the sheet, which defeats the purpose.

I want it to print the log into a separate file. Can I add in the file path/name to this code somewhere?

View 1 Replies View Related

Excel 2010 :: How To Make File Auto Save To PDF

Aug 16, 2013

I have workbook I would like to auto save to PDF copy file in different location every time the original file is save maybe some VBA code

View 4 Replies View Related

Excel 2010 :: How To Make File Auto Save To PDF

Aug 16, 2013

I have workbook I would like to auto save to PDF copy file in different location every time the original file is save maybe some VGA code is this possible ???

View 1 Replies View Related

Make Summary / Index For Different Sheet In Single Excel File?

Apr 11, 2014

How to create a summary/index sheet for multiple sheets in single excel file...

View 1 Replies View Related

Running Macro When Change Made To Worksheet

Feb 16, 2012

The intent of the code below is to run the marco HideCol when a change is made in cell C10. If I actually change the value in C10 it works. However, I have C10 linked to another worksheet (=anotherpage!A13). When the value changes, my macro is not executing.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim changed As Range
Set changed = Range("C10")
If Not Intersect(Target, changed) Is Nothing Then
HideCol
End If
End Sub

View 3 Replies View Related

File Size Made Smaller

Apr 20, 2006

Is there a way of eliminating un-used rows & columns to bring your file size down.

I already know about eliminating unnecessary pages, and I've cleaned up defunct modules, etc., and anything else that's just clutter.

The Row & Column thing would be a big help.

View 7 Replies View Related

Opening A File Without Opening A Workbook

Apr 17, 2007

I have a need to open a file from my companies intranet. My current method was to open said file via the method that the recorder gave me. However, I would like ot be able to open a said file without having to start open another workbook.

This is the path:
[url]

So the command is this:
Workbooks.Open [url]

Links are not actual links

So what I need to know. Is how can I open this file without opening a workbook. I haven't been able to use the VB "Open Statement" to open a file and I don't believe that I've been successfull using the Filesystem object either.

View 9 Replies View Related

Identify The Cell Address Of Last Change Made In Sheet Using VBA?

Aug 13, 2012

I am using an event macro to trigger a set of calculations. In the spreadsheet, users have a choice of 3 input cells to work out an answer; these 3 cells work out the same cost from different angles depending on the variables available to the user.

Users only ever need to fill in one of the three cells to work out the answer as the event macro I designed, should (upon input by the user), work out the remaining two input cells using logical arguments.

The macro and calculation work fine, except in the event of users amending a value in an input cell previously updated; in other words, if all 3 cells contain a value and 1 of them is being changed by a user.

The issue: I need the event macro to recognise the location of the last change made by the user in order to determine which 2 of the 3 cells need to recalculate. However, users will invariably press enter or tab etc... upon making changes and this has caused me issues so far.

What I have tried already:

1) running another event macro "on selection change" to record elsewhere in the model all cell selections as and when the user interacts with the model

EFFECT: it made the "on change" event macro very slow and clunky; so I need to avoid
2) declaring a range as set r = activecell.address

EFFECT: this did not work as the active cell's address would in fact be the address on the last cell; e.g. the one selected further to pressing enter (often the one directly below the cell recently changed)

A solution to add to the existing "on change" event macro that identifies the last edited cell

View 8 Replies View Related

Change Text Colour When Entry Made In Another Cell

Jan 10, 2010

I am using Excel 2003, my problem is that I have cells D4 & E4 with red text, when an entry is made in B4 (say $640, the amount can vary) I want the text in D4 & E4 to turn black. I have attached a section of the document in question.

View 5 Replies View Related

How To Review Who Has Opened A File With Password And Made Changes

Apr 17, 2013

I have a master file which users are able to open via read only or go in via password to edit.

I'm not too bothered on the read only users more on person's entering the file via password, making any changes and then saving those changes.

Is there a way to trace what changes they made and would their name be saved as the new "author" of the file?

View 4 Replies View Related

Opening The Workbooks In A Folder, One By One, To Make Changes

May 11, 2007

I'd like to create a macro which will

-go to a given folder

-open each of the workbooks in the folder, one at a time

-after opening the first wkbk, go to a specific cell address on the first tab of the wkbk

-paste in some values and formulas

-save the workbook

-open the next workbook, and repeat as above, for all workbooks in the folder

View 9 Replies View Related

Make Workbook Read Only Upon Opening

Dec 12, 2007

way of changing the mode of a workbook to readonly upon opening it.

View 6 Replies View Related

Log UserID Of Person Who Last Made Update In External File Links

Oct 2, 2006

Is there a way to log the userid of the person who last made an update in the external file links?

View 4 Replies View Related

Texts Automatically Change The Currency Depending On The Choice Made In The Previous Userform

Jul 3, 2008

I have options buttons in a userform, first is "$" the other is ""

When somebody chooses one of them, it writes the choice to a cell in the data sheet. But when clicked on the next userform I want some of the texts automatically change the currency depending on the choice made in the previous userform.

The formula below was is example

=""&Data!$A$2&" / m"

=""&Data!$A$2&" / kWh"

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved