I read two pages on "Workbook_BeforeClose" trying to find a relative answer. I want to able to close a workbook with out saving and not rely on the user pressing the correct button, can this be done?
View 3 Repliesi 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 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 a macro that opens up a file, copies info from the file then closes it and the macro continues. I don't want the user to have to click on any 'do you want to save changes' prompts.
View 5 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 userform1 with 12 text boxes with values/text and on this userform there is a cmdbutton to bring up userform2 to change the values/text of the textboxes on the userform1, it changes all the values/text on all the textboxes on userform1, however I want it to save these changes when the 1st userform closes & then re-opens?? How do I do this??
I have completed a long macro, and at the end of the macro I want to close a few workbooks automatically without saving. These workbooks have been assigned variable names.
Is there a command line I can use, that will not prompt the user to save?
Variables:
MARM_fileNM
MARC_fileNM
MAKT_fileNM
Temp_fileNM
I am new to EXCEL and wanted to know if you can have excel startup running a macro and also if you can have a macro close a worksheet without saving and without confirmation. I created the basic macro of what I want to do. I just want it to be able to run unattended. Is this possible with EXCEL?
View 9 Replies View RelatedHow can I put a VBA script to wait for 30 seconds, then close the Excel file (w/o saving it) which its name is stored in CSV_file_name variable, then re-open it?
I've the following code in my add-in. Strangely the code in the Workbook_Open event executes as expected, but not the Workbook_beforeClose event. May I know the reason?
*********************************************
Private Sub Workbook_Open()
msgbox "hi!"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
msgbox "bye!"
End Sub
I've been trying to figure this out all morning, and it's giving me a headache. I'm trying to write some code in the WorkBook_BeforeClose module as shown below:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans As Integer
ans = MsgBox(prompt:="Do you want to save the changes to " & ThisWorkbook. Name, _
Buttons:=vbInformation + vbYesNoCancel, Title:="X2O")
Select Case ans
Case Is = vbCancel
Cancel = True
Exit Sub
Case Is = vbYes...............
As you can see, I show a message asking the user if they want to save the workbook or not. Clicking the Cancel and the Yes buttons work fine. However, the No button causes the message to be shown again. If I then click No the second time, the workbook closes!
I have data that daily needs to be refreshed and printed to pdf.
I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.
I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).
I have a Macro that is stored in a specific workbook,"Projection Modeler.xls" the last line performed in this macro is the close workbooks without saving chages(this needs to be done due to the way this macro was written, and it would take too long to rewrite). If I would then like to reopen this workbook, and then rerun this macro, can I do this from inside the current macro (in a new sub routine, for instance), or do I have to write a macro outside of the workbook and then open the workbook and call the routine?
View 4 Replies View RelatedTrying to create a new workbook from another open workbook, then copying all the sheets that aren't called "Summary" to that new open workbook and then saving it. I get a subscript error on this line:
[Code]....
I cannot make this work in XL2010.
I am opening a second workbook in ReadOnly in the Workbook Open and Closing it in the Workbook BeforeClose.
I don't EVER want to save changes on either workbook.
I need to leave excel open as the macro created a 3rd workbook which the user will need.
[Code]....
I have a very brief question
dlgAnswer = Application.Dialogs(xlDialogOpen).Show
Selection.Copy
Windows("Derivative YK pricing Mod G.xls").Activate
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("C5").Select
Application.CutCopyMode = False
Windows("EXPORT1.xls").Activate
ActiveWindow.Close
As you can see, I open a workbook with the dlgAnswer, I was wondering how I could close that opened workbook
Currently I have
Windows("EXPORT1.xls").Activate
ActiveWindow.Close
however the workbook will not always be called export1.xls, I want it to close whichever one I opened earlier.
I'm trying to use Workbook_BeforeClose event to delete my custom menus.
However, when I try to pass the custom menu caption to the Sub, I get a compile error (Expected Function or Variable).
I believe this has something to do with global variables declaration or something...
i have a workbook blank template for my colleagues to fill in, they fill in this blank template and save it under the serial number of the item they are creating. However a lot of errors exist when they input the serial number inside the workbook where the serial number is a duplicate of a former serial number.
Anyhow i was wondering is there any way when they input the serial number into the worksheet that i could get it to save in a separate workbook and then have conditional formatting to check those serial numbers in the workbook to see if they match?
I snagged this code out from another project I wrote, and it works fine in that one, but doesn't in the new project.
What I want, is when the person presses the exit button, it will save whatever was done, its running as a form printer and database. The code below is the code for my exit button.
Private Sub CommandButton4_Click()
Dim sAnswer As String
Dim i As Integer
' ****************
' ** Exit Button**
' ****************
When it gets to "If ActiveWorkbook.Saved = False Then" it does put the question out "Do you want to save changes" I click Yes and it doesn't save it.. I have tried both activeworkbook and thisworkbook ... I copied the code and verified all is good to go..
I will shortly be running a project over a twenty four hour period, where a group of people will be inputting data into a workbook (one worksheet per hour), and what I would like to know is if it is possible, to input some coding into a thisWorkbook module to action a save on the workbook every time an entry is placed in column A (of each worksheet) which is divisable by 10 (a10, a20, a30 etc).
View 2 Replies View RelatedI have an Excel doc with ~20 tabs. Each type represents 1 type of activities (soccer, tennis, etc). The size of the file (with the 20 tabs) is pretty significant (>1mb), which jams our email traffic.
Is it possible to:
- keep this workbook with the 20 tabs when open by the user;
- let the user select and fill in whatever tab he needs (just one at a time);
- have a button for the user to click that will save only this tab with a different filename (therefore, when sent by email, the file should be pretty small).
How can i save and close one workbook in excel with vba code.
View 2 Replies View RelatedHow can I make one workbook close another without saving?
These all work in the immediate window, but not in the
Private Sub Workbook_BeforeClose
I have the below code in a macro attached to a button. Basically, if i choose yes to the message box at the beginning then the macro copies the all the cells on the sheet 'selections', opens up a new workbook, pastes into here, deletes some unused cells, rows and columns, then sends the sheet as an attachment on an e-mail.
What i also need to do is when i have sent the e-mail, the macro closes the new workbook without saving changes.
I know this is the code to close a workbook
View 5 Replies View RelatedI 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 somehow need to add to the section of code below a event to have the "(Public)Archive.xls" to automatically close 15 minutes without saving everytime it is opened.
Code:
Kill "H:AllMaterial Prep Archive(Public)Archive.xls"
Columns("A:H").Select
Selection.Copy
[Code].....
I have a sheet that on open looks at the username and determines which tabs can be seen by that user. If macro's are not enabled, I want the sheet to just display sheet 4.
here is the code I have on open that works fine: -
Private Sub Workbook_Open()
If Environ("username") = "Bob" Then
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = False
Else
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = True
End If
End Sub
I have a Workbook_BeforeClose() function that does not, and I am stuck as to why!
Here it is: -
Private Sub Workbook_BeforeClose()
If Environ("username") = "Bob" Then
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = True
ThisWorkbook.Save
Else
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = True
End If
End Sub
So basically after "Bob" is done it will save the sheet with only tab 4 visable. This means that if someone without Macros enabled opens the sheet they can only view tab 4 (I know it isn't password protected in this example, but it will be)
Is there anyway that a workbook can be set up to close itself at a set time i.e 19.00hrs every day?
is it possible to have a messagebox activate when closing a workbook.
View 9 Replies View Related