Referring to control value on form using variable name for control

[CompleteNewb]

Using Excel 2007, but have same problem in 2003. And to be clear, this is
about actual user fomrs, not controls on a worksheet. I see a lot of stuff
on Google and in the groups about dealing with controls when they're
inserted into worksheets, but this is about those user forms you design in
the VBA editor.

I am trying to add a completely new aspect of pricing to a kind of
hurky-jerky-typical-started-out-small-then-kept-getting-added to excel
application hat I've been dropped in the middle of:

Looping through the list of controlnames listed in a worksheet (this is
going on behind the scenes while the form's showing and being interacted
with), If the left 6 characters of the control's name are "BrkQua", then it
is a textbox, and I need to take the value that is in that textbox (the one
on the form) and do a Vlookup function that performs price breaks based on
quantity (I got the Vlookup part from the ozgrid site, I believe).

So, for every control whose name starts with "BrkQua", I look up that
control's value in a specific range in a spreadsheet, and multiply that
value by what my Vlookup gets.

My problem so far is referring to the value of these controls. It seems
that when I have a control's name in a variable, I can't refer to it's
value. If my variable is ControlName, then this works:

If Left(ControlName,6) = "BrkQua" then
MsgBox ControlName, vbOKOnly,""
End If

This will give me a popup with the actual whole name of the control.
However, when I try
If Left(ControlName,6) = "BrkQua" then
MsgBox ControlName.value, vbOKOnly, ""

Gives me a syntax error, or I get other errors when I try
Controls(ControlName).value or other variations I've tried using parenthesis
and quotes and things. I can't seem to find a way to have a control's name
be in a variable, and while in my loop access the value of that control.

Can someone help me with this?

Your assistance would be greatly appreciated, and thanks for reading.

 

[Dave Peterson]

Maybe this will get you closer. (And I'm assuming that your textbox is from the
control toolbox toolbar.)

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim OLEObj As OLEObject
Dim res As Variant 'could be an error
Dim LookUpRng As Range
Dim myStr As String
Dim myVal As Double

Set wks = Worksheets("Sheet1")

Set LookUpRng = Worksheets("Sheet2").Range("A:e")

For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
If LCase(Left(OLEObj.Name, 6)) = LCase("brkqua") Then
'MsgBox "Found it!" 'just for testing!
myStr = OLEObj.Object.Value
res = Application.VLookup(myStr, LookUpRng, 2, False)
If IsError(res) Then
'no match, what should be done
MsgBox "No match"
Else
If IsNumeric(res) = False Then
'arithmetic would fail, what should be done
MsgBox "Fix the table!"
Else
myVal = res * 12.34 'whatever
MsgBox Format(myVal, "$#,##0.00")
End If
End If
End If
End If
Next OLEObj

End Sub

 

[norie]

If this is a userform then take a look at the Controls collection.

Here's a simple loop:

Dim ctl As MSForms.Control

For Each ctl In Me.Controls
' do stuff with control
Next ctl

You can check the name and type of the control within the loop and
then so whatever you want to do.

 

[CompleteNewb]

Thanks, Norie and Dave. Dave, these are user forms, not controls put into
worksheets, so none of that stuff I found about referring to controls and
their values applied, as it was all about controls inserted into worksheets,
and apparently it's a horse of a different color when dealing with actual
forms.

Norie, I did figure out a workaround to address a control's value, but I was
never able to refer to controls using a variablename. For instance, if I
have a list f controlnames in a worksheet, and I set a variable named
"CtlName" to one of the values in the worksheet (let's say cell A3 as
"cboChooseState", which is the name of a combobox on the form, and I set
CtlName=Range(A3).value, I cannot then refer to CtlName.value, or
Controls(CtlName).value; it sees I can't refer tom a control if the name of
the resides in a variable. That's the issue I was having, and I've gotten
around it, but never figured out a way to resolve it.

 

[AB]

Although you've found a workaround - I agree with Norie - I don't see
any problems why you shouldn't be able to refer to a control using
myform.controls("controlname")
You surely can't do this:
CtlName.value
as your CtlName variable is a string but you surely can do this:
Sub CallControlByName()
Dim tbx As MSForms.TextBox
Const varTbxName As String = "TextBox1"
Set tbx = UserForm1.Controls(varTbxName)
tbx.Value = "MyValue"
MsgBox tbx.Value
UserForm1.Show
End Sub

The issue you might have been having could be to do with the fact that
not every control supports the .value property.
Again, that's what Norie was referring to - you need to know what type
of control that is and then you might need to use:
..value
or
..text
or
..caption
or maybe somthing else.

 

[Dave Peterson]

Sorry, I misread that first sentence!

If you want to loop through the controls looking for textboxes, you can do
something like:

Option Explicit
'some event in the userform module??
Sub Commandbutton1_click()

Dim Ctrl As Control
Dim res As Variant 'could be an error
Dim LookUpRng As Range
Dim myStr As String
Dim myVal As Double

Set LookUpRng = Worksheets("Sheet2").Range("A:e")

For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.TextBox Then
If LCase(Left(Ctrl.Name, 6)) = LCase("brkqua") Then
'MsgBox "Found it!" 'just for testing!
myStr = Ctrl.Value
res = Application.VLookup(myStr, LookUpRng, 2, False)
If IsError(res) Then
'no match, what should be done
MsgBox "No match"
Else
If IsNumeric(res) = False Then
'arithmetic would fail, what should be done
MsgBox "Fix the table!"
Else
myVal = res * 12.34 'whatever
MsgBox Format(myVal, "$#,##0.00")
End If
End If
End If
End If
Next Ctrl

End Sub

if you have a specific textbox you want to use, you can use:

MsgBox Me.brkquaTest1.Value
or
MsgBox Me.Controls("brkquatest1").Value

If you know that you have 14 of those textboxes, you can use that second syntax
and loop through them with something like:

Dim iCtr as long
for ictr = 1 to 14
msgbox me.controls("brkquatest" & ictr).value
next ictr