Posts by TOMTRUCK

Hi, me again.

Sorry, managed to fix it, but I could do with some assistance on something else if possible.

The fix - I had a set of quotation marks missing from the named range reference, stopping the lookup, it happened when I had to add the INDIRECT reference in to the OFFSET named range.

The named range also is used with a listbox that displays a list of the contracts that match part of the input textbox. THis works great and populates the listbox properly etc, however, I want to be able to click the correct record in the listbox and this be the selected record that I want to populate TextBox1 in another userform with. So, question is, how do I take the selected item in a listbox and extract the first column data in this to populate a Textbox in another userform?

I have found a couple of solutions to this online but none work.

Hi, I am hoping I can get some help. I am completely self taught from books and the good people at Google, so if some of my terminology/code looks 'newbie' like, there's a reason.

I have been writing a vba program in excel for a contract management system. I have a quite detailed userform that populates and then returns (upon searching In Column A for a textbox value) the rest of the textbox/combobox data from the excel list.

It has worked perfectly over the past with few days over multiple tests of example data, now I have completed the program, and using the proper data, it is now returning the seemingly well known 'Unable to get the VLookup Property of the worksheet functionclass'

There are 23 textboxes that the Vlookup is populating, and I am using a named range "Lookup" which I learned from a Youtube clip! Get me!

I have cut the first bit of the sub below if you can help me find the error please. The 'Red' text is the yellow highlight in the debug.

Honestly, it has been working fine all week with example data.

Private Sub TextBox1_AfterUpdate()


Application.ScreenUpdating = False


If WorksheetFunction.CountIf(Sheet2.Range("A:A"), Me.TextBox1.Value) = 0 Then
MsgBox "This is an incorrect contract number"
Me.TextBox1.Value = ""
Exit Sub
End If
With Me
.TextBox2 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), Sheet2.Range("Lookup"), 2, 0)
.TextBox4 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), Sheet2.Range("Lookup"), 3, 0)
.TextBox5 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), Sheet2.Range("Lookup"), 4, 0)
.TextBox6 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), Sheet2.Range("Lookup"), 5, 0)

Thank you in advance for your assistance.

Re: Search function to populate a userform

Hi,

Thank you for above but it doesn't seem to work.

The issue I have is that the worksheet is going to contain 40+ columns of data and 600+ rows of data and counting. What I need to do is input some search criteria into 1 or more of the boxes i have created and it then display the results in the list box. In theory, if I only put 1 piece of data in to any of the boxes, then the results will be multiple. If i enter 2 or 3 pieces of data, the results will be narrowed down and display maybe 1 or 2 rows.

Ultimate aim: - This application is to be used to control stock and sales. I have already built an input userform to put data into the excel sheet (works fine), but occasionally, I will need to go back in to that data and change a piece, I.e. in this example, if we then sell that piece of stock to another customer, I need to be able to change the customer. The userform I have works perfectly to add the data to a new row in the worksheet, however I need a way of using a 'search' function somehow to select a row of data from the sheet, load that row into an identical userform to the first one, change the data as required and then 'save' the new information over the old row. But I am struggling.

I do not need all 40 rows to be searched, only maybe 3 or 4 criteria I have put in the existing search userform, this should be sufficient to narrow down the results.

I can get the boxes in the Search userform to display the data, no problem. What i am struggling with is the code for behind the search button that then looks at the data inputted into the combo/textboxes and finds them matching rows in the worksheet. Step 1.

Step 2, clicking on one of the results in the Listbox and that rows data transferring to the complete Userform for editing.

Step 3, changing the data and the userform 'amend' button replacing that rows data with the new data, not creating a new row.

I apologise for the newbie-ness but I am learning on the go here. Any help is massively appreciated.

Kind Regards

Tom

Re: Search function to populate a userform

Hi,

I have created the form and search terms for the function, but i just need assistance if possible to get it all to work.

Please see attached file, this is just a small version of what I am creating. I have used four columns of data and one UserForm. If the user selects/enters one or more pieces of data into the userform and clicks 'search' I would like to corresponding results displayed in the listbox on the form, then I am looking to hit an 'amend' button which is not there at the moment for the selected record from the list box to transfer data from that selected row into a seperate Userform.

My apologies for asking but I am still learning as I go along.

Kind regards

Tom

Re: Combobox selection populates textboxes

Works perfectly, thank you sktneer

Tom.

Hi all,

If I have a userform with three text boxes and one combo box. (like attached)

I am looking to be able to select an option in the combobox and the textboxes auto-fill with the data from the same row as the selected option in the combo box.

The code I have so far is this

Private Sub CommandButton1_Click()
'Copy input values to sheet.
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(lRow, 1) = TextBox1.Value
.Cells(lRow, 2) = TextBox2.Value
.Cells(lRow, 3) = TextBox3.Value
.Cells(lRow, 4) = ComboBox1.Value
End With
'Clear input controls.
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
ComboBox1.Value = ""
End Sub

Private Sub ComboBox1_DropButtonClick()
ComboBox1.List = Worksheets("Data").Range("D2:D10").Value
End Sub

So if the selection in the combobox relates to the cell D2 for example, then textbox1 would show data from cell A2, TextBox2 fromB2 on so on.

Thank you in advance.

Tom

Re: Search function to populate a userform

Hi royUK.

Thank you for this. Your solution is sort of what I am looking for. I need to be able to type in part or full text of a certain value and it display results, like yours does. However I then need to be able to select one of them results and the data be shown in the original UserForm for editing.

So f I have a small userform with 3 or 4 textboxes/comboboxes in, the user then part or full fills any or all of the boxes, clicks on a search button and the result(s) are displayed in a list box, similar to yours. Then, the on record (row) that the user wishes to update, can be clicked on in the list box and then the original full userform displays with the results from that row in. The user then needs to be able to change one or a few text/comboboxes and click 'amend' and it will update that row.

I appreciate any help you can give.

Tom

Re: Search function to populate a userform

Hi,

Thank you for assisting. I have done an example one as the one I am working on is big and messy with data but the function is similar.

I have created a 3-column database and 3 input userform. What I would like is a search box to come up where the user can type in part or all of the contents of any cell and the results be displayed. Once the correct row is identified, the data from that row is shown in a userform similar to the first one. The data can then be changed and saved on top of the rows data that is already there without creating a new row.

The end worksheet will have approx 40-50 columns of data but I would like just maybe 3 or 4 to be searchable.

Your help is much appreciated.

Thanks

Tom

Hi all,

Newbie here!

I am working on something at the moment and I could do with some assistance. My knowledge of Excel and VB is self-taught so limited. I have an excel database running with multiple columns. I have created a UserForm in VB that when I fill it in and hit a command button, then populates the correct cells on the next line in the Excel database. All-good.

However, what I need to do is use a similar userform that I can put in an ability to search the rows in the excel sheet, once a term is matched up, say with the data in column A, the rest of the textbox's and combobox's etc in the form are filled with the data from that row. Should then a piece of that data change, I can alter the correct textbox/combobox in the userform and hit the save command button and it will accurately change the correct row/cell in the excel sheet.

So to summarize, I need to find a way of searching a record (row) then a command to open the UserForm with the correct data in from that search result, alter, and resave to overwrite that rows data.

Thank you in advance.

Tom