I need to alter the Close command on the title bar (the 'X' on the top right corner on the blue bar of excel)
Is there any way to alter the close button on the title bar to have it call a sub I created instead of actually closing excel?
I'm trying to prevent a user from closing a perticular workbook.
Here is some important factors.
Workbook(A) = "LA-01-04-10" This is only one of 52 other but only one is open at a time.
Workbook(B) = "LA.xls" Always the same name and is always opened when one of the workbooks(A) above it opened.
When workbook (A) is opened it will then open another workbook (B) at that time the window is hidden workbook(B) So the user can only see workbook (A) I have a button on workbook(A) that unhides the window to show workbook(B) After the user completes his work in Workbook (B) they need to click another button that sorts the list and returns them to workbook(A) (Which also hides the window for workbook(B).
The problem is I have users that are trying to close workbook(B) instead of pressing the sort list button. So what I want is to prevent the user from closing workbook(B) by displaying a msgbox saying they need to press the button.
Ive tried several approches to this but I'm still having problems. The below script works if workbook(B) is active or shown. The message is displayed and the workbook will not close. However if the user is in workbook (A) and trys to close the message is displayed and both workbooks close like they are suppose to. But why is the message displayed?
One other note I should say is that not only does workbook(A)Open workbook (B) but it also closes it. Which may be why I'm having problems?
I have also included both workbooks so you can see all the code.
I have this code in Thisworkbook of workbook(B)
i am using the following code to close my userform and work book,this disables the exit button on the userform but i can still close the workbook by using the exit buttons on the sheet. can i disable the exit buttons on the sheet so the only way to close the workbook from the userform
Private Sub CmbClose_Click()
Dim ans As Variant
ans = MsgBox("This will close XL, save current file?", vbYesNoCancel)
If ans = vbYes Then 'save book and quit XL
ThisWorkbook.Save
Application.Quit
ElseIf ans = vbNo Then ' no save and quit XL
Application.Quit
ElseIf ans = vbCancel Then 'no save, no close , back to userform1
Exit Sub
End If
End Sub
I have a small macro that before closing the workbook will check a range ("C4:G24") on Sheet ("Data Checks"). It will then show a msgbox displaying each non "OK" result in the range. I want this to close the workbook if all cells in the range have the value "OK" and cancel the close if the msgbox appears. This is what I have so far...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim r As Range, txt As String
With Sheets("Data Checks")
'for each non "OK" result in range
For Each r In .Range("C4:G24")
If r.Text <> "OK" Then
txt = txt & r.Text & vbLf
End If
Next
End With
If txt <> "OK" Then
MsgBox "Please check:" & vbLf & vbLf & txt, vbExclamation
Cancel = True
End If
End Sub
The problem I have is that although the msgbox displays the correct cell values if they are not "OK", it also appears and cancels the close if all cells in range DO have value "OK"???
I would like a macro which disables the close function of a worksheet if a particular cell or has not being filled out?
View 3 Replies View RelatedI have a userform which opens when the workbook opens. Ideally Id like to have the userform open without the workbook coming up and have the workbook close when you close the userform. If thats not doable then is there a way to just close the workbook when the userform is closed?
View 3 Replies View RelatedI run most of my macros out of a file called "Personal.XLS" and I have this bit of code at the end of this particular macro which is closing personal.xls instead of the active file it is working on. It is set up on a loop and it should go to the next file in the directory but since it closes the file I run the macro from, the macro stops immediately. It appears that it believes "ThisWorkbook.Close" is referring to the file from which the macro is initiated.
View 2 Replies View RelatedI have a perfectly functioning piece of code. There is still one snag, however. In the process of running the macro, three new workbooks are opened. I would like to close all three of those workbooks at the end. I am using the following procedure for each of the sheets:
To open the worksheet
Code:
Dim wb As WorkbookMsgBox "Please select the Stakeholder File"SHFile = Application.GetOpenFilename("Files (*.xls),*.xls", , "Please select the Stakeholder Workbook")If SHFile "False" Then Set wb = Workbooks.Open(Filename:=SHFile)
To close the worksheet at the end:
Code:
If Not wb is Nothing Then wb.Close SaveChanges:=False
The only problem is that I am getting bugs when closing multiple workbooks (3). I have assigned the names wb, vb and ab to the three workbooks.
I have some VBA code which opens another workbook, copies data from the first sheet in there and pastes into the current sheet before closing the workbook it got the data from. Is there any way of suppressing any messages associated with closing the other workbook? i.e. do you want to save - or possibly automatically generating a no message to the prompts?
View 3 Replies View RelatedI'm trying to close a workbook without saving it. But everytime I get the message box popupped twice.
These are the codes that I use:
VB:
Private Sub Workbook_BeforeClose _(Cancel As Boolean)
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
[Code].....
I have 4 employees who at the end of their shifts have to email me an excel workbook. The issue is that some of them are forgetful and after placing numerous post-it notes on their monitors one particular employees just forgets.
What i want to do if possible is that when the workbook gets closed, a pop-up window shows up on their screen reminding them to send the workbook. Even better would be if their was a button that they could click that would email the workbook as an attachment in the pop-up box.
I've created a macro which copies the current tab to a new workbook (Book X):
[Code]....
I then manipulate the copied data in Book X. Once this is done I move the manipulated sheet back into original file
[Code] .......
The problem i'm having is I'd like to close the newly created Workbook X file once the macro has run but Workbook X is usually called Book2, Book4, Book7, Book32 etc.
Workbook X will always be the previous file if i use the ALT TAB shortcut.
Is it possible to pop up a message when closing the workbook?
if so, could the message only popup if a certain cell is negative?
For example, This is what i would like to happen.
When workbook close is activated:
If cell A1=(-20), then pop-up "Please check your work and correct".
else, close workbook
How do I SAVE and CLOSE Workbook and it's instance of Excel.
I have other instances of excel open that I do not want closed.
I thought that I had this before, but I can't find it if I did. I do keep a list of all the requests where I ask for help and it's not in this list.
I wrote this macro where, from a userform, I click a button and it will fillout another form with certain data from the original WB to a different WB.
All is working fine and exactly the way I want it except, it won't close and save the other sheet. It will go back to the userform, as I want it to, but it will leave the other file in the background open.
This is the code:
Code:
Private Sub CommandButton6_Click()
'Allocation file fill out
Dim alloc As String
Dim allwb As Workbook
Dim ABA As Workbook
Dim Prompt As String
'Dim fd As FileDialog
Set ABA = ThisWorkbook
[code]....
Well I seem to be having an issue in a form i created. What happens is that when the user clicks a button it saves a worksheet of workbook as its own file, problem is that it will actually open the new worksheet as a workbook when I really just want it to save without opening a new instance. Here is the code
Dim IntWirefilename As String
Worksheets("Intwires").Select
Worksheets("Intwires").Copy
IntWirefilename = "International Wires Database"
Worksheets("Intwires").SaveAs FileName:="File Path" & IntWirefilename & (".xls")
Workbooks("File PathInternational Wires Database.xls").Close
I think the problem lies in the last line or it could be from the select and copy part.
I do not want it to open a new file after it saves it. Also can anyone help create a part that will bypass the overwrite existing file thing so that this file will just continuously save as the same file name rather than prompting the user constantly to Overwrite the file.
I have my Excel workbook open & have made changes. Now I close it and Excel prompts me if I want to save the changes. Is there a way to just close the workbook and automatically accept any changes without have the "Do you want to save changes" message appear?
What about if it is opened in read-only mode? Typically the workbook will not save any changes unless you save it off as another file name. If there is a solution to my question above, can it be enabled only when the workbook is opened in edit mode, or will it not auto-save changes over the file because Excel knows it was opened in read-only mode?
Need the VBA code to close a workbook, and not save it? I need it to open a workbook, run a macro, and close without saving. The code I have thus far is:
View 3 Replies View RelatedI am hoping someone can help. I am new to writing macros and have the following in the this workbook page. When opening the macro does exactly what I want and expect, but does not do the "before close" part. If I select the macro in editor and run it, it does just what I want, so dont understand what is going wrong. Do I have to put in some kind of prompt, or manually run this before close - preferably I would like it to just run.
secondly, I have password protected the workbook, but have to put the password in the macro, is there any way of making this invisible to anyone who then reads the macro....
I have a workbook on a share drive that is used by multiple people. In the event that someone leaves the workbook open after using it, I have a timer function that pops up a splash screen after 4 minutes of inactivity which states, "This workbook will close in 1 minute if there is no further activity". If there is no further activity in that minute, the workbook closes.
The code works fine...unless the workbook is manually closed in that minute between the splash screen and when the timer would have closed the workbook. If that happens, the workbook closes normally and then briefly reopens and closes a minute later when the timer would have closed the workbook. Is there any code to prevent this?
Is there a way (with macros, I''m assuming), to make my workbook always return to the Main tab and Save automatically --- every time somebody closes the workbook from any tab - so that the next person who opens the workbook will have it ready to go on the main tab?
View 5 Replies View RelatedI have a workbook setup with 4 sheets that I'm using. I need the 4 sheets to type in all caps when I enter the data. I found a script, and followed the directions. It works great, yet when I close out of the workbook, and open it back up, the script doesn't work anymore. I go to check the 'view code', and it still there. I resave it, and try it again, and it still doesn't work, what am I missing? This is all new to me. I am saving it as a Workbook Macro enabled file type.
Once I get this resolved, is there a way to make it default to use the script everytime I open the workbook?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("B3:O210")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub
In the various codes I am using AutoOpen, AutoClose, Workbook_BeforeClose and Workbook_BeforeSave Subs. BeforeClose and BeforeSave are in '_ThisWorkbook'; AutoOpen and AutoClose are in a Module. I also use Workbook_Open, Workbook_Activate and Workbook_Deactivate, also in _ThisWorkbook. I put message boxes thru out code to be sure code was running right. The Workbook_BeforeClose is running twice under certain condition (when a cell in active workbook sheet is blank ("")). I do not understand why it is running twice.
For condition where there is data in cell M10, this creates a whole other issue ( multiple running of mutliple various Subs) which I believe I should deal with in different thread/question.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("PanelSched").Activate
MsgBox "Test-Before close"
If Range("M10") <> "" Then
Dim PanelName As String
PanelName = Range("M10").Text
I have a spreadsheet that automatically opens a dialog box when the workbook is opened. This is a little disclaimer intended for other users of the spreadsheet, which if they click ok it simply closes the dialog sheet, but if they click cancel I want it to kick them out of the Workbook without asking them to save changes. I only want it to close that specific workbook incase they have others open that they haven't saved, etc.
The dialog box is opened as follows:
Private Sub Workbook_Open()
DialogSheets("Splash Screen").Show
End Sub
The code I have tried for the closing of the workbook is done by assigning the following macro to the cancel button (note, all but one of the sheets in my workbook is hidden and protected, including the dialog sheet):
Sub Splash_Close()
'
ThisWorkbook.Close SaveChanges:=False
'
'Also tried ActiveWorkbook.Close SaveChanges:=False
'Application.Quit (This closes all excel documents)
'
End Sub
I know there is something I am missing, keeping in mind I am quite experienced with excel, but relatively new to macros and VB programming.
Just a bit extra on this problem. I am creating the file as a template *.xlt, and the error I get when trying to run the example macro I gave at the start is a 1004 error Method close of object _workbook failed.
Also, in an ineresting (baffling for me at the moment) twist is that once you hit end on the error and it just goes to the first page of your spreadsheet, you can then go to ToolsMacros and run the macro from there, and it closes the workbook exactly the way I want it to!
I do not understand why my code returns a Run Time Error"9" Subscript out of range. With the following code, I opened a workbook and then wish to close that same workbook without saving. Eventually I will be pulling information from the workbook but for now I just want it to open and then close right away.
Private Sub cb_NewDate_Change()
ListIndex = cb_NewDate.ListIndex
If ListIndex = 0 Then
Application. ScreenUpdating = False
Workbooks.Open Filename:="I:JoeNetwork Conversion FactorsNetworkConversionFactors_Ver_2_0_0.xls"
Application.Workbooks("I:JoeNetwork Conversion FactorsNetworkConversionFactors_Ver_2_0_0.xls").Close False
End Sub
I have a central workbook that relies on data from other workbooks during the various calculations. I am trying to write some code so that when a user closes the central workbook, all of the other workbooks close as well. I have added my code to the thisWorkbook code, but this does not seem to work. Here is an example of the code. The object is to look for a workbook called Import_Sheet5 and to close it if it is open.
Private Sub Workbook_Close()
Dim Wb As Workbook
Dim sSought As String
sSought = UCase(wbName)
For Each Wb In Application.Workbooks
If wbName = Import_Sheet5 And InStr(Wb. Name, wbName) Then
Wb.Activate
Wb.Close SaveChanges:=False
End If
Next
End Sub
I can open a file at specific time using TaskSchedule. How do I close it at specific time?
I am trying to write a simple one-line code for that but do not know how to combine two statements:
Application .OnTime TimeValue("17:01:00")
and
ActiveWorkbook.Close
Also, this workbook is updated every 1 minute using the same OnTime method and everything works perfectly. However, when I try to close file there is a message saying that "OnTime method object application failed" and I have to click "Debug" or "End".
Only when I click "End", the file is closed.
I have two workbooks (Book1 and Book2). When Book1 opens, its Workbook Open procedure displays a Userform1 that has two Command Buttons. The first Button closes Book1 (exit the program). The second Button opens Book2. Book2 has a Workbook Open procedure that should close Book1 and display a new Userform2. When the Workbook Open procedure of Book2 closes Book1, the procedure stops there and does not show the UserForm2. How can I get the Workbook Open procedure of Book2 to execute both steps of closing Book1 and showing Userform2? This must be done by clicking the second Button on Userform1.
View 9 Replies View RelatedI have a spreadsheet for recording and summarising particular transactions. Column B records the date and Col C details the transaction type (from a data valdation list). Using conditional formatting the cell (in the same row) to whch the transaction value is to be entered is highlighted.
Other cells in the row contain formulas and are locked and the sheet is password protected.
On saving or closing the workbook I want to lock all the cells (used or unused) in any row in which there is a date in column B. How can I do this?
how I would display a message to the user before the workbook is closed regardless if any changes have been made e.g.
Ideally there would on be an “OK” option only by way of acknowledgement and also no option to close the message by the ‘x’ in top right corner.