Workbook 1 has references to workbooks 2,3,4,5,6. When I click the top cross and choose Save All with Workbook 1 active, the deactivate event in workbook 6 runs to the end as it tries to close, but it can't close because workbook 1 references it. So nothing further happens until I click the top cross again. Then everything closes without any more prompts. Not good. If I could ensure workbook 1 closes first that should solve the problem. There may be another way of course.
I have attached 2 blank files to show the problem. test1 references test2. Open test1. It will open test2. Make a change to both files, activate test 1 and click the top cross, choose save all. Nothing happens usually.
I have many worksheets in a workbook that need to be saved if a user changesanything on them. These sheet names all end in "....SD" and the code needs to only run on those sheets. I have learned alot from the forum but not enough, just yet . . This is what I have so far:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim sht As Object For Each sht In ThisWorkbook.Sheets If LCase(Right(ws.Name, 2)) = "sd" Then MsgBox(Prompt:="You must save changes. Save now?", Buttons:=vbYesNo) = vbYes Then ThisWorkbook.Save End Sub
It doesn't like the 2 "Then's". (Don't laugh - I'm trying.)
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?
I call the macro below from a Workbook_BeforeClose sub; however, if the user has not saved changes before they hit the close then the Sub SaveFile runs and then after the backup save they are then asked do you want to save changes.
I initially had this sub run with Workbook_BeforeSave, but I don't really want to run this everytime the user selects save.
Any ideas on how to how the save changes first and then the backup?
Sub Macro1() Dim NewWB As Workbook, NewName As String Set NewWb = Workbooks.Add NewName = Application.GetSaveAsFilename(ThisWorkbook.Name) NewWb.SaveAs NewName NewWb.Close False Workbooks.Open (ThisWorkbook.Path & "/" & ThisWorkbook.Name) End Sub It seems like I need to rename the sheet I am working on in order for this code to work. If I do not, it tells me that it "cannot save this workbook with the same name as another open workbook".
I am trying to use this code to get around an error with Excel described here: [url]
I would like to have the sheet save itself (not "Save As"), close, reopen, and continue running the macro. What adjustments do I need to make to this code to do this. I have tried different things, but I must be getting the syntax wrong.
I am trying to create a form button that will save as and close the active workbook. I have the Save As code working but I can't find any code to close the workbook that will work. A couple of issues: I don't have control of what the workbook is called - the user will name it - so I can't do a simple Workbooks (" ").Close. Also, I don't want to quit the application. I don't know if the user will have other worksheets open. I also tried ActiveWorkbook.Close and that didn't seem to work either. Not sure what I am doing wrong.
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).
I've got a kind of "menu" sheet that will pull up various reports from Excel spreadsheets. Rather than having the user work with the live data, the menu opens the file and saves it as a copy, closing the original. This is working fine.
The tweak I want to add is, since the user is looking at a copy rather than the live data, I don't want them to save when closing. Is it possible to programatically add a bit of code to the new copy to handle this?
Here's a sample of one of the menu button's code:
Private Sub CommandButton18_Click() Dim wb As Workbook Set wb = Workbooks.Open(Filename:="J:Z PII OvensRack Repair TrackingRack Repair List.xlsx") With wb.Worksheets("Sheet1") .Unprotect Password:="pass" ActiveWorkbook.Worksheets("Sheet1").Copy End With
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 have a an Excel workbook (wb1) with 4 worksheets. I also have a personal.xls that contains 4 worksheets. wb1 has volatile functions.
I have calenders, dropdown lists, read from a file that is closed, etc.. When I open wb1, look at it (I do not make any changes by hand) then close it, it gives me the " Do you want to save?" prompt.
I have tried numerous codes to stop it and I can not get it. I need it to give me the prompt if I have made a change and not give me the prompt if it is just a change from the volatile functions.
I cannot just tell it to save in the background because my boss does not want the date changed if it is just looked at.
The user opens numerous files when searching for a file that is almost what he needs, then he saves it with a new name and goes from there. Or he just opens numerous files to get ideas on how to make the new file.
The user cannot have to do anything to make it work. I know that the code will need to be copied to any existing workbooks. I have the code to do that. That would be done as the files are created to new ones. Also when he is starting from scratch the code would be in the template he uses. Eventually all of the files would be converted
Private Sub workbook_Open( ) Me.Saved = True End Sub
Private Sub Workbook_Open() ThisWorkbook.Saved = True End Sub
I have a macro that opens all workbooks from one directory and runs a macro for each workbook to clean up the data. I cannot figure out how to take all those open workbooks and save them to another directory and close the workbook. Also, I do not want the macro workbook (xlsm) to save. I only want it to close. I am working in 2007 Excel.
I have added a toolbar to an excel application that the company uses. I need this toolbar only to be accessed to this application and not any time that you open excel. I have used the following
Private Sub Workbook_Open() Application.CommandBars("Rithin-Toolbar2006").Enabled = True Application.CommandBars("Rithin-Toolbar2006").Visible = True go_cover End Sub
Private Sub Workbook_close() Application.CommandBars("Rithin-Toolbar2006").Delete Application.CommandBars("Rithin-Toolbar2006").Enabled = False Application.CommandBars("Rithin-Toolbar2006").Visible = False End Sub
This works but only If I accept yes when it prompts me to save changes to the workbook. Each time I open the workbook and close it, it prompts me to save changes to the workbook. I dont want to force a save on the workbook, meaning have code that will save the workbook before closing. I want it such that if the user opens the workbook, and makes no changes, he/she should just close it with no questions asked.
Using the developer tab I inserted an Active X combo box. Under properties I referenced a 'linked cell' that I want the data to appear in. This box works great as long as I keep the sheet open. Once I save and close the workbook and re-open, that reference is lost. It shows #REF! in that field. Why does it lose the reference? I have tried to reference a cell on the same sheet as well as a cell in a different sheet and it keeps the sheet name but not the individual cell.
I need a quick code to where if a user attempts to open my workbook and they have any other workbooks open at the time that it prompts them with a yes no msgbox asking them to save before continuing. Really i just need the code to tell if there is another workbook open in excel.
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.
When a user X's (clicks of the X in the titlebar) of a user form, I want to save whatever input has been made to that point and close the application. this is what I tried but it neither saved input nor closed the application
I know how to add a save and close button but i do not know how to make it instantly available in every new workbook that i open. Currently i click the button and it looks for the macro in the first excel book that i created it in.
how do i make that macro become a default in every workbook?
I have a macro that has to open another worksheet and get data and close the sheet. The problem is when the sheet closes after I have gotten the data it asks if I want to save it. I don't want the user of my macro to see this option because it slows things down. To avoid this in the past I had the worksheet save itself so that it wouldn't ask me to save. Now the worksheet is large and it takes a while to save and it is getting slow. Is there any way not to save it but close it without having excel asking if you want to save it?
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
When a supervisor goes into one of my sheets and adds some things in, she quite often forgot to share it again. To try and combat this I added the following code:-
Private Sub Workbook_BeforeClose(cancel As Boolean) Dim myworkb As String myworkb: myworkb = ThisWorkbook. Name If myworkb = "SLE.MasterSheet.SLE1.xls" Then Call NoProtection ActiveWorkbook. SaveAs Filename:="SLE.Mastersheet.SLE1.xls", accessmode:=xlShared End If Call Limpa Run "DeleteMenu" End Sub
The code appears to do everything it should, and when I first ran it appeared to be ok. However, now it appears to save it as shared yet when you go back into the file it opens up as exclusive.... Is there a reason why the code wont "share" ?
I am trying to fix is that I have two computers networked together both with multiple users all with access to the report form that I am using which is in the shared file. If I have been working on it (I am still trying to do improvements when I have time), and I have to leave for a fire or forget to close the report, no one can access it other than with read-only.
My idea was to run some type of code that either when the screen saver comes on or after so much time with no activity, it would close and save the file with a new file name(just in case I don't want the changes that I have made in the code or something).