Workbook Before Close Event
Jul 22, 2008
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)
View 9 Replies
ADVERTISEMENT
Jun 6, 2006
I'm trying to combine 2 sets of code that I have searched other threads for.Both use the OnTime code to trigger events after 5 and 10sec.
My problem is that the workbook won't close correctly and keeps re-opening on the refresh event. I understand both events need to be passed to the dTime variable, but I am obviously doing something wrong. I will include the code as I'm sure its something obvious;
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime dTime, "Sort", , False
If Cancel = False Then Application.OnTime dTime, "RefreshIt", , False
On Error Goto 0
End Sub
Private Sub Workbook_Open()
Run "RefreshIt"
Application.OnTime Now + TimeValue("00:10:00"), "Sort"
End Sub
Public dTime As Date
Sub Sort()
dTime = Time + TimeValue("00:00:10")
Application.OnTime dTime, "Sort"
Range("A6:M10").Select
Selection.Sort Key1:=Range("I6"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B2").Select
End Sub
Sub RefreshIt()
Sheets("Text").Range("A7:F38").QueryTable.Refresh
dTime = Time + TimeValue("00:00:05")
Application.OnTime dTime, "RefreshIt"
End Sub
Any help would be appreciated!
Thanks
View 6 Replies
View Related
Mar 22, 2012
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.
View 9 Replies
View Related
Apr 7, 2009
I have a MS Access db in which I have two delete queries called 'qrySessionsCompleted1' and 'qrySessionsCompleted2'. What VBA code can I use in the BeforeClose event to run these?
View 9 Replies
View Related
Jul 19, 2014
I am trying to make Excel update a cell (C27) in my workbook's front page before closing, but it doesn't run at all.
Debug doesn't show any error...
Actually I am not sure if it's a code error or just the place i put this sub in.
......
Next
MsgBox "All Sheets have been updated"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Front Page").Activate
If .Offset(27, 3).Value < DateValue(Date) Then
.Offset(27, 3).Value = DateValue(Date)
End Sub
View 6 Replies
View Related
Oct 28, 2006
I'm using the following code in "ThisWorkbook" of a couple of different workbooks, and the problem occurs consistently with the code use regardless of which workbook...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Close False
End Sub
The code itself does exactly as it should (not save changes when closing), but shortly after the workbook has closed, any other open workbooks will freeze, then give a popup message saying an error has occurred and a log is being generated, and EXCEL shuts down (loosing any other work)...
I have searched my HDD and only found 4 .log files that reference dates that coincide with the errors, although the times don't match(?)...
Originally Posted by WinMgmt.log
(Fri Oct 27 08:12:29 2006) : core is being shut down by WinMgmt.exe, it returned 0x0
Originally Posted by wmiadap.log
(Fri Oct 27 08:17:54 2006) : Performance library winspool.drv will be ignored as it was previously disabled (WbemAdapStatus = -1).
Originally Posted by wbemcore.log
(Fri Oct 27 08:47:10 2006) : Core physically unloaded!
Originally Posted by gotomon.log
Log file started at Fri, 27 Oct 2006 08:17:46 Eastern Daylight Time
0:00:00 i: G2EnumPortsCore() -- start -- level=1 pPorts=0x0
0:00:00 i: G2EnumPortsCore() buffer available:0 buffer needed:24
0:00:00 i: G2EnumPortsCore() -- leaving early --
0:00:00 i: G2EnumPortsCore() -- start -- level=1 pPorts=0xc55980
0:00:00 i: G2EnumPortsCore() buffer available:24 buffer needed:24
0:00:00 i: G2EnumPortsCore() -- leave --
I had been using;
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Save True
End Sub
Which had the same error... when I changed it to Close, I thought it had been fixed, but it was just that the delay is longer before the freeze.
View 4 Replies
View Related
Jan 16, 2007
I need to use the BeforeClose event to save the workbook with the same name when a user closes it. (82 users and they all seem to want to give it their own name.) I have the following code in the BeforeClose event but would like to eliminate the prompt "this file already exists, do you want to replace it" I have tried using the Application.DisplayAlerts = False but this seems to stop the workbook from saving.
I have a public demention called wbName and is set to the workbook name in an outo open macro
Public wbName
Public Sub auto_open()
wbName = ActiveWorkbook.Name
UserForm2.Show
MsgBox "PLEASE do Not insert rows/columns or enter calculations" _
& Chr(10) & " " _
& Chr(10) & "Enter Only Account Name, Date, and Corresponding_ Calls/Details", vbCritical, "Caution"
End Sub
View 6 Replies
View Related
Jan 5, 2014
I have a command button code on a sheet that checks if all the cells in a table have been completed before saving the worksheet. If they're not all complete a userform message box pops up with a reminder then returns to the sheet without saving. I want to be able to call this from the 'BeforeClose' event as well however, even though it still does what it's supposed to do, after the userform message box has displayed and been unloaded then it still pops up the Save Dialog box.
I'm struggling to suppress the Save Dialog box and return to the sheet.
View 1 Replies
View Related
Jun 6, 2008
I am trying to ask to the user to check if they logged out when they close the workbook but my code is not working...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("OD&D Log-in").Select
If Range("H5") = "reconcile" Then
a = MsgBox("Do you want to Log-Out?", _
vbYesNo)
If a = vbNo Then Cancel = True
If a = vbYes Then
Sheets("OD&D Log-in").Select
Else
Workbooks("Daily OSD Log (ver5).xls").Close SaveChanges = True
End Sub
View 9 Replies
View Related
May 30, 2009
I want a list to be sorted when a user closes the Excel DataForm. I have the code to execute the sort but how do I attach it to the DataForm Close button?
View 9 Replies
View Related
May 1, 2008
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.
View 13 Replies
View Related
Jun 18, 2008
I'm trying to create a macro that will save backup copies of documents that have changed when a user closes them. I created an add-in and put a Workbook_BeforeClose sub in "ThisWorkbook" that saves a copy of the document in a temp location on the hard drive.
If I have three Excel documents opened and each one has changes and I close Excel (with the "big red X") it only seems to call the Workbook_BeforeClose once backing up one of the files. If I use the "Close Window" (the "little X") it doesn't call that event at all.
Is there a better event to use or a better way to backup these files when closing them or Excel?
View 9 Replies
View Related
May 26, 2009
Is there a way to make a macro fire when you close the VB editor. For example,
I would like one of my pre- recorded macros to start as soon as I close the VB editor.
View 9 Replies
View Related
Jan 23, 2014
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).
View 4 Replies
View Related
Mar 30, 2007
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 Related
May 27, 2014
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]....
View 6 Replies
View Related
Jun 30, 2008
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.
View 9 Replies
View Related
Aug 26, 2006
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..
View 9 Replies
View Related
Dec 11, 2013
How can i save and close one workbook in excel with vba code.
View 2 Replies
View Related
Apr 9, 2007
How can I make one workbook close another without saving?
These all work in the immediate window, but not in the
Private Sub Workbook_BeforeClose
View 9 Replies
View Related
May 7, 2009
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.
View 13 Replies
View Related
Jul 11, 2009
I know this is the code to close a workbook
View 5 Replies
View Related
Sep 22, 2009
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.
View 14 Replies
View Related
Apr 2, 2013
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].....
View 1 Replies
View Related
Jan 18, 2009
Is there anyway that a workbook can be set up to close itself at a set time i.e 19.00hrs every day?
View 9 Replies
View Related
May 11, 2009
is it possible to have a messagebox activate when closing a workbook.
View 9 Replies
View Related
Jul 8, 2006
One of my computers pulls info from my cash register once everyday (sales info). Then I have a excel monthly sales sheet that pulls the info from the register info and puts it in the right places. So the computer pulls the register info at 9:20pm everyday, then I have my task manager open excel at 9:25 everyday and it has a workbook open macro to automatically pull the info into the excel spreadsheet. Here's my problem-- I need to close it.
I'm sure I could use a workbook close macro, but when it tries to close it won't it come up with a save, don't save, cancel prompt? Nobody will be there to hit SAVE so it won't close? Is there just a macro that could save it then close it?
View 9 Replies
View Related
Feb 15, 2008
I have an Excel workbook that runs some code in the back. When I close the workbook file it seems to close OK. However if I then open the VBA editor window I still see the file in the project explorer. If I re-open and then close I have 2 instances in the project explorer... and so on. I have tried setting all objects to nothing etc. but the problem still manifests itself.
View 4 Replies
View Related
Oct 16, 2008
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 Replies
View Related
Dec 5, 2008
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.
View 4 Replies
View Related
