Open user form upon enter key press

[ipbr21054]

Hi,
On my worksheet i have in column A customers names & in column P of that row is the receipt number for that customer.
What i would like to happen is when a certain customers name in cell A is selected when i press the enter key have it open my userform called Database & if possible to then take it one step further upon opening the userform have that customers records loaded.

I can assist further with info etc should you need any other input.

Thanks

 

[Domenic]

It looks like the Worksheet object doesn't contain a KeyPress event handler. Have you considered using either the BeforeDoubleClick event or SelectionChange event handlers? Your userform could be shown when the target cells are either doubled clicked or selected, depending on your preference.

 

[ipbr21054]

Hi,
No i havent,could you advise regarding double click as i think that would suit this best.

That would open the form but what about the file for the customer that i double clicked on

Thanks

 

[ipbr21054]

Hi,
This works to open the userform.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)Database.Show
End Sub

 

[Domenic]

You should set Cancel to True so that you don't enter the cell in edit mode after unloading your form...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Database.Show
End Sub

To restrict it to Column A, try the following instead...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Me.Columns("A"), Target) Is Nothing Then Exit Sub
    Cancel = True
    Database.Show
End Sub

 

[ipbr21054]

Hi,
The code to restrict it to column A works great.

Do you know how i can then pull up that customers file when it opens.

Thanks

 

[ipbr21054]

Hi,
When i am on the userform i can click a button and it will look at the receipt/invoice number then show me the pdf for that customer.
The code is shown below.
Im not sure if the bones can be picked out of that if it helps so when i double click on a customer his record in the database is displayed "not to show me the pdf this time"

Private Sub OpenInvoice_Click()

    Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\"
    
    If txtInvoiceNumber = "N/A" Or Len(txtInvoiceNumber) = 0 Then
                MsgBox "Invoice N/A For This Customer", vbExclamation, "N/A INVOICE NOTICE"
    Else
        If Len(Dir(FILE_PATH & txtInvoiceNumber.Value & ".pdf")) = 0 Then
            If MsgBox("Would You Like To Open The Folder ?", vbCritical + vbYesNo, "Warning Invoice is Missing.") = vbYes Then
                CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\")
            End If
        Else
            CreateObject("Shell.Application").Open (FILE_PATH & txtInvoiceNumber.Value & ".pdf")
        End If
    End If
End Sub

 

[Domenic]

I would suggest that you start a new thread, and provide all of the relevant details so that someone can provide you with a possible solution.

 

[ipbr21054]

But it’s civered/mentioned in my first post

 

[Domenic]

Since you haven't provided any details, I'll assume the following...

1) Column A contains the customer name, and Column B and Column C contain the corresponding data for the customer.

2) The data for the customer, located in Column A, B, and C, is to be loaded onto 3 textboxes on the userform.

[UserForm Code Module]

Option Explicit

Private m_ws As Worksheet
Private m_rw As Long

Sub LoadData(ByVal ws As Worksheet, ByVal rw As Long)
    Set m_ws = ws
    m_rw = rw
    Me.TextBox1.Value = m_ws.Range("A" & m_rw).Value
    Me.TextBox2.Value = m_ws.Range("B" & m_rw).Value
    Me.TextBox3.Value = m_ws.Range("C" & m_rw).Value
    With Me.TextBox1
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Value)
    End With
    Me.Show
End Sub

[Sheet Code Module]

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Range("A2", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then Exit Sub
    Cancel = True
    Database.LoadData Me, Target.Row
End Sub

You will need to adapt the code to suit your requirements.