Alternative way to the form command for searching and adding data in worksheet

NotMe2

New Member
Joined
Jul 16, 2023
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
Trying to emulate what the form command does from the quick access toolbar.
I don't know if it is possible to put this form command on a worksheet with a button, couldn't find anything about this and I suppose it is a Excel functionality which you can't generate on a worksheet.
So I found the next best thing which is making a userform to search and add data, only it only searchs for 1 criteria and I want to search for 2 criteria, email and last name.
The problem is that it doesn't work even for 1 criteria, it is probably that I have an automated insert for an ID number in Column A (this is done with another userform)
The input on my worksheet starts A5, so I tried to change the i =2 to i = 5 and some other fiddling, but to no avail. My knowledge of VBA is very poor, so I was hoping for someone to help solving this problem.
The code below is changed because the original code started from the first textbox and not the second textbox on the userform

.
VBA Code:
Private Sub cmdbtnSearch_Click()
Dim LastName As String
LastName = Trim(tbLast_name.Text)
LastRow = Worksheets("Guests").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LastRow
If Worksheets("Guests").Cells(i, 2).Value = LastName Then
tbFirst_name.Text = Worksheets("Guests").Cells(i, 1).Value
tbDob.Text = Worksheets("Guests").Cells(i, 3).Value
tbNationality.Text = Worksheets("Guests").Cells(i, 4).Value
tbPassport.Text = Worksheets("Guests").Cells(i, 5).Value
optMale.Text = Worksheets("Guests").Cells(i, 6).Value
tbEmail.Text = Worksheets("Guests").Cells(i, 7).Value
tbPhone.Text = Worksheets("Guests").Cells(i, 8).Value
tbEmergency.Text = Worksheets("Guests").Cells(i, 9).Value
tbAllergies.Text = Worksheets("Guests").Cells(i, 10).Value
tbRemarks.Text = Worksheets("Guests").Cells(i, 11).Value
tbBook.Text = Worksheets("Guests").Cells(i, 12).Value
End If
Next
 

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.
Trying to emulate what the form command does from the quick access toolbar.
I don't know if it is possible to put this form command on a worksheet with a button, couldn't find anything about this and I suppose it is a Excel functionality which you can't generate on a worksheet.
So I found the next best thing which is making a userform to search and add data, only it only searchs for 1 criteria and I want to search for 2 criteria, email and last name.
The problem is that it doesn't work even for 1 criteria, it is probably that I have an automated insert for an ID number in Column A (this is done with another userform)
The input on my worksheet starts A5, so I tried to change the i =2 to i = 5 and some other fiddling, but to no avail. My knowledge of VBA is very poor, so I was hoping for someone to help solving this problem.
The code below is changed because the original code started from the first textbox and not the second textbox on the userform

.
VBA Code:
Private Sub cmdbtnSearch_Click()
Dim LastName As String
LastName = Trim(tbLast_name.Text)
LastRow = Worksheets("Guests").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LastRow
If Worksheets("Guests").Cells(i, 2).Value = LastName Then
tbFirst_name.Text = Worksheets("Guests").Cells(i, 1).Value
tbDob.Text = Worksheets("Guests").Cells(i, 3).Value
tbNationality.Text = Worksheets("Guests").Cells(i, 4).Value
tbPassport.Text = Worksheets("Guests").Cells(i, 5).Value
optMale.Text = Worksheets("Guests").Cells(i, 6).Value
tbEmail.Text = Worksheets("Guests").Cells(i, 7).Value
tbPhone.Text = Worksheets("Guests").Cells(i, 8).Value
tbEmergency.Text = Worksheets("Guests").Cells(i, 9).Value
tbAllergies.Text = Worksheets("Guests").Cells(i, 10).Value
tbRemarks.Text = Worksheets("Guests").Cells(i, 11).Value
tbBook.Text = Worksheets("Guests").Cells(i, 12).Value
End If
Next
Because I am a newbie, I didn't think about the possibility of just recording a macro to get the form command and assign it to a button. Very bad beginner's mistake.
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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