I am looking for a macro that will run on worksheet activation. it needs to run only if the file had been saved from the previous week. would something like this work?
Private Sub Worksheet_Activate()
If application.worksheet.lastsaved = msolastweek Then
range("RANGE").clearcontents
End If
End Sub
Is there any way of creating a log that logs the last user that saved the workbook including the date and time. I would like the log to be a sheet hidden in the workbook. Not many people will be saving the workbook.
View 9 Replies View RelatedI have written a few macros, which use date type variables. Because VBA uses the 1900 date system, and some workbooks use the 1904 date system, I have to first check and see if a workbook uses the 1904 date system, and if so, subtract off 1462 days where appropriate. My macros used to check the date system by using the command:
If ( ActiveWorkbook.Date1904 = True) Then
nh_stringToDate = nh_stringToDate - 1462
End If
But, sometimes the macro resides in one workbook (call it macro.xls) but the currently active workbook is some other workbook (something.xls). What I really care about is the date system of macro.xls and NOT something.xls. So, instead of "ActiveWorkbook" is there some other object I can use to refer to the workbook in which the macro itself resides? I don't want to rely on using the name of the macro workbook (macro.xls) because this could change! So I need a way to simply refer to the workbook in which the macro resides.
Need VBA code to save column J of open workbook called LmbcAcctsPayable.xls, Sheet1, to same workbook & sheet called LmbcAcctsPayable.xls, Sheet1, when save is not selected.
Reason: The workbook is usually not saved because it is more of a templet with names in column A. Column J is updated with a date when a row is used so I need the column J to copy automatically to the blank templet when exiting without saving (using ThisWorkbook.Save = True in Auto_Close). That way, rows not being used for a long period of time can be periodically eleminated.
I would like to execute a sub when the workbook is saved, what is the procedure for this.
View 3 Replies View RelatedI am busy with code that filters and copies from a "source file" using dialogue to choose criteria than adds a workbook and pastes filtered information. I than go back to "source file" and do second filter. My question is this newly added workbook changed from book1 to book2 etc everytime the user will run the macro. How do I go about activating the workbook for second and third paste without it being saved?
Here is name code:
[B]Workbooks.Open ("C:Documents and Settingsabek276DesktopSource File.xlsx")
Options = InputBox(Prompt:="Scheme Code", Title:="Options")
Options1 = InputBox(Prompt:="Scheme Code", Title:="Options")
Options2 = InputBox(Prompt:="Scheme Code", Title:="Options")
[Code].....
I want the excel spreadsheet to "BE Protected" in standard form, and only be "Unprotected" when macros are enabled -- BUT DONT JUMP TO CONCLUSIONS, hear me out, it gets deeper than that
If the user does not enable macros, I want the book to be protected. If they do enable macros, it becomes unprotected.... (On load is good enough, no need to be real-time)..... But, they can save the book, so it would no longer be protected , which is where my question comes in ...
I could use the -unprotect "password"- on load when macros are enabled but this will only work if the spreadsheet was originally protected and is never saved again. This is a problem because this workbook will be given to others and will be saved time and time again.
So, I had a vision :-) .... function 'before save' , protect the sheet using -protect "password"- , allow the sheet to save, and then unprotect back using 'unprotect "password"' so you can continue using - BUT, the problem with this is now I have created an endless loop in theory, because after it unprotects it would want to re-save again because it changed
I have a workbook that I constantly am improving with new macros and VB routines. I sometimes have two versions available for use at the same time. I would like to create a macro or routine that will tell me if the workbook has been saved. A simple cell color change to indicate I've saved would be fine. Also, if I DO save the file and the cell color changes to indicate I have saved, I would like the cell color to change back if any change is made to the workbook. This way I will always know if I need to save or not to keep latest changes.
View 3 Replies View RelatedI currently have the code below that closes all but 1 sheet in my workbook called Main
Is there anyway i can clear a range of cells on a couple of sheets within the workbook when save is pressed?
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For a = 1 To Sheets.Count
If Sheets(a).Name "Main" Then Sheets(a).Visible = 2
Next a
End Sub
I've designed a performance indicator (PI) recorder for up to 12 users, it has a userform allowing the users to input their data and a button to save the data and unload the user form. the data is saved using the following
Dim NextRow As Integer
On Error Resume Next 'skips line if workbook is not open
ActiveWorkbook. Sheets("DATA").Activate 'sets the "data" worksheets as active
NextRow = Worksheets("DATA"). Range("A" & Rows.Count).End(xlUp).Row + 1 ' finds the next empty row
With Worksheets("DATA").Range("A" & NextRow) 'inserts the data from the user form
there follows a series of offsets to slot the data into the correct column,
then this code to save the workbook
ActiveWorkbook.Sheets("reports").Activate
Application.DisplayAlerts = False ' suppress overwrite warning message
ActiveWorkbook.SaveAs "MY PATH"
CreateBackup=False
Application.DisplayAlerts = True
MsgBox "Spread sheet Saved"
The problem I'm having is that when the user then closes the excel application there is a conflict between the users 'saves' i.e. one ultimately will overwrite the other. is there a way round this ?? (this is in EXCEL 2000)
I have working code to unhide specific worksheets from a large number of hidden worksheets and would like to be able to delete all the hidden sheets when the workbook is saved or maybe have a button to delete all the hidden (un-needed) worksheets beside the selection drop-down menu, so that only the selected sheets are saved.
View 4 Replies View RelatedI have a macro in a workbook template,that when the workbook is open it populates a cell with the date and time such as 111711.507. This becomes the contract number for this workbook.. They complete the form or workbook and save it. The next time they open the saved workbook the macro runs and changes the contract number to a new number. I want to disable the macro on the saved workbook from changing the number.
View 2 Replies View RelatedI have one workbook named "Data Master" with numerous worksheets... all sheets are named with the following 6 digit format, eg. 005001, 005024, 005133, 005456, 005763, etc.
I have another directory with files each beginning with the particular 6 digit cost centre number.
I was hoping to have a macro that will... for each worksheet in "Data Master" check a specified directory containing the cost centre files, for the file beginning with the relevant worksheet's 6 digit cost centre number... and simply paste a copy of the worksheet in "Data Master" to the end of the existing sheets in the cost centre file.
I have created a template with a form. Now I'm using (the code is in ThisWorkbook)
Sub Workbook_Open()
frmMyForm.show
End Sub
This runs the form also when opening the template or a saved workbook based on the template. I want the form to run only when creating a new workbook based on the template. How do I do?
I have an excel file in which the date format for a column was in the following format -: 3/14/01 Since I wanted the column to display in 'mm/dd/yyyy' format, I changed the type to -: 03/14/01 and the sheet was saved. I then for a specific purpose converted the xls file to csv and noticed that the date formatting I did is not getting saved. That is, every time I open the csv the date format changes back to 3/14/01.
how to retain the date format in excel? I need to use the date format in mm/dd/yyyy only for the application I'm using.
does anyone have a simnple code that when entered in a cell only returns the last date the file was saved? better yet, could it somehow be specific based on a modification to a specific worksheet modified?
example: i have 8 worksheets. how can i show when one worksheet is updated, say November 27th 2006, and another worksheet on November 28th 2006?
Is there a formula that will get the date & time the workbook was last saved? I am using an earlier posted macro now but would like for the information to be visible constantly in a certain cell. Possible?
Macro used:
Sub WhenSaved()
MsgBox "This file was last saved on " & Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), "mmmm d, yyyy") & ".", 64, "Date of most recent save:"
End Sub
When I open any saved Excel file the saved file opens along with a blank workbook titled "Book 1". How can I make it so when I open any saved Excel file it only opens the file I want and not a new workbook every time?
View 2 Replies View RelatedI'm looking for a way show the username of the person who last saved the spreadsheet, and the date & time it was saved. I would like to be able to ouput this data in certain cells on the worksheet. I have tried using something like:
View 2 Replies View Relatedhow I can have the latest date a file was saved showing on the worksheet? For example, I open a worksheet that was last saved on Sept 22nd, and after updating it today, I save it and want the date to show today's date?
I'm pretty sure I've seen it in some files in my working life, so would appreciate advice on this!
if there is an easy way of showing the active file "Last Saved Date" on a Userform and have it show automatically when the Userform is opened? The Scenario is:
I have a "Main" userform screen and I want to have a text line which says, "File last updated on xx/yy/zzzz ". This must show up automatically when the "main" userform is opened.
I'm looking for a code which is checking the file name first before opening and operating on it. File name format is filenameyyyymmdd.xls but the problem is files in that directory are saved irregularly (couple times a week) and I need to open the newest saved file.
View 2 Replies View RelatedI have a macro that returns the time the sheet was last saved:
Function LSDate()
LSDate = Application.Caller.Parent.Parent. _
BuiltinDocumentProperties("Last Save Time").Value
End Function
Unfortuantely, the cell doesn't update without recalculating the cell. Is there a way to get the cell to update automatically every time the sheet is saved?
I am about to write a macro and I want it to identify, from the C: drive, any .csv files that were created / saved today. I have managed a pure list before and I have managed to distinguish by filename, but selection by date would be exctremely useful.
View 2 Replies View RelatedI have a workbook with multiple worksheets. First 4 tabs are the standard tabs and rest of the tabs are created based ona macro with the unique names. Now i want to create the separate workbook for each tab by its name and , date and time stamp in a C directory.
View 5 Replies View RelatedIf I have a multiple workbooks open and I want to determine if a workbook is open for example "Test 1.xls", how would I do that. I want it to msgbox once to say "open" if open or msgbox "not open" if not open.
View 2 Replies View RelatedI cannot cut and paste from my working computer to the internet access computer.
This code is intended to check for a workbook already open and if it contains a particular worksheet. This function is called from a routine that reads a master workbook then opens a PCM workbook. The PCM workbook might already be open.
WB_PCM is declared as a workbook and the name is set. WS_PARAMETERS is declared as a string and its name is set to an existing worksheet. The workbook and worksheet do exist, but are not open.
This is the call to the function:
Code:
WS_exists = Is_Workbook_Open( WB_PCM, WS_PARAMETERS_NAME )
And that code is
Code:
Public Function Is_Workbook_Open( workbook_ref As Workbook, sheet_name As String) as Boolean
Dim check_sheet as Worksheet
On Error Resume Next
If( workbook_ref Is Nothing ) Then
Is_Workbook_Open = False
Else
Set check_sheet = workbook_ref.Worksheets( sheet_name ) ' < error
[code].....
During this time the master workbook is open but either not in focus or is minimized within the Excel windows. If I select that worksheet and select a cell in it, then this code runs. What should I do different to get this code to check for WB_PCM being open and being able to handle it when either open or not open.
Is it possible to write code that can identify how a workbook has been opened i.e. has the workbook been opened by another workbook or has it received a double click event on the workbook itself.
If it is possible I would like to place the code in the open event of the workbook that is being opened.
Trying to determine who has a file open, the code below works for simple files. However if the workbook contains macros and user forms it seems there are multiple occurances of the search terms strFlag1, strFlag2. How can I determine the last user
Function LastUser(path As String)
Dim text As String
Dim strFlag1 As String, strflag2 As String
Dim i As Integer, j As Integer
strFlag1 = Chr(0) & Chr(0)
strflag2 = Chr(32) & Chr(32)
Open path For Binary As #1
text = Space(LOF(1))
Get 1, , text
Close #1
j = InStr(1, text, strflag2)
i = InStrRev(text, strFlag1, j) + Len(strFlag1)
LastUser = Mid(text, i, j - i)
End Function
Above code taken from Determine Last User
I was wondering how i can code a if then loop to determine if a workbook by the name i specify is opened.
If Windows(vCriteria & "ISPR.XLS"). = True Then
MsgBox "true", vbCritical, "Ferguson Enterprises, Inc."
Else
MsgBox "false", vbCritical, "Ferguson Enterprises, Inc."
End If
thats what i got.... not sure if that will work... i'm missing the definition for: If Windows(vCriteria & "ISPR.XLS"). i keep wanting to type isopened, but alas it is not avaliable in the list. There seems to be no extention avaliable that appears to work with such a function. Is it possible to do what i am trying to do?
