controlling cursor movement in excel form

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
Hi
I created a form to enter first and last name (please see the pic in the link below). I have 2 questions:


1- I click on the first text box and enter the first name then pressing Enter will move the cursor to next box then I enter the last name. Then I press Enter then the cursor will go to Enter button and stays there even after pressing Enter key. My question, can I move the cursor to the first box automatically after pressing Enter button?


2- I have search button, how you would write the code to search for the first and last name that user entered in these 2 text boxes in the excel sheet. If the name is found then highlight the row or both first and last name in the sheet


Thank you so much





https://drive.google.com/file/d/1SqNzbYhsPa6XTPqTJacQNhJxup18wvph/view?usp=sharing
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
It's hard to work with a picture. Could you upload a copy of your file?
 
Upvote 0
To move the cursor to the first textbox add this to the end of the enter_click event
Code:
Me.[COLOR=#ff0000]TextBox1[/COLOR].SetFocus
Change the name in red to the name of your first box.
As your image shows all the same name which one do you want highlighted on the search button?
 
Upvote 0
Replace the current macros with the following ones:
Code:
Private Sub enter_Click()
    Dim X As Long
    X = WorksheetFunction.CountA(Range("A:A")) + 1
    Cells(X, 1) = first.Value
    Cells(X, 2) = last.Value
    first.SetFocus
    With first
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
    With last
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
End Sub

Private Sub search_Click()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim Rng As Range
    For Each Rng In Sheets("Sheet1").Range("A1:A" & LastRow)
        If Rng = first.Text And Rng.Offset(0, 1) = last.Text Then
            Rng.Resize(, 2).Interior.ColorIndex = 3
        End If
    Next Rng
    Application.ScreenUpdating = True
End Sub

Private Sub UserForm_Initialize()
    first.Value = "first"
    last.Value = "second"
    With first
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
    With last
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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
Back
Top