Userform VBA code using 2 criteria to populate 1 cell

[Pi_Lover]

Good afternoon,

I am attempting my first Userform. The purpose of the Userform is to enter in data to populate a cell with 2 different criteria. The first criteria will be column specific, while the second will be row specific, but the row will be determined by the data entered into a text box. Essentially, I want the row criteria to basically function like a Vlookup, or like a search function, to find the correct row, and then the 2nd text box will determine the column. Is this possible? See attachments below for reference.

 

[Max1616]

I'm not sure I fully understand what you are asking. But I have put together a mock up of a user form where you type in the Inst Tag #, and it looks up and populates that text boxes under the look up:

Look up table:

User form as I type:

User form once I finish typing a value that matches to the table (column A):

(note, the four text boxes are auto-populated, once the tag # is completely entered in)

Here's the VBA code to accomplish this:

Private Sub TextBox1_Change()
Dim Lookup As String
Dim LookupRow As Long
Dim StandClm  As Integer
Dim InstInClm As Integer
Dim VendInClm As Integer
Dim InstEnClm As Integer

On Error Resume Next
Lookup = Me.TextBox1.Value
LookupRow = Application.Match(Lookup, Range("A:A"), 0)

StandClm = 2
InstInClm = 3
VendInClm = 4
InstEnClm = 5

Me.TextBox2 = Cells(LookupRow, StandClm)
Me.TextBox3 = Cells(LookupRow, InstInClm)
Me.TextBox4 = Cells(LookupRow, VendInClm)
Me.TextBox5 = Cells(LookupRow, InstEnClm)

End Sub

 

[Max1616]

Alternatively, if you are looking to populate the Excel cells, based off the data you plug into the lower text boxes, this code will do that:

Private Sub CommandButton1_Click()

Dim Lookup As String
Dim LookupRow As Long
Dim StandClm  As Integer
Dim InstInClm As Integer
Dim VendInClm As Integer
Dim InstEnClm As Integer

On Error Resume Next
Lookup = Me.TextBox1.Value
LookupRow = Application.Match(Lookup, Range("A:A"), 0)

StandClm = 2
InstInClm = 3
VendInClm = 4
InstEnClm = 5

Cells(LookupRow, StandClm) = Me.TextBox2
Cells(LookupRow, InstInClm) = Me.TextBox3
Cells(LookupRow, VendInClm) = Me.TextBox4
Cells(LookupRow, InstEnClm) = Me.TextBox5

End Sub

Table before I run this user form:

User form before I run it:

Table after I run the user form:

 

[Pi_Lover]

This is great! Thank you, this is exactly what I am wanting to do. Much appreciation

 

[Pi_Lover]

@Max1616 This works perfectly, but I have run into an issue. Each column of data to be entered into the sheet may be performed at different dates and times. Meaning I may add progress of "1" for Stands today, then "1" Instrument installed the following day. When I do this, it clears the data entered in for the Stands on the worksheet. Is there a work around for this?

 

[Max1616]

@Pi_Lover , This would only update the cell(s) if there is data in the text boxes:

Private Sub CommandButton1_Click()

Dim Lookup As String
Dim LookupRow As Long
Dim StandClm  As Integer
Dim InstInClm As Integer
Dim VendInClm As Integer
Dim InstEnClm As Integer

On Error Resume Next
Lookup = Me.TextBox1.Value
LookupRow = Application.Match(Lookup, Range("A:A"), 0)

StandClm = 2
InstInClm = 3
VendInClm = 4
InstEnClm = 5

If Me.TextBox2 <> "" Then Cells(LookupRow, StandClm) = Me.TextBox2
If Me.TextBox3 <> "" Then Cells(LookupRow, InstInClm) = Me.TextBox3
If Me.TextBox4 <> "" Then Cells(LookupRow, VendInClm) = Me.TextBox4
If Me.TextBox5 <> "" Then Cells(LookupRow, InstEnClm) = Me.TextBox5

End Sub

 

[Pi_Lover]

Thank you again man! You have been extremely helpful