Excel用户表单输入失控 [英] Excel Userform Input going haywire

问题描述

我有一个名为 SlideSorterStart 的用户表单的以下代码:

Private Sub Okay1_Click()Dim startOn 作为整数startOn = SlideSorterStart.Input1卸载 SlideSorterStart结束子

Okay1 是下面的确定按钮，而 Input1 是文本框的名称.

我在模块中使用变量 startOn 如下:

Sub SlideSorter(ByVal control As IribbonControl)

Dim first As Long: first = ActiveWindow.Selection.SlideRange.SlideIndexDim last As Long:last = ActivePresentation.Slides.CountSlideSorterStart.ShowFor i = first to last使用 ActivePresentation.Slides(i)出错时继续下一步.Shapes("Squort").删除昏暗的正方形作为形状设置正方形 = .Shapes.AddShape(msoShapeRectangle, 400, 360, 150, 150)带方形.Name = "Squort"使用 .TextFrame.TextRange.Text = 开始以 ' 文本框结尾End With ' Square 本身结束于开始 = 开始 + 1接下来我结束子

出于某种原因，它没有给出与填充到用户窗体中的数字相等的输出，而是始终从 0 开始第一个，然后在下次运行该函数时，增加幻灯片的数量.

例如，如果有 5 张幻灯片可以放置此框，那么第一次幻灯片 1 的值为 0.下一次为 5.然后为 10，以此类推.

这是什么原因造成的?

解决方案

UserForm1.Show 又来了！

您正在显示表单的默认实例，并且在该表单的代码中，您指的是该表单的默认实例:

Dim startOn As IntegerstartOn = SlideSorterStart.Input1卸载 SlideSorterStart

SlideSorterStart 标识符在这里有双重用途:它是 UserForm 类的数据类型名称，和它是一个项目范围的全局变量以该表单类命名的对象变量.

您永远不应该在表单的代码隐藏中引用表单的默认实例.使用 Me 保留标识符来引用当前实例(可以是默认实例，但也可以是其他任何实例).

startOn = Me.Input1

无论您做什么，都不要在显示表单时卸载表单，并且您稍后需要访问其实例状态(例如，用户提供的控件内容).>

按如下方式更改表单的代码:

选项显式Private StartAtSlideIndex As Long私有已取消为布尔值公共属性 Get StartSlideIndex() As LongStartSlideIndex = StartAtSlideIndex最终财产公共属性 Get IsCancelled() As BooleanIsCancelled = HasCancelled最终财产私有子 Input1_Change()On Error Resume Next '如果无法转换文本值，索引将为 0StartAtSlideIndex = CLng(Input1.Text)出错时转到 0结束子私有子 Okay1_Click()我.隐藏结束子私有子 OnCancel()已取消 = 真我.隐藏结束子Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)如果 CloseMode = VbQueryClose.vbFormControlMenu 那么取消 = 真取消万一结束子

请注意，表单代码隐藏中的无处是否会破坏表单，或者有任何机会被破坏:这就是为什么我们需要处理 QueryClose，以防止如果用户通过单击红色的X"按钮取消对话框，则表单实例不会被销毁 - 由于用户始终可以通过这样做来取消任何对话框，因此显示表单的代码需要知道对话框是否被取消.如果您决定添加一个 Cancel 按钮，您只需使其 Click 句柄调用 OnCancel 方法即可.

因此，无论表单如何关闭，我们都只会隐藏它，并让调用代码销毁它.

SlideSorterStart.Show

这显示了表单的默认实例.避免这样做.

With New SlideSorterStart '<~ 表单实例在此处创建.表演如果 .IsCancelled 然后退出 SubDim startOn As LongstartOn = .StartSlideIndexEnd With '<~ 表单实例在此处被销毁

表单的 Initialize 处理程序，如果存在，将在 New SlideSorterStart 调用(在对象引用被交给 With 块之前));如果存在，表单的 Terminate 处理程序将在 End With 处被调用.

当您使用表单的默认实例时，这两个关键对象生命周期事件的确切时间是不确定的:这就是为什么您需要不惜一切代价避免它，并完全控制您正在使用的对象，以及您何时以及如何销毁它们.

你得到 0 的原因是因为表单正在系统地卸载自己；默认实例与其状态一起被销毁，然后在下次引用时再次重新创建，但随后使用默认初始状态重新创建，...这意味着您实际上丢失了用户的输入.

I have the following code for a userform called SlideSorterStart:

Private Sub Okay1_Click()

Dim startOn As Integer
startOn = SlideSorterStart.Input1
Unload SlideSorterStart

End Sub

Okay1 is the OK button here below, while Input1 is the name of the text box.

I use the variable startOn in a module as follows:

Sub SlideSorter(ByVal control As IRibbonControl)

Dim first As Long: first = ActiveWindow.Selection.SlideRange.SlideIndex
Dim last As Long: last = ActivePresentation.Slides.Count

SlideSorterStart.Show

For i = first To last

    With ActivePresentation.Slides(i)

        On Error Resume Next
        .Shapes("Squort").Delete

        Dim square As Shape
        Set square = .Shapes.AddShape(msoShapeRectangle, 400, 360, 150, 150)

        With square
            .Name = "Squort"

            With .TextFrame.TextRange
              .Text = startOn
           End With   ' TextFrame

        End With ' Square itself

    End With

    startOn = startOn + 1

Next i

End Sub

For some reason, instead of giving an output equal to the number filled into the UserForm, it always starts the first one at 0, and then the next time the function is run, increments by the number of slides.

For instance, if there are 5 slides to put this box on, then the first time, Slide 1 will have a 0. The next time at 5. Then a 10, and so on.

What is causing this?

解决方案

UserForm1.Show strikes again!

You're showing the form's default instance, and inside that form's code, you're referring to the default instance of that form:

Dim startOn As Integer
startOn = SlideSorterStart.Input1
Unload SlideSorterStart

The SlideSorterStart identifier is dual-purposed here: it's the data type name of a UserForm class, and it's a project-scoped global object variable named after that form class.

You should NEVER refer to a form's default instance within that form's code-behind. Use the Me reserved identifier to refer to the current instance (could be the default one, but could be any other too).

startOn = Me.Input1

And whatever you do, NEVER Unload a form while that form is being shown and you need to later access its instance state (for example, the user-provided contents of a control).

Change your form's code behind as follows:

Option Explicit
Private StartAtSlideIndex As Long
Private HasCancelled As Boolean

Public Property Get StartSlideIndex() As Long
    StartSlideIndex = StartAtSlideIndex
End Property

Public Property Get IsCancelled() As Boolean
    IsCancelled = HasCancelled
End Property

Private Sub Input1_Change()
    On Error Resume Next 'index will be 0 if can't convert text value
    StartAtSlideIndex = CLng(Input1.Text)
    On Error GoTo 0
End Sub

Private Sub Okay1_Click()
    Me.Hide
End Sub

Private Sub OnCancel()
    HasCancelled = True
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        Cancel = True
        OnCancel
    End If
End Sub

Note that nowhere in the form's code-behind does the form get destroyed, or gets any opportunity to be destroyed: that's why we need to handle QueryClose, to prevent the form instance from being destroyed if the user cancels the dialog by clicking the red "X" button - and since the user is always free to cancel any dialog by doing that, the code that's showing the form needs to know whether the dialog was cancelled. If you decide to add a Cancel button, you only need to make its Click handle invoke the OnCancel method.

So, regardless of how the form gets closed, we only ever Hide it, and let the calling code destroy it.

SlideSorterStart.Show

That's showing the form's default instance. Avoid doing that.

With New SlideSorterStart '<~ form instance gets created here
    .Show
    If .IsCancelled Then Exit Sub

    Dim startOn As Long
    startOn = .StartSlideIndex
End With '<~ form instance gets destroyed here

The Initialize handler of the form, if present will be invoked at New SlideSorterStart (before the object reference is yielded to the With block); if present, the Terminate handler of the form will be invoked at End With.

Exactly when these two critical object lifecycle events are fired when you use the form's default instance, is non-deterministic: that's why you need to avoid it at all costs, and take full control of what objects you're using, and when and how you're destroying them.

The reason why you're getting 0 is because the form is systematically unloading itself; the default instance gets destroyed along with its state, and then it gets re-created again next time it's referenced, but then it's re-created with the default initial state, ...which means you effectively lose the user's input.

这篇关于Excel用户表单输入失控的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！