使用VBA通过表单添加工作表导致错误的活动表 [英] Add sheet via form using VBA cause wrong activesheet

问题描述

我有一个包含大量VBA代码的庞大excel工作簿。它适用于excel 2007，但在excel 2013中有一个大问题：有时活动表不是应该的那个。这个问题感觉就像是excel中的一个bug。

I have a huge excel workbook with a lot of VBA-code. It works fine with excel 2007 but in excel 2013 there is one big problem: sometimes the activesheet is not the one what it should be. The problem feels like a bug in excel.

场景：

构建表单并创建一个按钮，使用sheets.add制作新工作表并使用sheet.addcomment放置某处注释。然后尝试在全新的表格中查看该评论。即使你可以看到单元格中的红色三角形，你也无法获得弹出窗口并阅读评论。
然后尝试编辑注释按下单元格上的鼠标右键并选择编辑共享。你得到一个空的评论表，然后你意识到你刚刚在完全不同的表格上创建了​​一个新评论！

Build form and make a button which make a new sheet using sheets.add and put somewhere comment using sheet.addcomment. Then try to look that comment in brand new sheet. Even you can see the red triangle in cell you can't get the pop-up and read the comment. Then try to edit the comment pressing right mouse button on cell and choose edit commet. You get an empty comment form and then you realize that you just created a new comment on completely different sheet!

一些事实：

如果工作表创建按钮位于另一个工作表中，则没有问题。如果你想重现问题，它必须是格式。

If the sheet creating button is in another sheet there is no problems. It have to be in form if you want to reproduce the problem.

看起来问题出在活动表中，但activesheet.name返回新表的名称。所以在VBA中，活动表是应该的，但是当使用鼠标和键盘时，excel excel用户界面上的活动表是错误的。

It looks like the problem is in activesheet but activesheet.name returns the new sheet's name. So in VBA the activesheet is the one what it should be but when using mouse and keyboard just with excel excel UI the activesheet is wrong one.

关于问题的其他讨论

ozgrid.com/forum/showthread.php?t=181943

ozgrid.com/forum/showthread.php?t=181943

mrexcel.com/forum/excel-questions/726376-excel -2013-worksheet-activate.html

mrexcel.com/forum/excel-questions/726376-excel-2013-worksheet-activate.html

social.msdn.microsoft.com/Forums/office/en-US/d9ab7a8a-7e45-4628-8d56-1b3aa55bb383/activate-worksheet-via -button-in-excel-2013？forum = exceldev

social.msdn.microsoft.com/Forums/office/en-US/d9ab7a8a-7e45-4628-8d56-1b3aa55bb383/activate-worksheet-via-button-in-excel-2013?forum=exceldev

所有这些讨论都引入了同样的问题，但没有一个有任何解决方案。有人可以帮助我解决或避免问题。

All of those discussions introduce same kind of problem but none of those have any solutions. Can someone helps me to solve or dodge the problem.

更新：

我只是意识到你不需要添加表格。即使您尝试激活现存的工作表，它也无法正常激活。而且只有在使用表单启动代码时才会出现问题。如果执行按钮在工作表上，则一切正常。

I just realize that you don't need to add the sheet. Even if you try to activate extant sheet it won't activate properly. And again the problem occurs only when you launch the code using form. If the executebutton is on sheet all works fine.

推荐答案

一般情况下，最好不要依赖活动表或激活片。在代码中使用对象要好得多，例如

In general, it is much better to never rely on the Activesheet or activating a sheet. It is much better to use an object in your code, like

Dim shtO AS工作表

Dim shtO AS Worksheet

设置shtO = Thisworkbook.Worksheets（" SheetName" ）

Set shtO = Thisworkbook.Worksheets("SheetName")

或者如果你需要添加一张表

Or if you need to add a sheet

设置shtO = ThisWorkbook.Worksheets.Add

Set shtO = ThisWorkbook.Worksheets.Add

shtO.Name ="新添加的工作表"

shtO.Name = "Newly added sheet"

然后使用它

shtO.Range（" A2"）。Value =" Test entry"

shtO.Range("A2").Value = "Test entry"

这篇关于使用VBA通过表单添加工作表导致错误的活动表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！