Pull Vlookup from excel sheet and use the lookup value as userform textbox value

One easy option is to copy the result from the cell that has this formula into the textbox.

To use it in VBA code you can put the formula inside a call to Evaluate; you will need to double all the quotes.

A user can type into a textbox. Is the user allowed to change this value?

Hi 6StringJazzer,


On the userform there are 22 option buttons that correlate to the product flavors in sheet "Data," column 1. As well there is an input textbox where the user will input the blend score (correlating to sheet "Data," column 2).

After the user selects the option button and blend score I want it to essentially lookup the textbox and option button and perform the calculation included in the vlookup then display the result in another textbox.

I had originally set up a search row with

Private Sub CommandButton3_Click()
Dim i As Long
If OptBCan.Value = True Then
 With Worksheets("CAN")
    For i = 2 To 10000
      If .Cells(i, 22).Value = TBoxBlend.Text Then TBoxMic.Value = .Cells(i, 26).Value: Exit Sub
    Next
  End With
  TBoxMic.Value = "N/A"
  End If
If OptBBot.Value = True Then
 With Worksheets("BOT")
    For i = 2 To 10000
      If .Cells(i, 17).Value = TBoxBlend.Text Then TBoxMic.Value = .Cells(i, 22).Value: Exit Sub
    Next
  End With
  TBoxMic.Value = "N/A"
  End If
End Sub

In this scenario it uses the excel vlookup after the user submits the data; however, this will not work because I need the value prior to the user submitting the data.

My new code will be in this command button; however, what is the syntax to do this?
This is what it should do:

=IF(XOR(13>=TextBox.Value,TextBox.Value=""),(($TextBox.Value-(VLOOKUP("Selected Option Button",DATA!$D$1:$E$22,2,0)))*100)*0.02,IF(TextBox.Value>=14,(((($TextBox.Value-(VLOOKUP("Selected Option Button",DATA!$D$1:$E$22,2,0))))*10)*0.02),""))

How can I have it determine the selected option button

I have declared/grouped all pertinent 22 option buttons in userform_initialize() as

OptB1.Groupname = "Product"
OptB2.Groupname = "Product"
...
OptB22.Groupname = "Product"

I added the module code and it looks like this now

   Dim c As Control
   
   For Each c In Controls
      If TypeName(c) = "OptionButton" Then
         If c.GroupName = Product Then
            If c.Value Then
               SelectedOptionButton = c.Caption
               Exit Function
            End If
         End If
      End If
      
   
   Next c

I placed the evaluate code in the textbox_change() where the calculated value should be displayed

the code is:

Private Sub TBoxMic_Change()
 Const GroupName = Product

   Evaluate ("IF(XOR(13>=" & TBoxBlend.Value & "," & TBoxBlend.Value & "="""")," & "((" & TBoxBlend.Value & "-(VLOOKUP(" & SelectedOptionButton(Product) & ",DATA!$D$1:$E$22,2,0)))*100)*0.02," & "IF(" & TBoxBlend.Product & ">=14," & "((((" & TBoxBlend.Value & "-(VLOOKUP(" & SelectedOptionButton(Product) & ",DATA!$D$1:$E$22,2,0))))*10)*0.02)," & """""" & ")")
   
End Sub

The only issue is that it is not displaying the value in TBoxMic; I do have a command button I could place this code in but it seems it is missing some code to add the resulting value to TBoxMic


Note: In the module code I added the word "Option" to change the code

    Dim c As Control
   
   For Each c In Controls
      If TypeName(c) = "OptionButton" Then
         If c.GroupName = GroupName Then
            If c.Value Then
               SelectedButton = c.Caption
               Exit Function
            End If
         End If
      End If
      
   
   Next c

to

   Dim c As Control
   
   For Each c In Controls
      If TypeName(c) = "OptionButton" Then
         If c.GroupName = Product Then
            If c.Value Then
               SelectedOptionButton = c.Caption
               Exit Function
            End If
         End If
      End If
      
   
   Next c

I currently have the evaluate code in the SelectedOptionButton_Click()

and everything is compiling without a sub undefined error; however, as mentioned, I need the piece to place the value

Something like TBoxMic = Evaluate.Value??

Best Regards,

Jon

Here is your file with the code corrected to work. However, I don't know if the final answer being calculated by the formula is the one you want. You will have to test that. Hopefully the code will be clear now that it's all complete and in one place, but let me know if you have questions.

You only have one set of option buttons so you don't need to use the GroupName field. It can be left blank.

I corrected an error in the code that builds the formula; it was missing a parenthesis at the end.

When you use Evaluate, the VLOOKUP is expecting a number. To use strings, you must put the value in value in quotes. I have completely overhauled the code to solve this problem. The solution involves coding the logic in VBA instead of using Evaluate on a worksheet formula, which I probably should have done in the first place.

Option Explicit

Private Sub CommandButton3_Click()
   
   Const GroupName = ""
   
   Dim OBcaption As String
   Dim Expression As String
   
   OBcaption = SelectedOptionButton(GroupName)
   
   If TBoxBlend = "" Then
      MsgBox "Set a test value first"
      
   ElseIf OBcaption = "" Then
      MsgBox "Select an option button first"
      
   Else

      If (13 >= TBoxBlend.Value Or TBoxBlend.Value = "") Then
         TBoxMic = ((5 - Application.WorksheetFunction.VLookup(OBcaption, Worksheets("DATA").Range("$D$1:$E$22"), 2, False)) * 100) * 0.02
      ElseIf TBoxBlend.Value >= 14 Then
         TBoxMic = ((5 - Application.WorksheetFunction.VLookup(OBcaption, Worksheets("DATA").Range("$D$1:$E$22"), 2, False)) * 10) * 0.02
      Else
         TBoxMic = ""
      End If
   End If
   
End Sub
        
Private Function SelectedOptionButton(GroupName As String) As String
   
   Dim c As Control
   
   For Each c In Controls
      If TypeName(c) = "OptionButton" Then
         If c.GroupName = GroupName Then
            If c.Value Then
               SelectedOptionButton = c.Caption
               Exit Function
            End If
         End If
      End If
   
   Next c

End Function