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.
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 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?
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.
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..
Doesn't Matter Doesn't Matter Doesn't Matter Doesn't Matter Doesn't Matter 1002 Sony Playstation
Basically, I'm looking to populate the blank cells in the PartNumber column (Sample_List) by looking at the data in Column1, Column2, Column3 then going to the Master_List, matching up Column1, Column2, Column3. The PartNumber value in the row with the matching values would then be copied to the Sample_List.
I used the button in Excel 2010 to record a macro that allows me to format a workbook font and stuff because i do that many times a day for several workbooks. However I want to be able to just push a button and have all open workbooks run the macro I recorded.
I am able to make a button- thats easy, but how do I get it to run for ANY workbook? My problem is that apparently it only runs the workbook that is named a certain way. should i post the code here?
We have recently converted to Office 2010 from 2007 I have now got a name conflict with some macros on my templates
I have some code which will open up the affected templates delete the required modules & import the amended modules, this is working OK.
I now have to re assign the new macro to each button on all pages on all my templates., I started of by recording the actions to achieve this (see part of the code), but unfortunately the recorded code will not work, it runs but when I test the buttons I get the message that "this macro does not exist in this work book".
Windows 8.1 Excel 2010, I open a Template, make some changes in the template then I want to save the modified template as a Template ".xltm" under a new name. I'm using the following code for the new workbook name (which works except for the file extension);
I believe that someone showed me once that you could view the same workbook on two monitors with the vien arrange all mode, you could make changes on both screens and the changes would be saved to the same file similar to arranging two views. I cannot rememebr how to do this or even if it is possible.
I have a workbook with a tab for each day of the month. This workbook is placed in a shared file and used by various yards in different locations. A new yard beginning the process is refusing to use it because due to formulas we must lock the workbook (contains many formulas and a live clock macro to track trip time) and they cannot sort A-Z. The sheets are labeled 1,2,3,4,5,6,7,8,9 and so on to 31. I know that you can write a Macro to allow sorting, but I cannot get it to work. the users CANNOT know the password due to hidden revenue etc. and formulas.
I have three workbooks. Every workbook has 50 persons IT information. Those are CPU list, Monitor list, UPS list. I would like to create another workbook. Where all user's IT information will be available. Like X user's information required, under x information his CPU, monitor and UPS information link will be available. How to create link of the CPU, UPS monitor sheets with new workbook?
I have a workbook that contains two sheets with two separate queries. On the first sheet I have two cells designated to accept the values for the parameters. The first sheet accepts the values and populates the table correctly. The second sheet doesn't seem to accept the values and just returns blank rows.
I am having a workbook (say a.xlsm) which has value entry fields and some values are given to another workbook (say b.xlsm) which has some sort of calculations and the result is projected back to the book a. most of the time book b will be closed. I am using Excel 2010.
I opened and saw that the result which is calculated and projected from workbook b is not getting updated. I opened the workbook b and saw that the values I have entered in a has not been updated in b. note that I am opening one book at a time and I do click on update links when I am asked.
I am running a macro-enabled Excel 2010 file, and there is a link to another workbook that pops up every time I open it asking if I want to update. I have searched formulas and pages, and can't find where it's linking from. I searched the VBA module I've been working in, and I can't find the link. When I click Data->Edit Links->Break Links and confirm, nothing happens. It still shows up as linked, and still prompts me to update next time I open it. I'd really like to get this data link severed.
Im using Excel 2010 and have a data connection that I want to use. I added it to a regular workbook and it updates perfectly as-designed. However, I need this feature to work in a shared workbook, but it doesn't.
I am using Excel 2010. I am digging through a workbook with 80-some worksheets. There is one worksheet with 11 data tables. When the workbook refreshes, there is a note at the bottom saying something about 21 data tables. I am not sure where the other 10 are. In an online post, someone said that the Name Manager should show them. But in Name Manager, when I filter on Tables, the list goes blank, and all the icons are the same for the other named ranges. Is there another way to find the data tables?
Hyperlinks are used to navigate internally throughout the workbook on a menu page and back. The hyperlinks started acting up after i saved to xlsm. Have since saved it back to xlsx but now the hyperlinks randomly disapper despite re-adding them over and over again.