Before Close Event (check User If They Logged Out?)
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
I've been tasked at work to make an excel file to do more than what I think I can figure out on my own. What I need done is this:
1. Every Monday, check excel file for data 2. If data exists, send email with said data
Here's my issues:
How do I get the workbook to open while logged off the computer? The file is stored on a share network drive, not locally. How do I get it to send an email without someone logged on to click "send"?
I tried using windows scheduler, but I don't have sufficient privileges to run as batch (IT restrictions).
I am having an issue with Excel creating a duplicate file when logged in as a user with restricted permissions on our network. If I modify a file while logged in as another user, excel will save the file but will also create a duplicate file with an eight character random name and no file extension. For example if user "Dave" opens a file called test.xls and saves it, in that same folder on the server a file named 296E9E20 will be created. Users are using Windows XP SP3 boxes and the server is running Win 2003. I am not sure why this only happens with Excel.
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?
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
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)
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.
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
I am trying to check a user input to see if the user has entered the date correctly. I am using the code below but it keeps giving me a "Type Mismatch" error.
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.
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?
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'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?
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
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.
How can I get excel to always perform a spell check before I close the document. I've tried using VBA but it won't execute. (I'm really bad at VBA.) This is what I tried but it wouldn't work:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
how you can remove the close 'X' on the title bar of a user form please? I have used this for a password input box but if the user selects the 'X' on the title bar then it closes the password box without them having to enter a password and allows them into the workbook.
Assume that the workbook Book1.xls opens a user form named myUserForm.
I'm trying to show only the user form on the desktop when I open Book1.xls, with Book1.xls minimized on the Taskbar.
And , if the Cancel button on the form is clicked, then unload the form and close Book1.xls.
That simple!
In ThisWorkbook, I have the event:
Private Sub Workbook_Open() ActiveWindow.WindowState = xlMinimized ShowTheForm End Sub On the Form, I have the Cancel button:
Private Sub btnCancel_Click() Unload Me ThisWorkbook.Close True End Sub
The above procedure shows the form, with Microsoft Excel blank window in the background, and produces a small bar at the bottom left of the screen for Book1.xls Click the Cancel button, and both the Form and Book1.xls close, but the blank Excel window remain open !
I suppose I would not be able to edit this version of Book1.xls, but that is fine for now!
How do I define the variable in a text box so that it stays in that text box after the close of the dialog box. For example, I enter "Test" in TextBox1 then close UserForm1. I want to be able to open UserForm1 back up and still have the same user defined text in there.
How can I get excel to always perform a spell check before I close the document. I've tried using VBA but it won't execute. (I'm really bad at VBA.) This is what I tried but it wouldn't work:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Cells.CheckSpelling SpellLang:=1033 End Sub
I have a workbook that has a macro that needs to run before it is closed. I have a button on my sheet that runs that macro and then closes the sheet. Is there a way that I can disable the "X" at the top right hand of the workbook and force the user to close the workbook via my button?
I have a macro that runs when the workbook opens. At the end of my macro I've added a Yes/No box. If 'No' the workbook closes without saving. If 'Yes' I would like to restart the macro. I can close it ok, but I don't know how to restart the macro from line 1 when 'Yes' is selected. Here is what I have:
YesNo = MsgBox("Done! Would you like to run again?", vbYesNo + vbCritical, "Caution") Select Case YesNo Case vbYes
'I don't know what to put here :(
Case vbNo ActiveWorkbook.Close savechanges:=False End Select
We have one shared excel workbook and it is used by many people (more than 20 simultaneously), is there any way to auto save & close the workbook (session) if a user is inactive for specified time.
how do i go about setting a message box to pop up every 10 mins or so prompting the user to close the workbook.? i want it to be as irritating as possible! iv'e had a look at ontime but cant get my brain into gear!also don't some of these timers fire even if workbook closed?
if you can prevent an event macro from running at will.
The problem is occuring when I have a userfrom with various controls, and the change of one control triggers an event macro that changes other controls, this then in turn triggers further control change macros.
I have tried altering the macro name to things like optionbutton1_change, select, enter etc.
The only other way around it I can think of is to introduce a binary variable, but that will mean ensuring it os always the right value and then coding an IF statement on each event macro.
Therefore, I'm thinking there must be an easier way, some kind of switch ??? Something that says ignore any event macro when the control is changed as a result of different event macro.
- I'll include a small example below just to clarify what I am talking about.
private sub optionbutton1_click()
userform1.textbox1.value = "Test Change"
end sub
private sub textbox1_change()
msgbox "Change taken place"
end sub
What I am trying to achieve is getting excel to ignore the second macro when the control is changed by another macro rather than a user entry.
I have these codes in place in my excel file, there is nothing wrong with them at all. Here is the the problem : My superior runs the macros in place in the file almost daily, and when he is done he then closes it and all is well. The macros does things to other files so they are changed in a way that the supervisors like them. I avoided putting macros in these individual files, because of the attempt to make it as simplistic as possible without them being able to affect overall file. The problem happens when he (my superior) accidently makes a change in the primary sheet in the workbook with all the macros, it affects alot of my formulas and macros that are already in place. Within this workbook, it keeps a log of things from the other files results, so I have to have a record of it in the workbook, hence having a save event in place.
The Question: Is it possible to create a macro button, that will allow him to disable the codes below, so that he can close it and open again, so that it doesn't show any of the errors that happens by accident and try to close it again so there is no errors? Basically, I am asking what is the code for this, and where would I place it in.
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim mylogoff Set mylogoff = Sheet1. Range("A65536").End(xlUp).Offset(0, 1) mylogoff.FormulaR1C1 = "=NOW()" mylogoff.Copy mylogoff.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = Fals mylogoff.Offset(0, 1).FormulaR1C1 = "=RC[-1]-RC[-2]" Application.DisplayAlerts = False Sheets("Start Here").Select ThisWorkbook.Save ThisWorkbook.Close End Sub......................
I have a userform, every time the user clicks on the start button it recodes there name, today’s date and their start time when they hit finish it records their end time.
The next time the user hits start it will record the users name, today’s date and start time on the next row (below their last entry)
The problem I have is that if the user clicks on start and then does not click on end button, but instead exits excel, then reopens the user form and hits start. The start will go on the row below, but now when they click on the end button the end time will go into the previous row for the end time, as they did not put it in if you know what I mean.
I want to stop the user from being able to exit excel if they had clicked on start
Private Sub Workbook_BeforeClose(Cancel As Boolean) Cancel = True MsgBox "You have clicked on Start and therefore mus Click the End button before you can exit excel.", vbCritical, "Cannot Close"
End Sub
This disables the X in the top right hand corner, I have aready disabled the one in the user form. However the following code does not let the user exit via File and Exit. Is there a way to put an IF statement into this that say if CmdStart has been clicked then the user can not exit, How every if it has not then they can exit.
I have read threads on this forum as well as on others and this does seam to be and issue.
OR is there a way of Hiding the X button that is in the top right hand corner. However this will still allows the user to hit start button and then exit excel, which will continue with the problem.
