PDA

View Full Version : Userform Errors



aworthey
08-17-2016, 08:31 AM
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
08-17-2016, 09:34 AM
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
08-17-2016, 12:00 PM
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
08-17-2016, 12:29 PM
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
08-17-2016, 05:17 PM
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
08-17-2016, 10:30 PM
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
08-17-2016, 10:31 PM
O and by the way: of course you give all controls meaningful names rather than going with the defaults as I did :-)

Aussiebear
08-17-2016, 11:42 PM
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
08-18-2016, 05:59 AM
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
08-18-2016, 06:06 AM
Thanks, everyone, for weighing in!! I appreciate the responses!

Jan Karel Pieterse
08-18-2016, 06:21 AM
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
08-18-2016, 07:42 AM
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
08-18-2016, 10:28 AM
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