Error code 32809 is opened on one computer but not on another computer
I have excel 2013 macro tutorial.
This book has been working great for the past 6 months. On the first day of this year, I had the below issue with the below line of code. This whole line of code was to set the checkbox to true when the workbook is open.
Sheets(WSCHARTS).chkAllJPM.value = True
I manage to solve the problem through the previous post. The answer was the following instructions . However, a team member who returned to the office during his first day has problems with this book. Again they used this book on a daily basis without any problem. Debug the code on the same line above with error code 32809.
I tried following the same instructions that fixed the problem for me, but no joy. I am unable to reproduce the problems on my computer. Not sure what is causing this error?
Here is the subroutine. Note that WSCHARTS is a public const string - its value is "charts"
Sub SetDefaultSetting()
' set the default view upon opening the spreadsheet
Dim ws As Worksheet
Dim wsTime As Worksheet
Set wsTime = ThisWorkbook.Sheets(WSTSJPM)
Set ws = ThisWorkbook.Sheets(WSCHARTS)
' get last date
Dim lRow As Long
lRow = wsTime.Range("A65536").End(xlUp).Row
ws.DropDowns("DropDownStart").ListFillRange = wsTime.Name & "!" & wsTime.Range("A2:A" & lRow).Address
ws.DropDowns("DropDownEnd").ListFillRange = wsTime.Name & "!" & wsTime.Range("A2:A" & lRow).Address
ws.Range(COLDATES & "1") = 1 ' start date is 12 dec 2013
ws.Range(COLDATES & "2") = lRow - 1 ' latest avaiable date
' control are linked to cells so just need to change their cell values
ws.Range("C1") = 6
ws.Range("D1") = 7
ws.Range("E1") = 8
ws.Range("F1") = 9
ws.Range("G1") = 10
' rest should be blank
ws.Range("H1") = 1
ws.Range("I1") = 1
ws.Range("J1") = 1
ws.Range("K1") = 1
ws.Range("L1") = 1
Sheets(WSCHARTS).chkAllJPM.value = True
ws.OLEObjects("chkBOAML5").Object.Enabled = False
Set wsTime = Nothing
Set ws = Nothing
UPDATE
So I created a new workbook and just put one checkbox on active1 and renamed it to chkTest.
I have added the code below. The code works on my PC, but not on my colleagues' computer. It's frustrating now. Any ideas?
Private Sub Workbook_Open()
Sheets("Sheet1").chkTest.Value = True
End Sub
Another update
Not sure if this has anything to do with my problem. When I followed the instructions mentioned earlier, I noticed in my c: \ users \ username \ Appdata \ local \ Temp \ VBE directory, I had an additional file that my colleague did not have.
I had MsForms.exd and RefEdit.exd. My colleague only had an MsForms.exd file. Could this cause any problems?
Hopefully the latest update
So, by modifying the above code in the update right above this code below, it now works on both of our computers. Can anyone please explain why or what is the difference?
Private Sub Workbook_Open()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
ws.OLEObjects("chkTest").Object.Value = False
End Sub
source to share
There is only one solution that works 100% (and trust me, I've worked (probably) every possible solution in a corporate environment): get rid of the built-in ActiveX controls >.
You have three options: IMO:
-
Option 1 is to move the functionality of the controls to the Ribbon, possibly adding a UserForm depending on the ActiveX control (no custom forms are required for the custom button, of course). There is a lot of work here, but this is the cleanest and safest way.
-
Option 2 is to replace ActiveX controls with form controls. It really sucks because you will lose most of the functionality that ActiveX controls provide, but it's an option nonetheless.
-
Option 3 is to replace ActiveX controls with Shape objects; some new specific problems to solve (like protection / locking) but you get a lovely range of formatting options :)
You will have to rework / update your books anyway. I mainly used option 1 and a little option 3. Note that you can use a combination of the above options in the same application.
source to share
After recommending deleting the .exd files that didn't work for us, and after considering rewriting the vb code in numerous files, we finally resolved this issue by uninstalling the MS KB update and not allowing it on subsequent updates. (Office 2013 KB2726958; 2010 KB2553154; 2007 KB2596927). We hope MS finds an elegant way to fix the Active X issue without problems in a future update.
source to share
Micoresoft re-updated an update in December 2014 that causes errors in Excel that use ActiveX controls. Apply the hotfix depending on the version of Excel on the machine.
Close all Office applications
For Excel 2007 Install Microsoft Update KB2596927
For Excel 2010 Install Microsoft Update KB 2553154
For Excel 2013, Install Microsoft Update KB 2726958
Open "File Explorer"
Enter% Temp% in the address box
Click the Excel 8.0 folder
Delete all * .exd files
source to share