根据单元格值将Textboxes添加到Userform框架
根据单元格值将Textboxes添加到Userform框架
提问于 2019-10-21 01:06:06
我希望用户按下一个按钮，将他们所有的“打开”位置拉到一个用户表单中。
我有一个具有框架和命令按钮的Userform，可以搜索电子表格上的第3列(名为"Status")，并找到单词"Open“。一旦找到这个单词，我希望文本框在框架内自动创建，其中包含来自4-10列的“查找”行的数据。
因此，如果发现一行带有"Open“，那么我希望在userform上创建7个文本框。从下面的图片，我期望28个文本框。
当我按下命令按钮时，什么都不会发生--没有错误--什么也没有。我试图使用断点来排除故障，但没有向我透露任何对其采取行动的信息。我没有“明白”为什么它在这里不起作用。
我试着将代码移出userform并进入一个模块，并调用函数，但这没有起到任何作用。
我尝试将我想要的行设置为常量，并在主代码中创建一个单独的"AddBox“函数来调用，但这不起作用，但我也不像现在发布的代码那样理解该代码。
帧名= Open_Positions_Frame
命令Button Name = open_post_but
工作表名称=数据库
Public Sub open_post_but_Click() '*****The command Button on the userform Frame*****
Dim i As Integer ' for the loop
Dim wb As Workbook
Dim wk As Worksheet
Dim ctlTextBox As MSForms.TextBox
Dim intCol, intCols As Integer '******for the columns I want to pull into the userform*****
Dim strCName As String '******this is to name the textboxes******
Dim lastRow As Long
Dim curColumn As Long
Set wb = ThisWorkbook
Set wk = wb.Sheets("Database")
curColumn = 3 '*********column index here - Mine starts at Column 3 ********
lastRow = wk.Cells(Rows.Count, curColumn).End(xlUp).row
For i = 3 To lastRow 'Loop through row 3 to last row
'********If the text "Open" is found in a row in column 3 then
' add & fill textboxes with data from Columns 4-10 onto the userform frame******
If wk.Cells(i, 3) = "Open" Then
'******Add the textboxes***********
'*****Columns 4-10 add textbox and give the control a name C# - #is the column*****
For intCol = 4 To intCols = 10
strCName = "C" & intCol
Set ctlTextBox = Open_Positions_Frame.Controls.Add("Forms.Textbox.1", strCName)
With ctlTextBox
.Top = 10
.width = 50
.height = 20
.Left = (intCol) * 60
.Value = intCol
End With
Next
'********************************
End If
Next i
End Sub回答 2
Stack Overflow用户
回答已采纳
发布于 2019-10-21 01:21:51
更新并测试了
你们是在把文本框写在对方的上面。我一排排地加了一个分离器。
Public Sub open_post_but_Click()
'you might want to experiment with these.
Const curColumn As Long = 4 'index will start a column 4 "Status"
Const theWidth As Long = 66 'you might experiment with these
Const theRowDifference As Long = 20
Dim i As Long, intCol As Long, rowspacer As Long
Dim ctlTextBox As MSForms.TextBox
Dim wb As Workbook: Set wb = ThisWorkbook
Dim wk As Worksheet: Set wk = wb.Sheets("Database")
Dim lastRow As Long: lastRow = wk.Cells(Rows.Count, curColumn).End(xlUp).row
For i = 3 To lastRow
For Each cl In wk.Cells(3, curColumn)
If InStr(1, cl.Value, "Open", vbTextCompare) > 0 Then
rowspacer = rowspacer + theRowDifference
'******Add the textboxes***********
For intCol = 5 To 11
Set ctlTextBox = Open_Positions_Frame.Controls.Add("Forms.Textbox.1")
With ctlTextBox
.Top = rowspacer
.width = theWidth
.height = 20
.Left = intCol * 60
.Name = "openPosBox" & intCol
.Value = wk.Cells(i, intCol).Value
End With
Next intCol
'********************************
End If
Next cl
Next i
End SubStack Overflow用户
发布于 2019-10-25 16:35:59
PGSystemTester帮助解决了堆叠文本框的问题-非常感谢！然而，我仍然在为我的If条件声明而挣扎。我设法弄清楚了，我正在为任何需要这个用例的人添加完整的代码作为答案。
Public Sub open_post_but_Click()
Const curColumn As Long = 4 'index will start a column 4 "Status"
Const theWidth As Long = 70 'you might experiment with these
Const theRowDifference As Long = 20
Dim i As Long, intCol As Long, rowspacer As Long
Dim ctlTextBox As MSForms.TextBox
Dim wb As Workbook: Set wb = ThisWorkbook
Dim wk As Worksheet: Set wk = wb.Sheets("Database")
Dim lastRow As Long: lastRow = wk.Cells(Rows.Count, curColumn).End(xlUp).row
Dim status As String
status = "Open"
For i = 3 To lastRow
If Cells(i, curColumn).Value = status Then 'took on the Instr() function and added this instead, textboxes will only be created if the column 4 status is "Open"
rowspacer = rowspacer + theRowDifference
'******Add the textboxes***********
For intCol = 4 To 11
Set ctlTextBox = Open_Positions_Frame.Controls.Add("Forms.Textbox.1")
With ctlTextBox
.Top = rowspacer
.width = theWidth
.height = 20
.Left = intCol * 40
.Name = "openPosBox" & intCol
.Value = wk.Cells(i, intCol).Value
End With
Next intCol
'********************************
End If
Next i
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58478616
复制相关文章
相似问题
腾讯云开发者
