• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup with Two Userform Inputs

rhibrown

New Member
Hi there,

I am wondering if it is possible to be able to enter two fields of information into a userform (through combo boxes), and then the respective value to be found.

Would is be possible to then edit the respective value that has been found, and then to save over the information?

So the first combo box would have the Item # to select from, and the second combo box has the other headers.
So I could choose ICT.1516.001 and Cost Centre, and it would bring up 509.
I then want to change it to 506, and overwrite 509 in the spreadsheet.

Is this possible?
I've attached only a small part of my file.

Thanks in advance.
 

Attachments

Belleke

Active Member
Hello,
Not exactly the same but the example does what you asked for.
I use a table and a defined name range. See formulas -> name manager.
To edit,in the listbox you have to select the Item # that you want to change.
You can search for a item #. In the searchbox enter a item# or part of a tem#, for example 010
The items that do not meet the search criteria disappear from the list.
See attached
 

Attachments

rhibrown

New Member
Thank you that's great!

Is there a way I can insert a comment into the spreadsheet for fields that have been edited? I'd need an extra text box to write the comment, just wondering if that would be possible?
 

Belleke

Active Member
Hi,
See attached.
You kan add a comment with de edit button.
Comment is added in column A, but you can easy change it.
in de Edit Record button code change the 1 with the column number you want.
ws.Cells(fnd.Row, 1).AddComment T_18.Value
 

Attachments

rhibrown

New Member
Thanks again! If I want to add a comment to a particular column which may be different every time - should I add a combo box with a list of all the column names and once that is selected, the comment will automatically go against that particular column?
 

rhibrown

New Member
Glad to help:)
I'm creating a second form to do a similar thing as above, except I want to populate the form with information from columns T to AE.
I can see it is populating the Item# box correctly, just not the other boxes with the information from column T to AE.
This is the code I have, but it comes up with 'Could not get the column property' referring to the red line below..

Code:
Private Sub LB_00_Click()
T_00.Value = LB_00.Value
For i = 19 To 30
    Me("T_" & Format(i, "00")) = LB_00.Column(i)
Next
Cmd_01.Enabled = True
T_18.Locked = False
End Sub
What do I need to do to fix?
 
Last edited by a moderator:

Belleke

Active Member
You get this error because you don't have 31 Columns in your listbox. (listbox start with column 0)
Name your textboxes as I do and start with T_00 OR T_01
assuming we have 10 textboxes then you use For i = 0 To 9 or For 1 T0 10.
To start in Column T
For the Add New Record button
Change .DataBodyRange.Cells(.ListRows.Count, 1) to .DataBodyRange.Cells(.ListRows.Count, 20)
For the Edit Record button
Change ws.Cells(fnd.Row, 1) to ws.Cells(fnd.Row, 20)
 

Belleke

Active Member
Hi,
You did it pretty well. It was partly my fault, now that I've looked at your file it's clearer.
First remark, avoid using AddItem,it is a slow way to fill combo-en listboxes.
Wat did you do wrong.
To fill the listbox you used
Code:
With LB_00
    .List = [ITs].Value
    .ColumnCount = [data_tbl3].CurrentRegion.Columns.Count
End With
ITs only has one column, it is normal that you get the error.
This is wat you should use.
Code:
With LB_00
    .List = [data_tbl3].Value
    .ColumnCount = [data_tbl3].CurrentRegion.Columns.Count
    .ColumnWidths = "60;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0"
End With
I renamed the textboxes, my fault,I thought your table started in column T, but is is column A.
See the code LB_00 click.
Code:
T_00.Value = LB_00.Column(0)
For i = 19 To 30
    Me("T_" & Format(i, "00")) = LB_00.Column(i)
Next
You can't do that, you used
Code:
ws.Cells(fnd.Row, fdd.Column).Resize(, 17).Value
If you select, let's say Jun-20 then Excel starts in column AE and fills AE and the next 16 columns.
When you don't select a month in T13 (if you don't need to add a comment) Excel does not know what to do.
Use
Code:
ws.Cells(fnd.Row, 20).Resize(, 12)
However this is correct for the comment
Code:
If T_14.Value <> "" Then ws.Cells(fnd.Row, fdd.Column).AddComment T_14.Value
And don't forget to change Resize
You used Resize(, 17).Value, there are only 12 textboxes, it should be Resize(, 12).Value
 

Attachments

rhibrown

New Member
Hi,
You did it pretty well. It was partly my fault, now that I've looked at your file it's clearer.
First remark, avoid using AddItem,it is a slow way to fill combo-en listboxes.
Wat did you do wrong.
To fill the listbox you used
Code:
With LB_00
    .List = [ITs].Value
    .ColumnCount = [data_tbl3].CurrentRegion.Columns.Count
End With
ITs only has one column, it is normal that you get the error.
This is wat you should use.
Code:
With LB_00
    .List = [data_tbl3].Value
    .ColumnCount = [data_tbl3].CurrentRegion.Columns.Count
    .ColumnWidths = "60;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0"
End With
I renamed the textboxes, my fault,I thought your table started in column T, but is is column A.
See the code LB_00 click.
Code:
T_00.Value = LB_00.Column(0)
For i = 19 To 30
    Me("T_" & Format(i, "00")) = LB_00.Column(i)
Next
You can't do that, you used
Code:
ws.Cells(fnd.Row, fdd.Column).Resize(, 17).Value
If you select, let's say Jun-20 then Excel starts in column AE and fills AE and the next 16 columns.
When you don't select a month in T13 (if you don't need to add a comment) Excel does not know what to do.
Use
Code:
ws.Cells(fnd.Row, 20).Resize(, 12)
However this is correct for the comment
Code:
If T_14.Value <> "" Then ws.Cells(fnd.Row, fdd.Column).AddComment T_14.Value
And don't forget to change Resize
You used Resize(, 17).Value, there are only 12 textboxes, it should be Resize(, 12).Value
Great! Thank you so much for explaining everything!!
 
Top