Excel VBA Run-time Error 32809 - Trying to Understand it

Question

A colleague at work made some changes to one of our macro workbooks and now on my PC only I receive the dreaded Run-time Error  32809  when I attempt to run it  This latest version runs fine on his PC and another colleague s PC that we tested it on  The previous version runs fine on all of our PC s  all of which are running Excel 2010   The error is thrown when the macro attempts to Select the Worksheet index 1  named  Info   I know that Select Activate is not required but am just working with this Workbook for now and am trying to work out why I alone would receive this error   I have tried    Reboot Power Cycle Saving a Copy of the Workbook Cleaning out Temp Files with CCleaner Researching online Checking for ActiveX Controls  Uses Form Controls    All with no success  I then had a bit of a mess around in the immediate window and discovered that even a simple   Debug Print ThisWorkbook Worksheets 1  Name   would throw the run-time error which lead me to believe that somehow that Worksheet had broke  I added a couple of events to the Worksheet including  Activate and  Change but none would fire even after confirming that   Application EnableEvents   True   I added a simple Test Sub as follows   Public Sub Test        Dim ws As Worksheet     Dim sheetNum As Integer      For Each ws In ThisWorkbook Worksheets         ws Select             Selects all Sheets Without Error         Debug Print ws Name   Prints All Worksheet Names Fine     Next ws      Set ws   ThisWorkbook Worksheets 1      ws Select                 Selects Sheet 1 Without Error        Prints all but sheetNum   1  Run-time Error 32809     For sheetNum   7 To 1 Step -1         Debug Print ThisWorkbook Worksheets sheetNum  Name     Next sheetNum        Run-time Error 32809     ThisWorkbook Worksheets 1  Select  End Sub   Has anyone run into anything similar to this or know of what causes this error to occur only on some PC s

User · Answer

I have encountered similar  nearly unexplainable  behavior  Found a reference to deleting  exd files under the directory C  Users username AppData Local Temp Located one in each of the directory Excel8 0 and VBE   Typical name is MSForms exd  Google  Excel exd  or  KB 2553154   From my perspective  it is a completely unacceptable situation which has been there for at least a month now

User · Answer

I got this problem after adding a combobox with VBA-code in a particular sheet  Testing the code etc was no problem at all  until I opened the sheet again  Stackoverflow and Microsoft comes with many work arounds  but no real solution  I use excel 2010  dutch version  with W10  upgraded from W7   I think the problem is in Excel 2010  In my case  I got an error on the line to unprotect a sheet by VBA  in a module which wasn t changed for a long time   Ok  this is how it is in my opinion  There was a security issue in FM20 DLL  for whic MS had an update in Q1 2015  This update installs a new FM20 DLL  however the language packages  FM20NLD DLL and FM20ENU DLL  were not updated  Possibly  if you don t use a language pack  you don t have this error  In my opinion  the language parts should have been updated as well  but there is no update available   Ok  deleting the  exd-files works for a moment  This is a temporary work around  MS doesn t has a real solution  but recompiling the code  solves  the problem   That is why some people said   Add a comment and the problem is solved   Yes  adding a comment forces a recompilation   I agree  this is still a work-around  but not a temporary work around  So   1  check in which part of the VBA-code the error exists 2  add a comment by which a recompile is forced  3  save the project again  that s all

User · Answer

I have found the solution  Just download the following Office update  https   support microsoft com en-us kb 2920754  Choose between 32-bit or 64-bit and install   Worked for me  hope it works for you    Regards

User · Answer

I ve been struggling with this for awhile too   It actually occurred due to some Microsoft Office updates via Windows Update starting in December   It has caused quite a bit of a headache  not to mention hours of lost productivity due to this issue   One of the updates breaks the forms  and you need to clear the Office cache as stated by UHsoccer  Additionally  another answer thread here  Suddenly several VBA macro errors  mostly 32809 has a link to the MS blog with details   Another of the updates causes another error where if you create or modify one of these forms  even as simple as saving the form data  it will update the internals of the spreadsheet  which  when given to another person without the updates  will cause the error above   The solution  if you are working with others on the same spreadsheet    Sadly  either have everyone you deal with use the office updates  then have them clear the office cache  or revert back to pre Dec  14 updates via a system restore  or by manually removing them    I know  not much of a solution  right   I m not happy either   Just as a back-story  I updated my machine  keeping up with updates  and one of the companies I dealt with did not   I was pulling out my hair just before Christmas trying to figure out the issue  and without any restore points  I finally relented and reformatted   Now  a month later  the company s IT department updated their workstations   And  without surprise  they began having issues similar to this as well  not to mention when I received their spreadsheets  I had the same issue    Now  we are all up on the same updates  and everything is well as can be

User · Answer

I exported the VBA Module - resaved the file  then imported the module again and all is well

User · Answer

I suffered this problem while developing an application for a client   Working on my machine the code forms etc worked perfectly but when loaded on to the client s system this error occurred at some point within my application   My workaround for this error was to strip apart the workbook from the forms and code by removing the VBA modules and forms   After doing this my client copied the  bare  workbook and the modules and forms   Importing the forms and code into the macro-enabled workbook enabled the application to work again

User · Answer

It seems that 32809 is a general error message    After struggling for some time  I found that I had not clicked on the  Enable Macros  security button at the below the workbook ribbon   Once I did this  everything worked fine

User · Answer

I have similar problem  The VBA  ActiveX  code  had been working fine on 20  computers for a few years  the problem suddenly surfaced out when one new colleague joined  the code doesn t work on his new laptop  although the Excel version is same  it showed Run-time Error  32809   I have checked all security settings of ActiveX and Macro  all correct  I did some experiments  I found the Code created in my computer will not work on my colleague s new laptop  However if I create code in my colleague s new laptop  it works in my computer  Once I saved this code in my computer  it won t be able work in my colleague s laptop any more   By checking the error in details  the problem is that all code written in my computer  my colleague s Excel won t recognize them  Debug  Compile will show compile error  even  DIM      is not recognized All the ActiveX Controls   Comboboxs  Buttons    the property-name were randomly assigned a new one  For example  I have a button name as  AddNew   once open in my colleague s new laptop  it is re-assigned as  commandbutton54    It even can not recognize the existing worksheet name   After researched all the solutions   I found Delete    exd  file doesn t work Even empty the recycle bin      Add comments  doesn t work     In the end the solution is   Step1  Make a copy of the sheets with codes sheets w o code is not required    Step2  Delete the original sheets    Step3  Rename copied sheet to their original name    It starts to work  I found all ActiveX controls get back their original name in their property  It just took a minute  Hope it help those who are facing the same problem  Note  No need to save the file to   txt or rename to xlsx

User · Answer

This worked for me using excel 2010 and getting the same error when opening a macro-enabled  xlsm file   -after dismissing the error dialog  do  save as  tab-delimited  txt file  click OK for         only active sheet          functions not saved    -then  save as  again  but this time select macro-enabled  xlsm format   to another file or overwrite original doesn t matter  but save as another feels safer    -close out excel   -open the newly saved  xlsm file  In my cases  the error messages went away and the macros are working

User · Answer

I did the following and worked like a charm    Install Office 2013  I haven t tried with 2010 but I think it would work too   Install Office 2013 SP1  Run Windows Updates and install all Office and Windows updates  Reboot computer  Done    This worked for me in two different computers  I hope this will work in yours too

User · Answer

I have removed all ActiveX controls from sheet and now it works smoothly without any error messages  That s my solution

User · Answer

My solution   may not work for you   Open the application on a machine that is flagging the error  Change the VB code in some way    I added one comment line of code of no consequence into one of the macros   Sheets sheetName  Select  comment of no consequence  and save it  This causes a recompile  Close and re-open - all fixed   Hope this makes sense and helps  Grant

User · Answer

Deleting all instances of   exd resolved it for me

User · Answer

In my case following helped    Save file as  xlsx  macro-free  - all macros would be erased while saving  Open source file with macros and copy modules to the  xlsx file  Save file as  xlsm - full recompile performed    Afterwards everything started working normally  I had file with 200  sheets and 50  macros and posting comments in each module didn t help  but this solution worked

User · Answer

I have the same problem and found that this is the problem of Microsoft vulnerabilities   It works for me when I install these update patches  You can find these patches on www microsoft com     If your office 2010 version is SP1  you need download and install office SP2 pack first  Update patch name is KB2687455  Install update patch KB2965240   This security update resolves vulnerabilities in Microsoft Office that could allow remote code execution if an attacker convinces a user to open or preview a specially crafted Microsoft Excel workbook in an affected version of Office  An attacker who successfully exploited the vulnerabilities could gain the same user rights as the current user  Install update patch KB2553154   A security vulnerability exists in Microsoft Office 2010 32-Bit Edition that could allow arbitrary code to run when a maliciously modified file is opened  This update resolves that vulnerability

User · Answer

Error 32 809   Copying the corrupt sheet to a new sheet and changing the name or deleting the corrupt sheet works for me   Additionally  I went to the SHEET Module of the corrupt sheet and removed the coding from the sheet Module associated with the corrupt sheet   That ALSO cured the problem for me    The sheet modules can have routines that are triggered by events specific to that worksheet    So in my case  I think it was a corrupt Sheet Module  not corrupt data on the worksheet itself

User · Answer

Ok  this might be weird  Anyway one of my colleagues had this error and we tried the edit VBA compile whatever  But the thing is  just copy the excel file to the desktop  And it worked  The Excel file was originally in a network drive  This worked  this is my answer to this issue

User · Answer

In my case  the error occurred executing a macro in  Sheets  own sheet one   Select  copy the sheet into another with other name  ie   oso   then delete the original sheet and renamed the new one as  own sheet one   Excel 2013

[excel] Excel VBA Run-time Error '32809' - Trying to Understand it

Examples related to excel

Examples related to excel-2010

Examples related to vba