打开工作簿错误代码32809在一台电脑，但不是另一台电脑 [英] opening workbook error code 32809 on one computer but not the other computer

问题描述

我有一个excel 2013宏工作簿。

I have an excel 2013 macro workbook.

此工作簿在过去6个月内一直工作正常。今年的第一天我在下面的代码行下面有一个问题。所有这一行代码都是在打开工作簿时将activex复选框控件设置为true。

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

我设法通过上一篇文章解决问题。答案是遵循说明。然而，一个在第一天回到办公室的团队成员在这个工作簿上有问题。他们每天都在使用这个工作簿，没有任何问题。代码在同一行上进行调试，错误代码为32809。

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?

这是子例程。请注意WSCHARTS是public const string - 其值为图表

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

更新

所以我创建了一个新的工作簿，只需在Sheet1&将其重命名为chkTest。

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

另一个更新

不知道这是否与我的问题有关。当我按照前面提到的指示，我没有注意到我的c：\users\username\Appdata\local\Temp\VBE目录我有一个额外的文件，我的同事没有。

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.

我有一个MsForms.exd和RefEdit.exd。我的同事只有MsForms.exd文件。这可能会导致任何问题吗？

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

希望我的最后一次更新

所以更改上面的代码在上面的代码直接在下面的代码现在可以在我们的电脑上工作。有没有人能够解释为什么或有什么不同？

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

推荐答案

只有一个解决方案可以工作100％（相信我，在corp环境中通过（可以说是）所有可能的解决方案）： 摆脱嵌入的ActiveX控件 。

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.

您有三个选项，IMO：

You have three options, IMO:

1. 选项1是将控件的功能移动到功能区中，可能需要根据ActiveX控件添加UserForm（当然不需要用户窗体）。这里有相当多的工作，但它是最干净，最安全的方式。

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是用Form Controls替换ActiveX控件。这真的很糟糕，因为您将失去ActiveX控件为您提供的大部分功能，但它仍然是一个选择。

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是使用Shape对象替换ActiveX控件;一些新的具体问题要处理（例如保护/锁定），但是您可以获得一系列格式化选项。）

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 :)

<在任何情况下，您都需要修改/更新您的工作簿。我主要使用的是选项1和选项3.请注意，您可以在同一个应用程序中使用上述选项的组合。

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.

这篇关于打开工作簿错误代码32809在一台电脑，但不是另一台电脑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！