Run vba when pressing Enter & userform open

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,048
Office Version
  1. 2007
Platform
  1. Windows
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

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,048
Office Version
  1. 2007
Platform
  1. Windows
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.

Rich (BB code):
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
 

Forum statistics

Threads
1,136,909
Messages
5,678,514
Members
419,768
Latest member
eguechi09x

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top