Had this recuring problem that after doing some work my excel file came up with the error "document not saved". Just managed to fix it so i thought i'd share it here.
This solution may work for you if like me you have a userform (in vba) that uses a RefEdit control.
Open the file, but disable macros.
Delete the RefEdit control (unfortunately my fix is you do without it).
In vba go tools>references. You may have a reference that is prefixed MISSING. Uncheck the reference.
Save the file (with macros disabled you should be able to do this).
Go back in, this time enabling macros and you should not have the problem again
Similar Excel Video Tutorials
I am working with a RefEdit control on a Userform and running into a problem.
I know the RefEdit control is buggy, but unfortunately it is the only
control that makes sense to use in this case. The problem occurs when a
Userform with a RefEdit control is displayed and I click on a workbook other
than the active workbook. This is a strange scenario because it is only
possible when the "show windows in taskbar" option is selected (otherwise
only one Excel window can be seen at a time). When I attempt to click on the
other workbook, Excel prevents it from activating (what I want to happen),
however a second RefEdit box appears at the top of the screen. Both the
RefEdit control on the Userform and the RefEdit at the top of the screen
display the same range. The problem arises if I actually click on (activate)
the RefEdit at the top of the screen, switch back to the Userform (activate),
and then close (cancel) the RefEdit at the top of the screen. This will
close the entire Userform and create a memory problem. The memory problem
prevents the Userform buttons from functioning properly when it is
redisplayed. Eventually I have to force quit or stop the code in the VBE,
which displays the memory error and then Excel shuts down. Is there a way to
prevent this error or even better to prevent the second RefEdit box from
appearing when a user attempts to activate a different workbook?
I have a multi-sheet workbook that has numerous buttons which have
macros assigned to them. Clicking on the button sends the user to a
particular spreadsheet, prints a spreadsheet, etc. All the buttons
work fine. However, recently when I attempt to save the file (either
via File Save As or Save), I get a message box saying the document is
not saved. Same error message if I attempt to save to a new file
name. The workbook is not protected.
Any suggestions? I suspect the macros assigned to the buttons because
if I disable the macros, the file will save. Of course then I lose
the macros that control the buttons.
I'm using Office 2000, (same problem with Office 2002).
I created a spreadsheet for work that uses quite a bit of macros that I wrote in VBA.
Everything was running well, until I attempted to add a splashscreen, that launches a macro. The macro performs a web query on a small website, and if a cell range is valid, it continues running. If not valid, it closes the workbook.
I saved the file.
Now when I try to load the file, I get the following Error.
"Error in loading DLL"
I went to VBE and checked TOOLS>REFERENCES
and there is a MISSING: Ref Edit Control listed.
I try to uncheck the box and I get the error "Can't perform requested operation"
I'm at a complete Loss.
I have noticed that I can't pull up any of the Modules....
Any suggestions would be greatly appreciated!
I am asking for any tips concerning RefEdit Control. I am building a userform with a refedit control to allow the user to select a range of cells in a workbook different.
The macro that calls the userform containing the refEdit is repeated as many times as the number of items contained in one of the listbox of the main userform.
The first time the refedit is used there is no bug but then it is not possible to change the activesheet of the active workbook and the way the refedit is filled in during the range selection is very awkward.
Hope you will get my english and my explanations ... and above all give me a tip to solve my problem
thanks in advance
I have a spreadsheet with a set of macros. One of the macros used the Microsoft Calendar Control 11 to furnish a popup calendar. However, I have disable this by erasing the page code which referenced the control.
I have given a copy of the spreadsheet to a coworker. Everything worked fine until she got Office 2010, at which point the macros quit working with the error message "Unable to find project or library." Using the VBA References dialog box, I have traced this issue to the fact that the Office 2010 installation on her computer does not have the Calendar Control 11 file. I went into the References dialog & tried to uncheck Microsoft Calendar Control 11, but I get an error message telling me it can't be deleted because it's in use. I've looked through my other macros & can find no references to it. The help box on the error message says to remove the control from the form & then uncheck the box. There are no forms listed in the project window.
Can someone tell me how to find where this may be so I can delete it? While we could just install the control file on her computer, I'd really prefer to clean up the spreadsheet so I don't keep running into this every time I provide a copy to someone.
Any help appreciated.
A Workbook that I have just gotten about 95% complete has suddenly simply stopped working.
I am using Excel for Mac, When I try to open the Workbook it starts to open then just quits and exits and asks if you want to send an error report.
On my PC it did open and i was able to work with it but i had to save as in stead of just saving.
back on the Mac, I tried to open the newly renamed sheet, it opened but with debug errors from some macros that run on startup, which had been working fine.
I tried to just delete some of the macros but here is the BIG problem, it wont save at all. All i get is an error message "Document not saved"
Finally when i try to disable macros i get a "this workbook contains Excel 4.0 macros that cannot be disabled" Which according to the PC warning "may contain harmful viruses" any code not from me came from this forum so i wasn't too worried about them.
What do I do? does anyone have an idea, or encountered something like this?
I am having a problem with a .xlsm file after converting it from 2003 to 2007.
When I initially opened the file the macros worked. As soon as I saved the file and re-opened it, I received a "macros disabled/encryption" message and they stopped working.
I did another conversion and the macros again worked. If I close the file without saving and then re-open it, the macros continue to work. It's as if as soon as the file is saved, the macros disable.
The only thing I have found is that when I look at the name of the macro in the Macro Menu, it reads 'Test1.xlsm'.Module1.MACRO1' rather than just MACRO1.
It's like when you have more than one file open and it's showing you macros in another workbook (even though the file name shown is the one I am having issues with). I have "enabled all macros", "trusted access to the VBA project object model" and also added the relevant foldes in the Trusted Source centre, but to no avail.
Sorry for the long winded post.
Had a workbook with macros developed in Excel 97. Loaded it into excel 2007. Works fine saved it in xlsm format. If I try to encrypt the file I get error message that basically says can't do it with old macros. I cannot find any reference on how to change macro sto be Excel2007 compatible. Is there one? So I saved the file in xls format. All is OK.
BUT, if I converted or rewrote the macros, I have read about another problem (and duplicated it). apparently you cannot open encrypted xlsm files with macros enabled. Can't find a way to override this. Found some reference to SP2. Thats what I have running. Found suggestions for registry change to disable virus scan of macros. Did not work.
I have opened up an excel file I have been working on at home on my work computer and I got this message at the beginning "error dll not found"
then I went to VBA/Tools/References and saw a tickbox saying: MISSING REFEDIT.DLL
I unticked the box and re-opened the file and everything appears to work fine..
any ideas on why that happened?
I work on this file almost daily between home and work and I didn't encounter this problem before..
I remember I have messed around userforms a little bit and copied a drawing object (assigned to a macro) from another file at home ...
I have an Excel sheet at work with macros attached to it. I saved the
worksheet as a different file name so I could make changes. Now anytime I try
to execute any of the macros in the original worksheet, it first opens the
file that I "saved as" before it'll execute the macro. I deleted the file
that I'd "saved as" and now it won't execute a macro at all. It gives me an
error message saying it can't find a specified file. I've checked over the VB
coding of the macros to see if Excel made reference to the new file that I'd
saved, but can't find it referenced anywhere in the code
When I open a particular file, two excel icons appear in the tray. When I click on Window in the menu, the file name appears twice - once with a :1 at the end and again with a :2 at the end of the filename.
I have some macros imbedded in a word document that interacts with this particular file. I have saved the file to a new name and when reopened, the newly saved file produces the same result. I have rebooted several times thinking I may have a dangling reference to the file. No difference.
If I open another file in the same Excel session, both icons disappear. Then the only way I can switch back forth from one workbook to another is via code or using the Window item on the menus.
The file has NO macros imbedded in it - it is only used by other macros.
Any help would be greatly appreciated.
I am trying to implement a simple userform using the RefEdit control.
So I have the RefEdit control and an Ok (which has code attached to it) and a Cancel commandbutton.
For some reason, I can't get the focus on the RefEdit control (i.e. when I activate the form, I have to actually click in the RefEdit box before it gets the cursor). Which property sets the focus in this control?
Right now I have the Ok button Default property set to True.
I have a commandbutton on the spreadsheet that activates the userform.
Hope there is an easy solution for this. I have several macros in a workbook that work fine, however, if the file name is saved as something different, the macro errors because the it's performing the function of referencing the workbook in which it is run from.
Is there a line of code I can insert that will tell the macro to reference the file name in the workbook in which it is saved? Otherwise, when I save as a different file name I have to manually edit the different macros and change the file name.
Excel 2002, Windows XP Version 2002, SP1
I have a RefEdit control on a UserForm that has both Change and Exit
events working without a problem. I subsequently defined a Class
module that would contain a RefEdit control and moved the UserForm's
code to the Class module. In the Class module, the Change event code
works without a problem, but the Exit event's code will not work. Even
something as simple as the code below won't work. Any ideas on why
not? One would think that if the Change event code worked, they Exit
event code would work as well.
Public WithEvents RefEdit As RefEdit.RefEdit
Private Sub RefEdit_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Thanks in advance.
I'm developing an XLA that incorporates the use of the RefEdit control on a userform. Everything works just fine, but I noticed that the reference to the control's DLL is pointing at a specific version of Excel:
C:\Program Files\Microsoft Office\Office11\REFEDIT.DLL
If the end user has a different version of Excel this will go poorly. I'd like to keep the use of the control or implement an approximation.
Does anyone have a suggestion on this?
I'm trying to set a reference to my personal file (VBE TOOLS>REFERENCES). I keep getting a message box with a title "Microsoft Visual Basic", an "X" inside a red circle (critical) and "400" as the message, with "OK" and "Help" buttons. I click "Help" and get no help, just the "getting started" screen of help. I click "OK" and the reference is not set.
I have had no problem setting the reference before.
I just discovered that macros were turned off in this file. I closed the file and reopened it with macros enabled and the Error (?) went away.
I have had this same message box before under different circumstances and was never able to discover what it meant or if it was causing any problems.
Can anyone enlighten me as to what "400" means?
I have been trying to write what I thought would be a simple macro to save a worksheet as a pdf file with a file path based on cell references. For some reason, I can't seem to get it to work and have tried everything I know to fix it.
The macro I wrote looks like this:
Public Sub Save_As_PDF_Test()
Dim Name As String
Dim Stamp As String
Dim WhereTo As String
Dim sFileName As String
WhereTo = ActiveCell.Value
Stamp = ActiveCell.Value
If Stamp = "" Then Stamp = Date
Name = ActiveCell.Value
sFileName = WhereTo & Name & "_" & Stamp & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sFileName, Quality _
:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
When I run the macro, I get the following error message:
"Run time error '1004' Document not saved. Document may be open, or an error may be encountered when when saving."
The document has indeed been saved in .xlsm format and other macros appear to be running fine. The Error shown when running the debugger is in the next to last command to save to PDF ("ActiveSheet.ExportAsFixedFormat Type:.....")
I am running Excel 2010 in Windows 7 on a virtual machine using Parallels on a Mac. I have had not trouble with macros that save documents to a cell reference file path - the error appears isolated to the Save to PDF command.
I'm interested in any ideas how to get this working.
I have an excel spreadsheet with approximately 20 sheets. The file has many macros and formulas embedded in it. The file size is about 10MB.
My problem is that when I make all the necessary changes to the sheet and try to save, I get the error msg "Document not saved". It's not a network problem because I moved the file to my local PC. It's not disk space because I have plenty of space. I am using Excel 2000 SP3.
Has anyone experienced anything similar? I read in some places that it might be the Anti-Virus causing the problems. We use Norton AV Corporate Edition.
Thanks in advance!!
Hi - has anyone had the following experience in Excel 2007?
1) I have some simple macros in a workbook.
2) When I fire up Excel, it asks if I want to enable macros. I press "yes".
3) The macros in the workbook do not work. A message pops up saying "The macros in this project have been disabled".
If I select "no" in step 2 above, and then manually enable macros from once the file has opened, the macros work fine. This is annoying because macros in my personal.xls file remain disabled, and also, I wish to share this file with others, and I want things to be as simple as possible.
Anyone have any ideas why this is happening?
Thanks in advance!
Have a project going and have had a disturbing trend develop. I'm working in Excel 2003. Upon opening the file and enabling macros, I started receiving File/Path errors, at which point it would shut down the application. When opened, I have a splash that would come up, but it wouldn't go to the next userform. At this point I would get the error message and the file would be closed. If I then reopened the file, disabled macros, saved under a different file name, and then reopened with macros enabled, everything worked fine, until I shut it down and then tried to reopen, at which point the same error would occur.
Lately I have simply been getting the Microsoft Excel dialogue box telling me an unexpected error occurred and it had to be shut down. I'm at a loss for what is causing this. The last time this happened, when Excel recovered the file, it told me a repair had been done, and the repair was to basically delete all of the VBA code from the file. Obviously, this is a problem. Ideas?
I have worked on a file for long time using Excel 2003. I have few macros and custom functions written. I decided to use another computer to do some additional work on this file - mainly new formulas and outline, but no code writing. The file was opened as 2003 and was saved in the same fashion, was not converted to 2007 at any point.
I saved it and closed it. I then opened it on my original computer and after opening it gave me "Automation Error - Catastrophic Failure" message without an error number. Right after that it sent me to VBA screen and instead of having my file name under the Project Tree, it has "VBAproject". I click on it and can see my code and my forms, but the worksheets are not label properly plus they have this little blue icon next to them.
I tried to open the file again by clicking Disable macros, and no error was given but obviously can't do much with the file. Now the option of Enable/Disable macros is not showing at all, which is very odd! Not only on this file.
One thing I read around is about sharing and references. This file sharing options were turned off so that's not it. I then realized under VBA-Tools-References that the office 2007 is referencing different files for some of the selections, like Visual Basic For Applications, Microsoft Excel 12.0 Object Library, etc. I copied those files to the proper folders on the 2003 computer but that didn't do the trick. The problem is that with my 2003 computer I can't check these references because the file crashes on opening and it restricts me from going to that option.
Any ideas? I did quite of work on this file so redoing this is the last resort but I hope I don't have to go that route.
Thank you in advance!
I have a Macro that I wrote that I need to be able to run. I am using Excel 2007. This workbook and macro were written in Excel 2007, and I saved the file as a "Macro Enabled Worksheet". The Macro was working fine before and after saving it.
When I opened the worksheet later today and tried to run that Macro, it says,
"Because of your Security settings, Macros have been disabled. To run macros, you need to re-open this workbook, and then choose to enable macros. For information about enabling macros, click Help."
I did click Help, and there was nothing useful there. (If their solution had worked, it would have been useful, but it did not.)
My security settings for Macros are currently "Disable all macros with notification."
It doesn't, however, give me an notification or choice to run the macros or not.
I need this macro to work, and one solution that I read (save the macro to your personal workbook and access that when you need it), I didn't even try because it is not an option. I need to be able to pass the Macro with the workbook to some other users.
Thanks in advance for any help. This is the first major problem I've encountered with Excel 2007, but it is an extremely annoying one.
Here's a little public service (and a reference point for me the next
time this happens if I forget) for people stuck with this type of Excel
You try to save a file (for me, 53Mb excel file using extensive VBA and
autofiltering to an NTFS compressed network share) and get the
following type of message;
Your changes could not be saved to '<<filename>>.xls", but were
saved to a temporary document named 'D933D120'. Close the
existing document, then open the temporary document and
save it under a new name."
You attempt to save and get an error "File not saved".
You close, reboot, wait for a three-quarter moon etc etc, try to open
the temporary file and get the error
"Excel cannot access 'D933D120'. The document may be read-only or
Thank you to those authors describing the above as a garbage bucket
error message, commonly having nothing to do with either read-only
attributes or encryption, which got me thinking "what else could stop a
file being opened".
Here's the solution;
Right click the file
For me, the security settings were blank. It appears that the saving
process which creates this temp file when "overwriting" (actually
creates new file(the temp file above), deletes old, renames new with
old name) an old file fell over before it could apply the relevant
access lists and rename the file.
Simply add yourself as an authorised user for the file, with full
I was then able to open the file, rename etcetera, as described on the
original error message.
As to the original cause of the issue, ie why the save process fell
?http://support.microsoft.com/kb/291204/ - didn't get identical
messages, so not 100%
?Various mentions of antivirus software - unconfirmed
?NTFS compression - takes so long on some large files that a timeout of
some sort occurs- hypothetical only. May depend on network traffic at
I have had a go at creating a refedit style control for excel. I no
longer need this control (and it probably shouldn't be used anyway) so
I'm putting it up for public consumption.
Basically, download the control from:
extract the file (extension on website is .txt but its actually a
..zip), and run the install.bat file.
To test, double-click on test.xls. If you a prompted for a missing
reference, re-ref to RangeEditControlTest.dll. Then run the TestStart
This is just an example, but if you want to suggest
improvements/extensions then do. There are lots of bugs.
Here is the scenario:
Macro written/recorded in a blank workbook.
Macro is written to open two other files - an excel file and a .txt file
All files are on a shared drive (here at work)
When I go to save the blank workbook (the one in which the macro "lives"), I get a message - "document not saved".
I write/record macros all the time and I've never gotten this message before. I tried saving all files involved to my hard drive...still got the error message. Can anyone come up with a reason/solution? I've been searching the net all morning...none of the scenarios that I've come across match mine exactly. Thanks.