Using UserForm and Textbox Name as arguments for a function

Question

I have a userform with many textboxes (describe paramenter of tests). I want to use function in order to check each textbox if it's value is in specific range. Allowed values ranges are appearing in excel sheet "T_list) - for each parameter there is different range.If the value of textbox is in the range, the function will return "true", otherwise it will return "false".

Here is how i call to this function:

g = CheckGreen(Test_Procedure, CWS1, 1)
msgbox(g)

Test_Procedure- name of userform, CWS1 - name of specific textbox, 1 - the first range of value (in the table in the eacel sheet T_list)

Here is the code of the function which doesn't work, since the txtbox argument is get the value of the cell and not the taxtbox name. what i'm doing wrong?

Public Function CheckGreen(formName As UserForm, txtbox As MSForms.TextBox, i As Integer) As Boolean
  If formName.txtbox.Value >= Sheets("T_list").Range("T_Start").Offset(i, 0).Value And formName.txtbox.Value <= Sheets("T_list").Range("T_Start").Offset(i, 1).Value Then
formName.txtbox.BackColor = &HFF00& ' Green
Sheets("Test_Data").Range("D_Start").Offset(T_R, 8).Value = formName.txtbox.Value
CheckGreen = True
Else
formName.txtbox.BackColor = &H3535FD ' Red
Sheets("Test_Data").Range("D_Start").Offset(T_R, 8).Value = formName.txtbox.Value
CheckGreen = False
End If
End Function

*****edited according to @Banana comment:

this is my function code using the string option (per @Banana comment):

Public Function CheckGreen1(formName As UserForm, txtbox As String, i As Integer) As Boolean
If formName.Controls(txtbox).Value >= Sheets("T_list").Range("T_Start").Offset(i, 0).Value And formName.Controls(txtbox).Value <= Sheets("T_list").Range("T_Start").Offset(i, 1).Value Then
formName.Controls(txtbox).BackColor = &HFF00& ' Green
Sheets("Test_Data").Range("D_Start").Offset(T_R, 8).Value = formName.Controls(txtbox).Value
CheckGreen1 = True
Else
formName.Controls(txtbox).BackColor = &H3535FD ' Red
Sheets("Test_Data").Range("D_Start").Offset(T_R, 8).Value = formName.Controls(txtbox).Value
CheckGreen1 = False
End If

End Function

Here is how i call the function:

g = CheckGreen(CWS1.Value, 1, CWS1)

This option is still doesn't work sinse the value now is string, and the IF equation doesn't recognize the value as number, and go alwayse to 'False' (even if the value is in the allowed range).

Please your assistance, thanks

Possible duplicate of Referencing Excel Userform Control Name Value From String (VBA) — Banana
– Banana, Commented Jun 19, 2018 at 7:46
@Banana - it's not the same topic, i need to know how to add textbox name as argument in function. — Shira L.
– Shira L., Commented Jun 19, 2018 at 8:30
a name is just a string, and you pass it as any other string: "CWS1", and you use it as described in the link from my first comment. — Banana
– Banana, Commented Jun 19, 2018 at 8:38
i've tried to put string inside the function argument, it's still doesn't work. can you help me with the syntax? — Shira L.
– Shira L., Commented Jun 19, 2018 at 8:42
this is my code:Function CheckGreen(Tvalue As Double, i As Integer, cell As String) As Boolean If Tvalue >= Sheets("T_list").Range("T_Start").Offset(i, 0).Value And Tvalue <= Sheets("T_list").Range("T_Start").Offset(i, 1).Value Then Test_Procedure.cell.BackColor = &HFF00& ' Green Sheets("Test_Data").Range("D_Start").Offset(T_R, 8).Value = Tvalue CheckGreen = True Else Test_Procedure.cell.BackColor = &H3535FD ' Red Sheets("Test_Data").Range("D_Start").Offset(T_R, 8).Value = Tvalue CheckGreen = False End If End Function — Shira L.
– Shira L., Commented Jun 19, 2018 at 8:43

Shira L. · Accepted Answer · 2018-06-19 19:45:26Z

0

The code is work by putting the string value in the function CDbl() which convert string to double.

answered Jun 19, 2018 at 19:45

Shira L.

811 silver badge14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using UserForm and Textbox Name as arguments for a function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related