vba Excel 2013 工作表激活
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18726141/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel 2013 worksheet activate
提问by user2766088
I have an issue with activating a sheet from a user form, the same code works fine in Excel 2003 to Excel 2010, doesn't work with Excel 2013.
我在从用户表单激活工作表时遇到问题，相同的代码在 Excel 2003 到 Excel 2010 中工作正常，但不适用于 Excel 2013。
This is how to simply reproduce the issue:
这是简单地重现问题的方法：
Have a workbook with 2 worksheets in it, called Sheet1and Sheet2let's say, and on Sheet12 buttons:
有一个包含 2 个工作表的工作簿，调用Sheet1并Sheet2假设，并在Sheet12 个按钮上：
On click of
Button1activatesSheet2worksheet using aMacro1with 1 line in it:ThisWorkbook.Sheets("Sheet2").Selectand I can edit data from it fine.
On click of
Button2aUserForm1pops up and on click ofCommandButton1call same Macro1 like this:Unload Me Macro1the
Sheet2worksheet is activated, sort of, but if I edit data in it, it actually updates corresponding cells in Sheet1, if I click onSheet1I can see data entered in there!
单击
Button1激活Sheet2工作表，其中Macro1包含 1 行：ThisWorkbook.Sheets("Sheet2").Select我可以很好地编辑数据。
上点击
Button2一个UserForm1弹出，就点击CommandButton1呼叫同宏1所示：Unload Me Macro1该
Sheet2工作表被激活，排序的，但如果它，我编辑的数据，它实际上更新与细胞在Sheet1，如果我点击Sheet1我可以看到在那里输入的数据！
Clicking back to Sheet2worksheet activates the Sheet2sheet properly.
单击返回Sheet2工作Sheet2表可正确激活工作表。
Has anyone seen such behaviour? If yes, are there any coding workarounds to properly activate Sheet2?
有没有人见过这种行为？如果是，是否有任何编码解决方法可以正确激活Sheet2？
回答by Profex
Wow, I was able to reproduce this error using 2013. This is a fantastic failure of Excel's new SDI Interface. What is also interestingly odd is that when you follow the following steps
哇，我能够使用 2013 重现这个错误。这是Excel 新 SDI 接口的一个奇妙的失败。同样有趣的是，当您按照以下步骤操作时
- Select cell
A1onSheet1 - Select cell
B2onSheet2 - Click
Button2which opens the form - Click the
CommandButton1on the form (which hides the form and activatesSheet2)
- 选择单元格
A1上Sheet1 - 选择单元格
B2上Sheet2 - 单击
Button2以打开表单 - 单击
CommandButton1表单上的 （隐藏表单并激活Sheet2）
it looks like cell B2on Sheet2is selected, but when you start typing, the text is going into cell A1; Once you hit Enter the text disappears to cell B2on Sheet1
它看起来像电池B2上的Sheet2选择，但是当你开始打字，文字是进入细胞A1; 点击 Enter 后，文本将消失到单元格B2上Sheet1
You almost have to see it to believe it.
你几乎必须亲眼所见才能相信。
The only thing that I've found that works is using the Ontimefunction.
我发现唯一有效的是使用该Ontime函数。
Private Sub CommandButton1_Click()
Unload Me
'Call Macro1
Application.OnTime Now(), "Macro1"
End Sub
...but I have a feeling that this isn't going to help everyone
...但我有一种感觉，这不会对每个人都有帮助
回答by user3779699
I was able to reproduce the error in Excel 2013 as per Profex's answer. A workaround that solved the issue for me was to make my userform modeless.
根据 Profex 的回答，我能够在 Excel 2013 中重现该错误。为我解决问题的一种解决方法是使我的用户表单无模式。
userform1.show vbModeless
I understand that this workaround won't be helpful if you require a modal form, but hopefully it will save the next person some time.
我知道如果您需要模态表单，此解决方法将无济于事，但希望它可以为下一个人节省一些时间。
回答by Dima Reznikov
The workaround for the above Excel 2013 worksheet activation through automation bug is below (c#):
通过自动化错误激活上述 Excel 2013 工作表的解决方法如下 (c#)：
public static void ActivateSheetAndWorkaroundExcel2013VBASheetActivationBug( Worksheet oSheet2Activate )
{
if( oSheet2Activate.IsNull() )
return;
oSheet2Activate.Activate();
// Excel 2013 has problems activating worksheet through automation
// https://www.google.com/webhp?ie=utf-8&oe=utf-8#q=excel+2013+worksheet+activate+problem+
// http://stackoverflow.com/questions/18726141/excel-2013-worksheet-activate
//
// The only way to reset the Excel 2013 state is to hide/show the active window
if( Application.Version == "15.0" )
{
Window oActiveWnd = Application.ActiveWindow;
oActiveWnd.Visible = false;
oActiveWnd.Visible = true;
oActiveWnd.Activate();
}
}
Call that helper method instead of directly calling oSheet.Activate(). You're welcome:-)
调用该辅助方法而不是直接调用 oSheet.Activate()。别客气：-）
回答by DavB.cs
I found a possible workaround for a similar situation. We have a complex Excel addin that uses both COM and XLL code. (No VBA) We encountered similar issues as above. (When activating a sheet from a modal dialog, the new cell data appeared on the previous sheet).
我找到了类似情况的可能解决方法。我们有一个复杂的 Excel 插件，它同时使用 COM 和 XLL 代码。（无 VBA）我们遇到了与上述类似的问题。（从模态对话框激活工作表时，新的单元格数据出现在前一个工作表上）。
In our case, we discovered that the issue appeared only when our code evaluated a cell Range's ".HasFormula" property. There's no logical explanation. If our compiled .Net code evaluated that property, we would observe the bug. If the code ignored that property, we did not observe the bug. We also found that if we manually clicked back and forth between worksheets (tabs), the bug went away.
在我们的例子中，我们发现只有在我们的代码评估单元格范围的“.HasFormula”属性时才会出现问题。没有合乎逻辑的解释。如果我们编译的 .Net 代码评估了该属性，我们就会观察到该错误。如果代码忽略了该属性，我们就没有观察到错误。我们还发现，如果我们手动在工作表（选项卡）之间来回单击，错误就会消失。
回答by Jason Brady
I think Dima Reznikov has the right answer. I used his C# code and did it in VBA and it seems to fix the problem. My personal code also included the use of a Modal UserForm.
我认为 Dima Reznikov 有正确的答案。我使用了他的 C# 代码并在 VBA 中完成了它，它似乎解决了这个问题。我的个人代码还包括使用 Modal UserForm。
Example:
例子：
Sub Macro1()
Dim ws As Worksheet
Dim win As Window
'... bunch of code here...
ws.Activate
Set win = Application.ActiveWindow
win.Visible = False
win.Visible = True
End Sub
Hopefully this helps someone else who is also confused by the C# code.
希望这可以帮助那些也对 C# 代码感到困惑的其他人。
回答by Joe Laviano
Sub Macro1()
Sheets("Sheet2").Select
End Sub
Sub Macro2()
Unload Me
Call Macro1
End Sub
This works. I could reproduce your error to an extent, but it may be the location of your macros? (modules vs sheets).
这有效。我可以在一定程度上重现您的错误，但它可能是您的宏的位置？（模块与工作表）。
EDIT: I didn't see your comment at first, I have Excel2010 (which is why I couldn't really reproduce it). -Sorry
编辑：我一开始没有看到您的评论，我有 Excel2010（这就是我无法真正重现它的原因）。-对不起
回答by Siddharth Rout
Please avoid using .SELECT. You may want to see THIS?
请避免使用 .SELECT。你可能想看看这个？
Having said that, if you really want to activate the sheet then use .ACTIVATE
话虽如此，如果您真的想激活工作表，请使用 .ACTIVATE
For example
例如
Sheets("Sheet2").Activate
So using that in your code, it will look like this.
所以在你的代码中使用它，它看起来像这样。
Sub Macro1()
Sheets("Sheet2").Activate
End Sub
Sub Macro2()
Macro1
Unload Me
End Sub
回答by Sam Smith
I have the same problem, but my code is actually running in a Excel COM addin, which is slightly different from VBA.
我有同样的问题，但我的代码实际上是在 Excel COM 插件中运行的，这与 VBA 略有不同。
My code worked in 2003,2010... using Sheets("Sheet2").Activate, but not 2013
我的代码在 2003,2010 年工作......使用Sheets("Sheet2").Activate，但不是 2013 年
But I fixed the problem by starting a timer, 100 ms after my event code (Buttonevent).
但是我通过在我的事件代码（Button事件）之后 100 毫秒启动一个计时器来解决这个问题。
Then this trimmer opens the file and uses Sheets("Sheet2").Activate. It is then select, just the same as it used to be in old versions.
然后这个修剪器打开文件并使用Sheets("Sheet2").Activate. 然后选择它，就像以前在旧版本中一样。
回答by Peter Mooren
In the worksheet code on worksheet_selectionchangeevent just put in me.activate.
在worksheet_selectionchange事件的工作表代码中，只需输入me.activate.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Select
End Sub
