I have a macro to run which will clear out all rows and move them corresponding sheets on the same workbook. At the minute i have this macro to run at 21.00 hours everyday. Is it possible to fire this macro to auto run before me closing a workbook so i don;t need to wait and also worksheet is saved with the latest changes
View 3 RepliesI am trying to make a save&close workbook macro.
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
I have a spreadsheet and I would like for it to automatically close after printing.
Long version: The sheet is set up with several fields that can be changed by the user, and the date/time are automatically entered upon opening the sheet. I want to ensure that all fields are reset, and the date/time are correctly updated, every time that the user uses the spreadsheet. The last thing the user will do when finished entering his information will be to print the form, so I figure this would be a good time to make the form close (if this is possible).
I have already entered a code to prevent the query "do you want to save changes" before closing. So when they open the spreadsheet it will always contain the same starting information.
Is there anyway that a workbook can be set up to close itself at a set time i.e 19.00hrs every day?
Any way to auto-close the excel application after finishing my macro run?
I tried this code,but it doesn't work... i added a sub and ran it like this:
Sub CloseandSave()
ActiveWorkbook.Close SaveChanges:=True
End Sub
to be more specific,there is a macro running,closes all open workbooks,but excel itself stays open...
At the time of opening the file some macros are running automatically in my file as per my requirement. If auto filter is on then it disturbs my program. Sometimes I forget to remove auto filter before closing the file and it cause a problem in my file after opening it again.
Requirement is: Is there any code to prevent the file closing if the auto filter is on and give me a warning message 'Please remove auto filter first'.
As an example, I have this code to open a message box saying "Hello" when Excel opens.
View 4 Replies View RelatedI have a 'few' workbooks that I have added some code to which auto closes the workbook if it has been inactive for more than 60 minutes.
Problem seems to be is that this doesn't envoke if a screen saver is active, does anyone have any VB code to disable the screen saver until after the workbook has closed.
I have a menu workbook with macro buttons that call different workbooks. Call the menu workbook (switchgear.xlsm). Switchgear contains 2 buttons. Button 1 closes switchgear and opens Book1. Button 2 closes switchgear and opens book 2.
If book1 is open and nothing has changed for 3 minutes I would like it to close and open switchgear.xlsm. I do not want it to just close in 3 minutes... I want it to close after 3 minutes of inactivity.
I have search all over and tried and tried to achieve this without success...
I have been able to get the timer to work but it gets caught in a loop. If I open book1 and close it in less than a min then 3 minutes later out of nowhere the code tries to run???
Secondly when this happens I see several book1 vba projects loaded in the editor window???
I have an excel file stored on a network drive for the purpose of information sharing. (File protected with a password)
But some the guys leave the file open for quiet long time and hence I cannot open the file for updating the data.
-I need to have a macro that runs every 5 minutes and displays an alert message saying "Please close the File" as long as the file is kept open.
-A second macro with a modified version of the above to close the file automatically after 5 minutes from file opening time after showing an alert message "You cannot leave the File Open, File is Closed Automatically!"
I'm working on a time sheet, and to make it easier to enter time i created a macro that sets the auto correct to correct a "." to a ":", but when the workbook closes i want to run a macro that deletes the auto correct rule.
I have the following code that displays a form at a user defined time and if the user does not press "Stop" then the workbook saves and closes. The user can press stop then the workbook remains open.
Here is what I have where:
Admin_Auto_Shutdown = Yes or No
Admin_Auto_Shutdown_Time = 3:34pm or user defined time (This doesn't seem to work??)
'Auto Shutdown CloseandSave
If UCase(wb.Worksheets("Admin"). Range("Admin_Auto_Shutdown").Value) = "YES" Then
Application .OnTime TimeValue("Admin_Auto_Shutdown_Time"), "AutoShutdown"
End If
Sub AutoShutdown()
Application.OnTime TimeValue("Admin_Auto_Shutdown_Time"), "AutoShutdown"
Auto_Shutdown_Form.Show
End Sub
Now, my question is about a timer that I can show on a form. When the form is displayed I would like to give the user 30 seconds to press stop (and keep the workbook open) or to press proceed and save and close or to not do anything and the workbook would close and save when the timer reaches zero.
Code for user form which is missing most everything...
Private Sub Halt_Click()
'If user whats to continue without closing
Auto_Shutdown_Form.Hide
End Sub
Private Sub Proceed_Click()
'If user whats to save and close
Auto_Shutdown_Form.Hide
How do I add a timer to this code where it will run this at the end of the timer?
Auto_Shutdown_Form.Hide
Application.DisplayAlerts = False
With ThisWorkbook
.Saved = True
.Close
End With
I have a problem on auto-sorting my data after clicking the "x" or "Close" button on DataForm. Im creating a command button that trigger a macro to open an autofill DataForm. I want a code that everytime i ADD or EDIT using the DataForm, it will automatically sort the Column A after clicking the "x" or "Close" button. Below is the code of my macro:
Code:
Sub OpenForm()
Sheets("DATA").Select
ActiveSheet.ShowDataForm
End Sub
I need to know the command to close a workbook. I have used the information found here: http://www.ozgrid.com/VBA/userform-close.htm to disable closing the workbook via the upper right "X", but I need a button that, when clicked, simply closes the workbook instead.
View 2 Replies View RelatedI have a workbook, "A", that while open I need to be able to detect any type of activity within other open Excel workbooks "B...Z" that are simultaneously open.
I understand that I would need to use the Class Module with a Public WithEvents function but I am stuck here.
I am using Excel 2003.
It’s been working perfectly up until around an hour ago. However, now when I close Excel, I get compile errors.
These compile errors seem to be because Excel is trying to access controls in the workbook after it has closed. Since the workbook is closed, VBA can no longer ‘see’ the controls, and therefore it thows up errors.
I’m also getting a similar error on a Worksheets("DataExplorer").unprotect line, which seems to be because the worksheet isn’t there after closing.
These errors only occur when I close the whole excel application using the big cross in the top right. If I just close the workbook (using the smaller cross just below the big cross in the top right of excel), it doesn’t throw up these errors.
Just to clarify: all of the code runs perfectly when Excel is open. The errors are being thrown up for lines of code which run without problem until Excel is closed.
I have the following Macro setup and I would like the macro to run once the Save button is clicked.
Sub LetterCol()
Columns("C:C").Select
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("D:D").EntireColumn.AutoFit
Range("A1").Select
Application.CutCopyMode = False
Range("D1").Select
ActiveCell.FormulaR1C1 = "Letter Column"
Range("A1").Select
End Sub
I have a workbook that contains 4 macros. This workbook is copied by vb.net application with another name. When I run the macro in the copied worksheet, the Original file/workbook is opened automatically, and it is open until the copied workbook is closed.
My question:
1) Is this normal, when a copied workbook invokes a macro from the original workbook, the original workbook opens?
2) Is it possible to close the original workbook while executing the macro from the copied workbook?
3) Will that affect the macro in the copied workbook?
4) If possible to close the original workbook, kindly help me with the code.
I'm trying to run what is a pretty basic macro when a file is closed
Sub Auto_Close()
FileCopy "C:Documents and SettingsdaveDesktopWorking olderPlanner.xls", "S:LeavePlanner.xls"
End Sub
I've checked that the Auto_Close feature is what I need, but it just doesn't seem to be running
I've tried it from clicking the x, and also File>Close, but with the same lack of success
I have the following macro to recalculate the time every second on opening a spreadsheet
Sub Auto_Open()
Calculate
Application.OnTime Now + TimeValue("00:00:01"), "Auto_open"
End Sub
Looking for the code I need to add that will stop the macro when the file is closed.
When I open my workbook it also opens a separate workbook and hides second book.
when I close myworkbook I want to unhide the hidden one and close it without saving.
How can i exit or close the macro after the condition,
View 3 Replies View RelatedUsing excel 2003, I have a ss that uses filters and sorting. I can get to the data I want but must reopen the workbook if I want to start over and extract different information. I tried installing this VBA:
Sub CloseMe()
Application.OnTime Now + TimeValue("00:00:10"), "OpenMe"
ThisWorkbook.Close False
End Sub
Sub OpenMe()
MsgBox "I'm Back!"
End Sub
This is my first time at inserting VBA. Usually I just use keystroke macros. I have set my Excel VB Book options to: Option Explicit double clicked the sheet I was using and under general inserted the code, closed Ctrl+Q, saved workbook. When I reopened and tried to use it all I get is “I'm Back!” nothing else happens. One time it actually ran and reopened but it appeared that the workbook was saved before closing because it was still sorted. What I need is it to completely unsorted as it is when I first open it so I can start over.
I have a macro already made to protect all the worksheets upon leaving. One thing I have noticed as I go into these workbooks is it opens on whatever page you left or saved on. I would like it to open up to the first sheet, which is my master.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "Delta Crew"
ws.Protect "delta"
[Code] .........
Current Workbook: Sheet 1 is a report and the rest of the "Sheets" are data tables. I currently manually identify completion in the "Data Table" Sheets in column U by stating "Yes" or "No" (Row 1 in all data tables is just a Heading Row no data). I go into each row of the data sheet initially say "No" in column U and color the cell with a "Yellow" fill to make it easy to identify. When the item is finally complete I go into the data tables add a completion date in column T, change column U from "No" to "Yes", and fill that column U cell from Yellow to White.
I am looking for a continuous macro that works for all sheets except "Sheet 1" and when there is no completion date in column T then column U is "No" and color filled Yellow but when a completion date is added in column T then column U changes to "Yes" and fills White.
Some dependents that I feel might affect the code would be that Row 1 of each data sheet is headings so that row will not need to be affected by the code and that the code will be only used if there is a tracking number in Column A otherwise the entirety of each sheet would populate like that because initially their are no completion dates in column U. Also dates are in this format in column U "MM/DD/YY"
I have the following strange problem. A simple macro 'ActiveWorkbook.Close' causes excel to crash if I use it with a button on my sheet (a form). When I run the macro via developer > macros > run
the macro runs as expected. Even if a run the macro with alerts (thanks to jproffer) the problem persist.
i m designing a test for a school and want to add a macro that will be assigned to a button at the end of the test i want the macro to close down the the excel program running and not prompt the user to save the workbook is there a vb code that can do this i have tryed a few but they all come back object not found.
View 6 Replies View RelatedI have a folder which has a set of files (Files may be anything like excel,pdf,word..etc).My rrequirement is: When i run the macro the first file to last file should open and wait for 5 sec and then close..I mean first time the first file should open and wait for 5 sec and close..Next 2 nd files shoul open and close after 5 sec..next third files should open and close after 5 sec...Like wise till last file..Is this possible to achive through macro code..
View 7 Replies View RelatedI have created several excel files as I cannot open them all at once since they are too large. So I have to open one excel file whilst closing another.
part1
part2
part3
part4
part5
I want a macro that opens the excel file part 1 and part 2. Then it should close part 1 and after closing part 1 it should open part 3. Once part 3 is open, part 2 should be closed and when part 2 is closed part 4 should open. When part 4 is open, then part 3 should be closed and part 5 should open.
I need to have a workbook (all of them in a folder, ideally) refresh in the middle of the night. The file is large so the refresh takes a full minute. I know I can't auto refresh a closed workbook (pretty sure anyway), and I can't leave the workbook open for various reasons.
So I'm thinking I could have a macro in an open excel (it kicks off on open) that will open a particular workbook elsewhere (the one I need refreshed), open it, refresh it and close/save it. Ideally it would do this for the entire group of workbooks in the folder but I'd be happy to start with just one. I would like to set it to occur every twelve hours (so at midnight, say, this would occur - I would just have to leave the workbook with the macro in it open on a computer so that it runs and completes this task every night).