opening workbook error code 32809 on one computer

Question

I have an excel 2013 macro workbook.

This workbook has been working fine for the last 6 months. The first day this year I had an issue below with line of code below. All this line of code did was to set the activex checkbox control to true when the workbook is opened.

Sheets(WSCHARTS).chkAllJPM.value = True

I manage to fix the issue through a previous post. The answer was by following the instructions. However a team member who is back in the office for their first day is having issues with this workbook. Again they have been using this workbook daily without any issues. The code debugs on the same line above with the error code number 32809.

I tried following the same instructions that fixed the issue for me but no joy. I cannot replicate the issues on my computer. Not sure what is causing this error?

Here is the sub routine. Please note WSCHARTS is 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 place a single activex checkbox control on Sheet1 & renamed it chkTest.

I added the code below. The code works on my PC but not on my colleagues computer. This is rather 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 issue. When I follow the instruction mentioned earlier I did notice in my c:\users\username\Appdata\local\Temp\VBE directory I had an extra file which my colleague did not have.

I had a MsForms.exd and RefEdit.exd. My colleague only had the MsForms.exd file. Could this cause any issues?

Hopefully my last update

So changing the code above in the update directly above this to the code below it now works on both our computers. Is anyone able to explain why or what the difference is?

Private Sub Workbook_Open()

Dim ws As Worksheet
Set ws = Sheets("Sheet1")
ws.OLEObjects("chkTest").Object.Value = False

End Sub

Answer 1

My problem is over I deleted all files from the %temp% folder One important thing is that the user account must have Administrator privileges This solves my problem Greetings to all, I hope this helps many.

Answer 2

After trying recommended .exd file deletions that didn't work for us, and considering rewriting vb code in numerous files, we finally solved this problem by uninstalling the MS KB update and not allowing it on subsequent updates. (Office 2013 KB2726958; 2010 KB2553154; 2007 KB2596927). We're hoping that MS will find an elegant way to solve the Active X issue seamlessly in a future update.

Answer 3

Micorsoft relesed an update in December 2014 that is causing errors in Excel application that use ActiveX controls. Apply the fix 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

• http://www.microsoft.com/en-us/download/details.aspx?id=45224

For Excel 2013 Install Microsoft Update KB 2726958

• http://www.microsoft.com/en-us/download/details.aspx?id=45202

Open the File Explorer

Type %Temp% in the address Window

Click on the Excel 8.0 Folder

Delete all *.exd files

Answer 4

There is only one solution that works 100% (and believe me, I've been working through (arguably) all of the possible solutions, in a corp environment): get rid of imbedded ActiveX controls.

You have three options, IMO:

1. Option 1 is to move the controls' functionality into the ribbon, perhaps with an addition of a UserForm depending on an ActiveX control (no UserForms would be needed for a command button of course). There is quite a bit of work here but it is the cleanest and the safest way to go.

2. Option 2 is to replace ActiveX controls with Form Controls. This really sucks because you will lose most of the functionality an ActiveX controls gives you but it is an option nonetheless.

3. Option 3 is to replace ActiveX Controls with Shape objects; some new specific problems to deal with (e.g., protection/locking) but you get a beautiful range of formatting options :)

In any case you will need to rework/update your workbooks. I've mostly used the Option 1, and a bit of the Option 3. Please note that you can use a mix of the above options in the same app.