Run vba when pressing Enter & userform open

[ipbr21054]

Evening,

Code in use is shown below.

I wish to run the code when the Enter button is pressed & ONLY if the userform called DatabaseUserForm is open to cover hitting Enter by mistake when your not using the userform etc.

Currently the code below worked fine & did what it was supposed to do when on a command button, "new row & value entered in cell A6"
I then added the text in Red which now when i press the Enter button i need to press it twice, then the userform closes but no new row was added & no value was entered into cell A6

Private Sub Workbook_Open()

Application.OnKey "{ENTER}"

Rows("6:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Range("A6:Q6").Borders.LineStyle = xlContinuous

Range("A6:Q6").Borders.Weight = xlThin

Range("A6:Q6").Interior.ColorIndex = 6

Range("$Q$6").Value = "'NO NOTES FOR THIS CUSTOMER"

Range("$Q$6").HorizontalAlignment = xlCenter

Sheets("DATABASE").Range("B6").Select



Cancel = 0

If TextBox1.Text = "" Then

    Cancel = 1

    MsgBox "YOU MUST ENTER A CUSTOMERS NAME", vbCritical, "DATABASE USER FORM NAME TRANSFER"

    TextBox1.SetFocus

 

End If



If Cancel = 1 Then

Exit Sub

End If



Dim i As Long

Dim x As Long

Dim ctrl As Control

Dim LastRow As Long

 

With ThisWorkbook.Worksheets("DATABASE")

    .Range("A6").Value = TextBox1.Text



End With



Unload DatabaseUserForm



End Sub

 

[ipbr21054]

So im kind of getting there but still not 100%

I have the code below which adds a new line & also enters the customers name in cell A6 BUT there is no 001, 002 etc etc after the name.

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
Rows("6:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A6:Q6").Borders.LineStyle = xlContinuous
Range("A6:Q6").Borders.Weight = xlThin
Range("A6:Q6").Interior.ColorIndex = 6
Range("$Q$6").Value = "'NO NOTES FOR THIS CUSTOMER"
Range("$Q$6").HorizontalAlignment = xlCenter
Sheets("DATABASE").Range("B6").Select

Cancel = 0
If TextBox1.Text = "" Then
    Cancel = 1
    MsgBox "YOU MUST ENTER A CUSTOMERS NAME", vbCritical, "DATABASE USER FORM NAME TRANSFER"
    TextBox1.SetFocus
    
End If

If Cancel = 1 Then
Exit Sub
End If

Dim i As Long
Dim x As Long
Dim ctrl As Control
Dim LastRow As Long
    
With ThisWorkbook.Worksheets("DATABASE")
    .Range("A6").Value = TextBox1.Text

End With

Unload DatabaseUserForm
End If
End Sub
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim fndRng As Range
    Dim findString As String
    Dim i As Integer
    Dim wsPostage As Worksheet
    
    findString = Me.TextBox1.Value
    If Len(findString) = 0 Then Exit Sub
    
    Set wsPostage = ThisWorkbook.Worksheets("DATABASE")
    i = 1
    Do
        Set fndRng = Nothing
        Set fndRng = wsPostage.Range("A:A").Find(What:=findString & Format(i, " 000"), _
                                                    LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, _
                                                    SearchDirection:=xlNext, MatchCase:=False)
        If Not fndRng Is Nothing Then
            i = i + 1
            Cancel = True
        End If
    Loop Until fndRng Is Nothing
    
    Me.TextBox1.Value = findString & Format(i, " 000")
    Cancel = False
    
End Sub