Index Match into code when name is selected from combo box.

**nancytaylor** · 10-27-2013, 10:40 PM

I am currently using the below formula to index and match.

=INDEX(Individualnames!$D$3:$D$24991,MATCH(Collectionsheet!D45,Individualnames!$E$3:$E$24991,0))

I would like to put this into code where it will populate a text box (gradyear) when a name is selected from a combo box (studentname).

Thanks for any help.

**Pauleyb** · 10-28-2013, 03:41 PM

Okay, this one has been quiet for a while.
Are you asking how to include an Excel function within VBA? If so, this link may help (as well as the many that appear for 'excel formulas in vba'):
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Essentially, use Application.Worksheetfunction.your_formula_here

Or, are you asking for the entire solution? If so, while your request is possible, it has a caveat and I would advise other solutions. Essentially, there is no easy way to change the text in a text box. And by text box, I assume you mean a true text box from the Insert->Text Box ribbon, and not a cell with text in it. As you have determined VBA is needed. One of the concerns is that it would need to be a macro that resides in the Worksheet_update event handler. That means the macro will run every time a cell is updated. Usually not a big deal, but it may be overkill.

Also, is your combo-box an activex or a form control? If so, then you should be able to add the code within the control's event handling routine. Not sure of your coding experience, so not sure where you are stuck.

Would in not be possible to just create a cell which looks like a text box? Then the cell would contain your formula and you are done.
Or, if there is some shape or look of the text box that cell formatting can achieve, can you have the text box be semi-transparent or use 'no fill' and then place the shape over a cell that has your function in it?
Or, if you want it to be movable, could you use a picture of a cell in another location that holds your text?

**nancytaylor** · 10-28-2013, 03:57 PM

Thank you for your reply. The problem was solved. I pasted the code below. I created userform with text boxs and combo boxes. I was able to do most of the coding but got really stumped on this one.

Dim emptyRow As Long
Dim xlCalc As XlCalculation
On Error GoTo ExitPoint
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

'Make Sheet1 Active

Sheets(1).Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("d:d")) + 2

'Export Data to worksheet
Cells(emptyRow, 1).Value = Datebox.Value
Cells(emptyRow, 3).Value = LFETextBox.Value
Cells(emptyRow, 2).Value = BoxTopTextBox.Value
Cells(emptyRow, 4).Value = Studentname.Value
Cells(emptyRow, "E").Value = Class.Value

'Empty Studentname
Studentname.Value = ""

'Empty BoxTopTextbox
BoxTopTextBox.Value = ""

'Empty PhoneTextBox
LFETextBox.Value = ""
Class.Value = ""
ExitPoint:
With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub