VBA通过用户窗体中的文本框控件循环 [英] VBA Loop through textbox controls in userform

问题描述

我浏览了许多有关UserForm控件循环的文章，但是似乎无法调整我发现的代码以满足我的需要，并且需要一些帮助.

I have looked through numerous posts on looping through UserForm Controls but cant seem to adjust the code i have found for my needs and need some help.

我要弄清楚的情况:

1. 我在一个用户窗体上有44个文本框，其名称均以"ch"开头，例如"chTextBox1"

1. I have 44 text boxes on a userform whose names all start with "ch" example "chTextBox1"

激活用户窗体时，我需要遍历所有以"ch"开头的文本框，并将这些文本框的背景色更改为基于单元格内部颜色的颜色

When the userform activates I need to loop through all of the text boxes that start with "ch" and change the background color of those textboxes to a color based on the interior color of a cell

下面是我一直在处理的代码，我要么陷入无限循环，要么得到

Below is the code that I have been messing around with and I either end up in an infinite loop or I get

错误424

Error 424

Private Sub UserForm_Activate()
    Dim wb As Workbook
    Dim wsRR As Worksheet
    Dim bColor As Range
    Dim c As Control
    Dim y As String

    Set wb = Application.ThisWorkbook
    Set wsRR = wb.Sheets("RiskRating")
    Set bColor = wsRR.Range("C3")   

    For Each c In JHKey.Controls
        If TypeName(c) = "TextBox" Then
            y = Left(c, 2)
        End If
        If y = "ch" Then
            c.BackColor = bColor.Interior.Color
        End If
    Next c
End Sub

推荐答案

尝试将"ch"的If语句测试放入"TextBox"的If语句测试中.另外，在检查控件名称时，应为控件指定Name属性，否则默认为其Value属性.另外，顺便说一句，我建议用关键字Me替换JHKey，该关键字指的是用户窗体本身，而不管其名称如何.

Try placing the If statement testing for "ch" within the If statement testing for "TextBox". Also, you should specify the Name property for the control when checking for its name, otherwise it defaults to its Value property. Also, as an aside, I would suggest replacing JHKey with the keyword Me, which refers to the userform itself regardless of its name.

Private Sub UserForm_Activate()
    Dim wb As Workbook
    Dim wsRR As Worksheet
    Dim bColor As Range
    Dim c As Control
    Dim y As String

    Set wb = Application.ThisWorkbook
    Set wsRR = wb.Sheets("RiskRating")
    Set bColor = wsRR.Range("C3")

    For Each c In Me.Controls
        If TypeName(c) = "TextBox" Then
            y = Left(c.Name, 2)
            If y = "ch" Then
                c.BackColor = bColor.Interior.Color
            End If
        End If
    Next c
End Sub

这篇关于VBA通过用户窗体中的文本框控件循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！