vba 一遍又一遍地从用户表单打开用户表单 - 导致对象问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4395033/
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
Opening userforms from a userform over and over - causes object issues
提问by Nick Perkins
I currently have built a tool in Excel 2003 which displays a series of data entry forms. The client has requested that there be "Previous Form" and "Next Form" buttons on the forms.
我目前在 Excel 2003 中构建了一个工具，该工具显示一系列数据输入表单。客户要求在表格上有“上一个表格”和“下一个表格”按钮。
The code used to move between the forms is as follows
用于在表单之间移动的代码如下
Sub NextForm(strFormName As String)
Dim intCurPos As Integer
Dim strNewForm As String
'Find out which form we are currently on from a list in a range
intCurPos = WorksheetFunction.Match(strFormName, Range("SYS.formlist"), 0)
If intCurPos = WorksheetFunction.CountA(Range("SYS.formlist")) Then
'We want to use the first one
intCurPos = 0
End If
'Get the name of the form to open
strNewForm = WorksheetFunction.Index(Range("SYS.formlist"), intCurPos + 1)
'Load the form into the Userforms Collection
Set newForm = VBA.UserForms.Add(strNewForm)
'Show the form
newForm.Show
End Sub
The issue I have is that after you do this 25 times (yeah I know) the system crashes. I realise that this is because everytime you get to the newForm.Show line above the code doesn't get to complete and so sits in memory.
我的问题是，在你这样做 25 次之后（是的，我知道）系统崩溃了。我意识到这是因为每次你到达 newForm.Show 上面的代码行都没有完成，所以坐在内存中。
Modeless forms would stop this issue but then the user could load other forms and do other things which would cause major issues.
无模式表单会阻止这个问题，但随后用户可以加载其他表单并做其他会导致重大问题的事情。
Does anyone have any suggestions to help with this? Somehow to force code execution but not stop the modal ability of the form?
有没有人有任何建议可以帮助解决这个问题？以某种方式强制执行代码但不停止表单的模态能力？
Long shot but appreciate any help.
远射，但感谢任何帮助。
采纳答案by CaBieberach
Maybe you should change your approach.
也许你应该改变你的方法。
I would suggest the following:
我建议如下：
In the main module use a loop to cycle open the form and loop every time the user press the "Next Form" button.
在主模块中，每次用户按下“下一个表单”按钮时，使用循环来循环打开表单和循环。
'This sub in your main Module
sub ShowAndCyleForms
Dim FormName As String
Dim MyForm as Object
Dim CloseForm as Boolean
FormName = "frmMyForm"
do while CloseForm=False
set MyForm = VBA.UserForms.Add(FormName)
MyForm.Show
CloseForm=MyForm.CloseStatus
FormName=MyForm.strNewForm
Unload MyForm
Set MyForm = Nothing
loop
end sub
In every form, declare:
在每种形式中，声明：
Public CloseStatus as Boolean
Public strNewForm as String
In every "Next Form" button, put something like:
在每个“下一个表单”按钮中，放置如下内容：
Private Sub btnNextForm_Click()
CloseStatus=False
strNewForm= NextForm(Me.Name)
Me.Hide
End Sub
Modify your sub to be a function that delievers the next Form Name
将您的 sub 修改为提供下一个表单名称的函数
Sub NextForm(strFormName As String)
Dim intCurPos As Integer
'Find out which form we are currently on from a list in a range
intCurPos = WorksheetFunction.Match(strFormName, Range("SYS.formlist"), 0)
If intCurPos = WorksheetFunction.CountA(Range("SYS.formlist")) Then
'We want to use the first one
intCurPos = 0
End If
'Get the name of the form to open
NewForm = WorksheetFunction.Index(Range("SYS.formlist"), intCurPos + 1)
'
End Sub
You will also need to modify your O.K. to just hide the form instead of unloading it and setting the CloseStatus to true.
您还需要修改 OK 以隐藏表单而不是卸载它并将 CloseStatus 设置为 true。
The idea is to control all your forms loading/unloading from outside the from in a single procedure.
这个想法是在单个过程中控制从 from 外部加载/卸载的所有表单。
Hope it is clear enough.
希望它足够清楚。
