A question about a Userform - Pressing Enter to Input Value

[JohnGow383]

I currently have an input box showing when a certain set of conditions are met when first opening my spreadsheet. It asks to enter the number of miles. I input and press enter, the data is transferred which then triggers a set of other actions. You can also click Okay and same is done. Works fine.
However, I was wanting to spruce it up a bit with a simple Userform (Userform3) to mimic this action however it's behaving strangley. I have the same enter and cancel buttons. It works fine when clicking Okay, it transfers the data from the Textbox and then unloads the userform. I have tried Key up and key down Enter and others to try and get the number to transfer when pressing enter. Eventually I got it to work somehwhat by having the same code for commandbutton enter but here's the kicker. Works as expected when pressing enter but if clicking the command button (Okay) it crashes or gives a runtime error. I know this is probably a very simple fix but really struggling. I have a test button in worksheets where the debugger will tell me where the error is. It's only a userform3.show command. Here is the code for the transfer data button on the useform

Private Sub CommandButton1_Click()
ThisWorkbook.Worksheets("NOON Figs").Range("I5") = TextBox1.Text
Unload UserForm3
End Sub

Private Sub CommandButton1_Enter() 'Strangley, I needed both click and enter enabled like this to be able to enter miles and press enter for it to transfer and unload textbox
ThisWorkbook.Worksheets("NOON Figs").Range("I5") = TextBox1.Text
Unload UserForm3
End Sub

Here is the code for the Textbox

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'Ensures a valid numer is entered
With UserForm3.TextBox1
     If IsNumeric(.Text) And TextBox1.Value > 0 And Texbox1.Value < 600 Then
        .BackColor = &HC0FFFF 'when number is entered correctly changes to pale yellow
     Else
        MsgBox ("Not a valid entry. Check correct milage and ensure to add numbers only"), vbCritical
        .BackColor = vbYellow 'error will change to dark yellow
        Cancel = True
    End If
End With
End Sub

I think I'm missing something extrememly obvious but can't fathom it.
Also, when the userform did work it would not unload until the other sequences had executed which is not what the Excel Input box does. Changing Modal to false didn't fix it either.
Any help much appreciated. Thanks

 

[JohnGow383]

I currently have an input box showing when a certain set of conditions are met when first opening my spreadsheet. It asks to enter the number of miles. I input and press enter, the data is transferred which then triggers a set of other actions. You can also click Okay and same is done. Works fine.
However, I was wanting to spruce it up a bit with a simple Userform (Userform3) to mimic this action however it's behaving strangley. I have the same enter and cancel buttons. It works fine when clicking Okay, it transfers the data from the Textbox and then unloads the userform. I have tried Key up and key down Enter and others to try and get the number to transfer when pressing enter. Eventually I got it to work somehwhat by having the same code for commandbutton enter but here's the kicker. Works as expected when pressing enter but if clicking the command button (Okay) it crashes or gives a runtime error. I know this is probably a very simple fix but really struggling. I have a test button in worksheets where the debugger will tell me where the error is. It's only a userform3.show command. Here is the code for the transfer data button on the useform

Private Sub CommandButton1_Click()
ThisWorkbook.Worksheets("NOON Figs").Range("I5") = TextBox1.Text
Unload UserForm3
End Sub

Private Sub CommandButton1_Enter() 'Strangley, I needed both click and enter enabled like this to be able to enter miles and press enter for it to transfer and unload textbox
ThisWorkbook.Worksheets("NOON Figs").Range("I5") = TextBox1.Text
Unload UserForm3
End Sub

Here is the code for the Textbox

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'Ensures a valid numer is entered
With UserForm3.TextBox1
     If IsNumeric(.Text) And TextBox1.Value > 0 And Texbox1.Value < 600 Then
        .BackColor = &HC0FFFF 'when number is entered correctly changes to pale yellow
     Else
        MsgBox ("Not a valid entry. Check correct milage and ensure to add numbers only"), vbCritical
        .BackColor = vbYellow 'error will change to dark yellow
        Cancel = True
    End If
End With
End Sub

I think I'm missing something extrememly obvious but can't fathom it.
Also, when the userform did work it would not unload until the other sequences had executed which is not what the Excel Input box does. Changing Modal to false didn't fix it either.
Any help much appreciated. Thanks

Okay I found a fix. I changed the exit code to Userform3.hide and both actions work now. However, it doesn't hide immediatly like the Inputbox behaviour. It only hides are a sequence of other macros further down in the chain are fully executed which I can't figure out why.