2

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
Community
  • 1
  • 1
mHelpMe
  • 5,782
  • 15
  • 59
  • 109
  • you mentioned the error code 32809 is related to open workbook error. Check where you open the workbook instead. Post your whole block of code if possible. More info required. Also, if it's working on 1 computer and not other, check accessiblity on your file / folder. – Alex Jan 07 '15 at 21:24
  • When the workbook is opened it calls a sub routine. Running the routine on its own still causes the same error at the same place. We both have the same permissions on the file and directory – mHelpMe Jan 08 '15 at 07:50
  • 1
    do debug.print and run your code with F8 function key. Check if each variable / object is assigned correctly (i.e. the 'wb' value you set for new workbook does contains the correct value). Last thing to check is if the sheet is locked. Again, more codes are required – Alex Jan 08 '15 at 16:03
  • I've stepped through the code all values are as expected & the sheet is not locked. I have posted the code however I don't think the code is the issue – mHelpMe Jan 08 '15 at 17:18
  • Assuming chkAllJPM is a named range, does it exists on the sheet? Or what is it if it's not a named range – Alex Jan 08 '15 at 21:43
  • chkAllJPM is the name of the activex checkbox control – mHelpMe Jan 09 '15 at 08:01
  • 1
    Are the Excel versions the same? Are the security settings the same? Excel options->Trust Center -> Trust Center Settings ->ActiveX Settings -> "Enable all controls"/ Safe mode, etc? – Rusan Kax Jan 09 '15 at 10:56
  • Yes are Excel versions are the same. I've checked the Trust Center Settings. Under ActiveX we both have "Prompt me before enabling all controls with minimal restrictions" and the Safe Mode ticked. Macro settings are the same too. Both have Trust Access to the VBA project object model ticked & enable all macros – mHelpMe Jan 09 '15 at 12:12
  • Now that we are still not sure if the error comes from activex control or access right, I will suggest to write a simple code on the same workbook, just to open the workbook and add a value to a cell (say add "hello" on range A1. See if that prompt for errors. If that's fine, then try another simple function with local activex control – Alex Jan 09 '15 at 15:19
  • did the other user do any Microsoft updates to office that you dont have? – Sorceri Jan 09 '15 at 20:53
  • No we do not believe so. All microsoft updates are managed by our IT team so everything should be the same however something is obviously not – mHelpMe Jan 12 '15 at 09:15
  • @mHelpMe Regarding your last update: Excel does not recognize chkTest ActiveX object any longer but it knows of an object which hase a name "chkTest". Most of the stuff you could do with the chkTest before you will not any longer, but the code change you've made saves you a raised error in this context. – Gene Skuratovsky Jan 16 '15 at 15:42

4 Answers4

5

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.

Jean-François Corbett
  • 34,562
  • 26
  • 126
  • 176
Gene Skuratovsky
  • 591
  • 2
  • 6
1

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.

microstat10
  • 71
  • 3
0

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

For Excel 2013 Install Microsoft Update KB 2726958

Open the File Explorer

Type %Temp% in the address Window

Click on the Excel 8.0 Folder

Delete all *.exd files

Brandon White
  • 1
-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.

alfredo
  • 1