Unloading and Showing UserForms problem

Hi All,

I Have a Spreadsheet that uses 2 Userforms. Userform1 and Userform2.

The main Userform is Userform1.

If a certain textbox on Userform 1 is Filled with a specific Value type in this case Numbers then Userform1 Unloads, Userform2 loads and the activecell is offset to another cell (This must happen everytime userform2 is used) once the Textbox in UserForm2 has been filled it unloads and Userform1 reloads to carry on.. this can happen consistantly.

Code for the TextBox in Userform1 to Unload and show UserForm2:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

ActiveCell = TextBox1

If IsNumeric(TextBox1.Value) Then

Dim oneControl As Object
 
For Each oneControl In UserForm1.Controls
    Select Case TypeName(oneControl)
    Case "TextBox"
        oneControl.Text = vbNullString
    End Select
Next oneControl
Unload Me

UserForm2.Show

End If

End Sub

Code for UserForm2:

Private Sub TextBox9_Enter()
ActiveCell.Offset(RowOffset:=-1, ColumnOffset:=4).Select
Label2.Caption = ActiveCell.Offset(, -1).Text
End Sub

Private Sub TextBox9_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ActiveCell = TextBox9
Dim oneControl As Object
 
For Each oneControl In UserForm2.Controls
    Select Case TypeName(oneControl)
    Case "TextBox"
        oneControl.Text = vbNullString
    End Select
Next oneControl
Unload Me
UserForm1.Show
End Sub

This works for the First instance that UserForm2 is shown but when it is called again the offset doesn't work.

Can anyone tell me why?

Any help would be greatly appreciated.

Regards

JRidge

Hi Norie,

I am Using a Hand Scanner or Keyboard + Enter to enter the data in the Fields of the Userforms.

Regards

JRidge

This is an example of what it should do and what it actually does on the second time the Userform2 is called.

Userform1 enters in the cells B:F

TextBox1 = B (Alphanumeric) example MW-01-001-01
TextBox2 = C (Numeric) Example 1234567890
TextBox3 = D (Alphanumeric) Example F123
TextBox4 = E (Numeric) Example 9087654321
TextBox5 = F (Numeric) Example 654

If instead of Alphanumeric a Numeric number is entered in TextBox 1 Example 1234567890

The system knows that another container is located in the same location as the line before.
It Adds a new Line above the current Row, and pastes the 1234567890 in the E column of the new Row and then automatically moves 1 column right.

Userform2 then opens to add the Qty number to column F but because this action moves the activecell to the start of the next process i am moving it back to the cell that would receive the Qty.

This works for the first time that the Userform2 is called, but when called a second time the offset doesnt work again and the activecell stays in the cell on the next row at the start of the process.

I don't know why it works the first time but not the second..

Hope this makes sense.

Tried a mixture of Both:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim quantity As Long

    ActiveCell = TextBox1

    If IsNumeric(TextBox1.Value) Then

        Dim oneControl As Object

        For Each oneControl In UserForm1.Controls
            Select Case TypeName(oneControl)
                Case "TextBox"
                If oneControl.Name <> "TextBox1" Then
                    oneControl.Text = vbNullString
                End If
            End Select
        Next oneControl
 
        quantity = InputBox("Please enter quantity of " & TextBox1.Value & ":")
        
        ActiveCell.Value = quantity
        
        For Each oneControl In UserForm1.Controls
            Select Case TypeName(oneControl)
                Case "TextBox"
                
                    oneControl.Text = vbNullString
                
            End Select
        Next oneControl
        Cancel = True
    End If

End Sub

Seems to be working...

Or like this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim quantity As Long

    ActiveCell = TextBox1

    If IsNumeric(TextBox1.Value) Then

        Dim oneControl As Object

        
        quantity = InputBox("Please enter quantity of " & TextBox1.Value & ":")
        
        ActiveCell.Value = quantity
        
        For Each oneControl In UserForm1.Controls
            Select Case TypeName(oneControl)
                Case "TextBox"
                
                    oneControl.Text = vbNullString
                
            End Select
        Next oneControl
        Cancel = True
    End If

End Sub

How could i say: If Active cell is in Column F then Offset activecell by (,-1) or words to that effect...

If Not ActiveCell.Column = 5 Then
    ActiveCell.Offset(RowOffset:=0, ColumnOffset:=-1).Value = vbNullString
    ActiveCell.Offset(RowOffset:=0, ColumnOffset:=-1).Select
End If

This seems to do it..

If activecell isnot in Column 5 then delete contents in the cell to the left and set that cell as the active cell..

Not neat but seems to work