Excel VBA实现：文本框查找联系人并登记至指定工作表

Hey there! I've got you covered with a practical VBA implementation for your contact lookup button. Let's break this down into easy-to-follow steps:

Step 1: Add the "Register" Button #

• Go to the Developer tab (enable it if you don't see it via File > Options > Customize Ribbon)
• Click Insert > Choose Button (Form Control)
• Draw the button on your worksheet, then assign a new macro (name it something like RegisterContactLookup) when prompted

Step 2: Paste the VBA Code #

Open the VBA editor (press Alt + F11), find your macro in the Project Explorer, and replace the default code with this:

Sub RegisterContactLookup()
    Dim inputName As String
    Dim contactSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim searchRange As Range
    Dim matchResult As Range
    
    ' Set your worksheet names - adjust these to match your actual sheet names
    Set contactSheet = ThisWorkbook.Worksheets("Contacts") ' Sheet with your contact table
    Set targetSheet = ThisWorkbook.Worksheets("Output") ' Sheet where you want to paste the match
    
    ' Get the name from your TextBox (replace "TextBox1" with your actual TextBox name)
    inputName = ThisWorkbook.ActiveSheet.TextBox1.Value
    
    ' Handle empty input
    If Trim(inputName) = "" Then
        MsgBox "Please enter a contact name first!", vbExclamation
        Exit Sub
    End If
    
    ' Define the search range (first column of your contact table, adjust row range as needed)
    Set searchRange = contactSheet.Range("A2:A" & contactSheet.Cells(contactSheet.Rows.Count, "A").End(xlUp).Row)
    
    ' Look for the input name (adjust LookAt for exact vs partial match)
    ' Use xlWhole for EXACT name match, xlPart for partial matches (e.g., "John" finds "John Doe")
    Set matchResult = searchRange.Find(What:=inputName, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    
    ' Check if a match was found
    If Not matchResult Is Nothing Then
        ' Paste the matched name to your target cell (replace "B2" with your specified cell)
        targetSheet.Range("B2").Value = matchResult.Value
        MsgBox "Contact found and registered successfully!", vbInformation
    Else
        MsgBox "No matching contact found for """ & inputName & """", vbCritical
    End If
End Sub

Key Customizations You Need to Make #

• Replace "Contacts" with the actual name of your sheet containing the contact table
• Replace "Output" with the sheet name where you want to output the matched name
• Replace "TextBox1" with the name of your input TextBox (check the name via the Properties window in Excel)
• Adjust the target cell ("B2") to your desired output location
• Switch LookAt:=xlWhole to LookAt:=xlPart if you want to allow partial name matches (e.g., typing "Smith" finds "Jane Smith")

How It Works #

1. The macro first grabs the name from your TextBox and checks if it's empty
2. It searches the first column of your contact table (starting from row 2 to skip headers)
3. If a match is found, it copies the name to your specified target cell and shows a success message
4. If no match exists, it alerts you with an error message

This should cover all the core functionality you need. Let me know if you run into any issues adapting it to your specific workbook setup!

内容的提问来源于stack exchange，提问作者Xavier93