Excel Userform Input going haywire

Question

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?

Answer 1

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.