Controlling Controls with Blanket Rules

[BrianExcel]

I have a group of text boxes inside a frame. What I want to do is write a rule that says "If the value of any textboxes within this frame = MISC" then do code.

So far I have gotten as far as defining the txtbox as a control and trying to reference it, but it doesn't give me the value property as an option.

This is the code I would like to try and use if possible, but I need to consider the value (or text) property of the textbox.

dim cntl as control
for each cntl in userform1.frame3.controls
if cntl.value [COLOR="Red"]<-- this where I need to reference value[/COLOR]
= "Misc" then
DO CODE.

Any thoughts?

 

[mikerickson]

I'm not sure if userform controls are objects of type Control. They might be of type msForms.Control. But I'd avoid the issue entirly with code like this. (Note the Dim statement.)

The VBEditor might not be aware that cntl has a .Value property, so it might not give you that option.

This code has two issues.
1) There might be controls other than texboxes in Frame1. If so, testing the .Value property might be a problem (e.g. if a Label is in the Frame, there will be an error)

2) If two textboxes have the value Misc, the Do Code will run twice. I'm not sure if that is what you want.

I hope this helps.

Dim cntl as Object

For Each cntl in Userform1.Frame3.Controls
    If cntl.Value = "Misc" Then
        Rem do code
    End IF
Next

 

[BrianExcel]

That definitely worked. ALthough I do have controls other than the text boxes within that frame (labels, for example). I would like to keep those if possible.

Do you have a better way of referencing those controls using smoething like a group name perhaps?

Thanks!

 

[BrianExcel]

I was able to get it work (using your code) by doing a double IF statement. It may not be the most efficient way, but it works and the workbook and tasks are small enough that the time difference would never be noticed.

Here is the code I wound up using...

                    For Each cntl In UserForm1.Frame3.Controls
                        If Left(cntl.Name, 3) = "txt" Then
                            If cntl.Value = "Misc." Then
                            cntl.ForeColor = vbRed
                            cntl.Font.Bold = True
                            End If
                        End If
                    Next

Thanks again for your help!

 

[shg]

If you're hankering for IntelliSense, then ...

Sub x()
    Dim oCtl As MSForms.Control
    Dim oTxt As MSForms.TextBox
    
    For Each oCtl In UserForm1.Frame1.Controls
        If TypeOf oCtl Is MSForms.TextBox Then
            Set oTxt = oCtl
            If LCase(oTxt.Value) = "misc." Then
                oTxt.ForeColor = vbRed
                oTxt.Font.Bold = True
            End If
        End If
    Next oCtl
End Sub