Userform Errors

[aworthey]

Hello,

I'm encountering a very frustrating error with one of my userforms. I have 15 textboxes on one userform that require the user to input a special code from a custom numbering system. To assist in selecting the appropriate number code, I created another userform that launches from command buttons next to each textbox. To avoid having 15 copies of this number code finder userform, I assigned the textbox name to a hidden label caption on the number code finder userform. This label caption is assigned to a variable that I'm using to make sure that the selected number code populates the correct textbox on the previous form. In the case an appropriate number code doesn't exist yet, I have yet another form for its creation. This hidden textbox name gets passed on to the next userform, as well.

This has been working perfectly until I tried to modify the number code creation userform. When I launched the userforms, whatever modifications I made were deleted. I could not figure out why this was happening. So, I recreated the userform from scratch with all the additional fields I needed. When I launched the userform, all labels, textboxes, and comboboxes were deleted...the only thing left on the userform were the two command buttons.

Has anyone else encountered anything like this before?

Here's the code that is involved when I click debug:

Private Sub cmdNewSN_Click()
Dim boxSMART As String
boxSMART = frmSMARTNumberPicker.bxSMRTvalue.Caption
With frmSMARTNumberCreator.bxSMRTvalue
    .Caption = boxSMART
End With
If frmFinishTA.cboModel.Value <> "" Then
frmSMARTNumberCreator.ComboBox4.Value = frmFinishTA.cboModel.Value
Else: frmSMARTNumberCreator.ComboBox4.Value = ""
End If
Unload frmSMARTNumberPicker
frmSMARTNumberCreator.Show
End Sub

[aworthey]

New info:

I just recreated the userform and exported it. I disabled the code where I encountered the debugging. Then, I saved and closed my workbook. I opened another workbook to import the userform, and it was blank! I then opened the original workbook, and the userform was blank there, too! I never ran any code.

Is there a limit to the number of userforms a workbook can have? I have never encountered anything like this before.

Any ideas?

[aworthey]

Additional info:

I created a new file so that I could build the userform in a new workbook in an effort to not lose my work again. However, when I clicked save and close, the controls disappeared again on the userform. With a little more experimentation, it seems that any control that I place in a frame disappears with the frame. No trace of the frame or the controls appears in the properties dropdown window.

I have plenty of userforms with frames. Why are my frames suddenly deleting from Excel?

[aworthey]

Last bit of info:

In my toolbox, I have 2 frame options. One frame option disappears; the other option works perfectly so far. Strange.

[mikerickson]

You have three different user forms involved in this.
Have you considered using a Multi-page Control rather than subsidiary user forms?

[Jan Karel Pieterse]

I'm not sure I understand your setup, but I would do it like this:
Userform1 is the form that is showing
Userform2 is the form that asks the user for a number
So in Userform1:

Private Sub CommandButton1_Click()
    Dim sNum As String
    sNum = GetNumber()
    If Len(sNum) = 0 Then
    'cancelled
    Else
        TextBox1.Value = sNum
    End If
End Sub

In any normal sub:

Option Explicit
Function GetNumber() As String
    Dim uf As UserForm2
    Set uf = New UserForm2
    uf.Show
    GetNumber = uf.TheNumber
End Function

In Userform2 we have just TextBox1 and Commandbutton1 (an OK button) and CommandButton2 (Cancel), with this code:

Option Explicit
Public TheNumber As String
Private Sub CommandButton1_Click()
    TheNumber = TextBox1.Value
    Me.Hide
End Sub
Private Sub CommandButton2_Click()
    TheNumber = ""
    Me.Hide
End Sub

[Jan Karel Pieterse]

O and by the way: of course you give all controls meaningful names rather than going with the defaults as I did :-)

[Aussiebear]

O and by the way: of course you give all controls meaningful names rather than going with the defaults as I did :-)

LOL, therein lies a battle with most programmers.

[mikerickson]

Jan, One slick variation on your plan is to move the GetNumber function to inside Userform2.
Userform2 has two Command buttons, butOK and butCancel, a TextBox1 and a Label1

' in userform2 code module

Public Function GetNumber(Optional Prompt As String = "Enter a Number") As Double
    Me.Label1.Caption = Prompt
    
    Me.Show
    
    If UserForm2.Tag = "OK" Then
        GetNumber = Val(UserForm2.TextBox1.Text)
    End If
    
    Unload UserForm2
End Function

Private Sub butOK_Click()
    Me.Tag = "OK"
    Me.Hide
End Sub

Private Sub butCancel_Click()
    Unload Me
End Sub

This would be used with code like this in some other module

Dim userEntry As Double
    
userEntry = UserForm2.GetNumber("Show me a number")
    
If userEntry = 0 Then
    MsgBox "User might have entered text or pressed cancel or entered 0"
Else
    MsgBox "User entered " & userEntry
End If

The trick is that during the .Show line, the user might have canceled or corner X'ed. So after .Show line, one can't use the Me keyword because Me might be unloaded, so Userform2 has used (and explicitly unloaded).

[aworthey]

Thanks, everyone, for weighing in!! I appreciate the responses!

[Jan Karel Pieterse]

Good point. Minor quibble: I like my implementation slightly better as it separates the function from the form and it is easier to do away with that userform and just use inputbox.

[mikerickson]

Agree for the pseudo-input box, but for multi-input or multi-output or choose-from-list situations, where an InputBox isn't a good option, the notion of putting everything inside the userform is keeps extraneous code out of the main routine.

[SamT]

Var Types needed unknown.
"m" prefix used for internal variables used with User Defined Properties

UserForm1 Code:

Dim mNeedsNumber

Property Get NeedsNumber() 
NeedsNumber = mNeedsNumber
End Sub 

Property Let NewNumber(mNewNumber)
Me.Controls(mNeedsNumber).Value = mNewNumber
End Sub

Sub TextBox1_Click() 'and all others

mNeedsNumber = "TextBox1"
frmSMARTNumberPicker.Show
End Sub

frmSMARTNumberPicker Code:

boxSMART = UserForm1.NeedsNumber
'blah, blah, blah
UserForm1.NewNumber = Whatever
'blah, blah, blah