我目前正在制作一个 excel 文档来对某些产品进行排序,并显示它们包含哪些组件。我的 A 列是组件,第 1 行是我的产品。我目前有 2 个用户表单,1 个用于添加新组件,另一个用于新产品。在我的新产品表单中,我可以选择列表框中第 1 列中的任何组件,然后将其添加到第二个列表框。但是我现在无法继续,我希望每个产品及其组件之间的对应单元格根据是否使用每个组件来改变颜色。用户表单代码是:
Private Sub AddList_Click()
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then ListBox2.AddItem ListBox1.List(i)
Next i
End Sub
Private Sub CommandButton1_Click()
ListBox1.Value = ""
End Sub
Private Sub CommandButton2_Click()
End Sub
Private Sub CommandButton3_Click()
Unload Me
UserForm1.Show
End Sub
Private Sub CommandButton4_Click()
If ListBox2.Contains("#A2") Then
MsgBox "Please enter condition in notes"
End Sub
Private Sub CommandButton5_Click()
ListBox2.Clear
End Sub
Private Sub CommandButton6_Click()
Dim counter As Integer
counter = 0
For i = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(i - counter) Then
ListBox2.RemoveItem (i - counter)
counter = counter + 1
End If
Next i
End Sub
Private Sub ListBox1_Click()
End Sub
Private Sub ListBox2_Click()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
For i = 1 To 500
ListBox1.AddItem Sheet1.Cells(i, 1)
Next
End Sub
答案1
我建议你在每个单元格中添加“TRUE”价值在与组件匹配的产品列中。然后使用标准条件格式突出显示包含值 TRUE 的单元格。如果您不想在单元格中看到文本值,请将默认文本颜色设为白色,并将条件格式文本颜色设为与单元格填充颜色相同的颜色。
粗略的伪代码如下:
'insert new product in row 1
newproductcol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Cells(1, newproductcol).Value = Textbox1.value
'now find components
For i = 2 To Cells(Rows.Count,1).End(xlUp).Row
For j = 0 To listboxcount - 1
If listboxitemvalue(j) = cells(i,1).value then
cells(i, newproductcol).Value = TRUE
exit for
end if
Next j
Next i