Userform Error Check and Textbox Help

**Impartial Derivative** · 12-01-2011, 02:41 AM

Hey Guys,

I've created a program to load and manipulate text files of data. When the macro is initiated the first thing the user encoutners is my userform which contains 5 textboxes requiring the user to input integer values in the text box. The only buttons on the userform are a calculate button and a cancel button. I have already written the code for the cancel button but need some assistance witht the calculate button. Currently, pressing the calculate button hides the userform and loads a file launcher where the user can select their data file and then the macro imports the data directly into an excel worksheet. Once this is completed the userform unloads. However, at the moment the calculate button will launch the file chooser even if the 5 textboxes are empty. This is my first issue:

What is a simple and efficient code to prevent the file chooser from launching if the textboxes are not all filled with integer values?

Secondly,
How can I display these textbox values in my newly generated spreadsheet?

Here is my code for the calculate button so far:


Private Sub Done_Click()

Dim blnFileOpened As Boolean

Initialize.Hide

blnFileOpened = OpenText

If blnFileOpened Then
    Call Destrezas
End If

Unload Me

End Sub

Thanks again!

**royUK** · 12-01-2011, 03:16 AM

Try this

Private Sub Done_Click()
    Dim oCtrl As MSForms.Control
    Dim blnFileOpened As Boolean
    For Each oCtrl In Me.Controls
        If TypeName(oCtrl) = "TextBox" & oCtrl = Empty Then
            MsgBox "Please complete all text boxes", vbCritical, "Input required"
            Exit Sub
        End If
    Next oCtrl
    
    Initialize.Hide
    blnFileOpened = OpenText
    If blnFileOpened Then
        Call Destrezas
    End If
    Unload Me
End Sub

For entering data see the DatabaseForm

**Impartial Derivative** · 12-01-2011, 01:10 PM

Roy,

Thanks for the quick response but I'm having some trouble with the code.

If TypeName(oCtrl) = "TextBox" & oCtrl = Empty Then

From this snippet of code For the TypeName portion should I replace this with the name of the userform or the name of the textbox? I have currently tried both and have generated run time errors in both instances.

**royUK** · 12-01-2011, 03:26 PM

You shouldn't need to change anything.

**Impartial Derivative** · 12-01-2011, 06:24 PM

When I attempt to run the code, I receive a run time error that says wrong number of arguments or invalid property assignment. Any ideas on what this could be from?

Note:

Also, when thinking about my code leaving the text boxes blank would be okay but I do need to ensure that the input values are integers and not text

**royUK** · 12-02-2011, 03:24 AM

The problem is in your part of the code. I haven't a clue what your input is doing, my check code works fine on it's own

Have you got a UserForm called Initialize, your code is attempting to hide that form

 Initialize.Hide

I can't see any variable named OpenText & whether you give it a value

**Impartial Derivative** · 12-06-2011, 10:20 AM

Roy,

I define OpenText with a function I had written in a different module as follows:

Function OpenText() As Boolean

Dim OpenFile As Variant

MsgBox"Please select a text file",vbOKonly
OpenFile=Application.GetOpenFilename("Textfiles (*.txt),*.txt", ,"Open a textfile...")

      If OpenFile <> False Then
              
                 ' Code specific that excel import the tab delimited tab with spaces

      OpenText=True
      End If

End Function

This function has always seemed to work in the past but it seems to be having trouble running with the error check for the user form. Any help is greatly appreciated.