组合框列表中的灰色项在使用后，因此用户无法选择它们

Question

我有一个项目，我正在工作的硬件部署在新的地点。我有一个带有IP地址列表的userform组合框，该列表将分配给某些硬件项目(即信用卡机器)。我试图找到一种方法，一旦选择一个IP地址供使用，组合框列表中的项目要么被删除，要么灰暗，用户无法重用。但是，如果由于某种原因IP返回可用，则该项目现在可在列表中使用。硬件、IP和其他信息将添加到excel中的表/数据库中。因此，我假设必须根据该表/数据库验证这个IP列表。但我不知道如何才能完成验证。我已经尝试过数据验证，但是我想在基于combobox的VBA中进行验证。

​

这是当前输入IP地址组合框的代码。您可以输入单个IP或范围。

Private Sub Submit_Data_Click()
 Dim wb As Workbook, ws As Worksheet, rngTarget As Range
    Dim s1 As String, ip1 As Variant
    Dim s2 As String, ip2 As Variant
    Dim i As Integer, n As Integer

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Arrays")
    Set rngTarget = ws.Range("I" & Rows.Count).End(xlUp)

    If Me.Add_single_IP = True Then
        s1 = Me.sgle_IP_add_tb1
        s2 = s1
    Else
        s1 = Me.rge_IP_start_tb2
        s2 = Me.Rge_IP_End_tb2
    End If

    ' split string into bytes
    ip1 = Split(s1, ".")
    ip2 = Split(s2, ".")

    ' validate
    Dim msg As String
    If UBound(ip1) <> 3 Or UBound(ip2) <> 3 Then
        msg = "IP must be n.n.n.n"
    ElseIf ip1(3) > 255 Or ip2(3) > 255 Then
        msg = "Host must be 1 to 255"
    ElseIf ip1(3) > ip2(3) Then
        msg = s1 & " is greater then " & s2
    ElseIf ip1(0) <> ip2(0) Or ip1(1) <> ip2(1) Or ip1(2) <> ip2(2) Then
        msg = "Different networks"
    End If

    ' failed validation
    If Len(msg) > 0 Then
        MsgBox msg, vbCritical, s1 & "-" & s2
        Exit Sub
    End If

    ' calc range and write to sheet
    n = ip2(3) - ip1(3) + 1
    For i = 1 To n
       Set rngTarget = rngTarget.Offset(1, 0) ' move down
       rngTarget = Join(ip1, ".")
       ip1(3) = ip1(3) + 1
    Next
    MsgBox n & " addresses added ", vbInformation, s1 & "-" & s2
End Sub

​

正如我所说的prior...If，我可以从选择中删除IP，因为它们是分配的，这样我们就没有副本，如果不再使用硬件块，我们就有能力再次使用它。如果这样做更容易，那么允许它仍然是可见的，但在列表中是灰色的，当然也不允许选择它，并且向用户添加一个IP已经在使用的MsgBox错误也会很好。

组合框应该将可用/未使用的IP写入名为MstrInv的表中的数据库表中。

​

​

谢谢你的帮助。

Answer 1

我会输入所有IP地址到一个单独的‘助手’范围与2列。第一列将具有IP地址，第二列将具有、Yes、或无值。我会使用索引和匹配的组合来动态填充第二列(下面将解释)。

然后，我将使用填充IP地址的新列填充combobox，但是填充到combobox中的唯一IP地址是列旁边有No的IP地址，因为它们没有被“使用”。

  Example of the Helper columns:            Example of your Array sheet columns:
         A            B                            H                 I
   +------------+------------+               +--------------+-----------------+ 
 1 | IP Address |   In Use?  |             1 | Brand/Model  |  CC Machine IP  |
 2 |  10.0.0.1  |     Yes    |             2 |  Model ABC   |     10.0.0.1    |
 3 |  10.0.0.2  |     Yes    |             3 |  Brand 123   |     10.0.0.4    |
 4 |  10.0.0.3  |     No     |             4 |              |                 |
 5 |  10.0.0.4  |     Yes    |             5 |  Brand 456   |     10.0.0.2    |
 6 |  10.0.0.5  |     No     |             6 |              |                 |
   +------------+------------+               +--------------+-----------------+

这将用值填充combobox；10.0.0.3和10.0.0.5按顺序排列，它们之间没有空格。

但我们究竟是如何做到这一点的？

假设我已经在一个名为"HelperSheet"的新工作表上设置了助手列，其中Column A中有“IP地址”和“在使用中”。是/否在Column B和IP地址从您的输入用户表单输入到工作表"Arrays"。您可以在自己的代码中对这些值进行必要的调整。

首先，输入您希望在Column A中使用的所有IP地址。您输入它们的顺序将决定它们在combobox__中的填充顺序。

为了方便起见，您可以输入第一个IP地址，然后填充其余的IP地址。您可以通过单击单元格右下角并向下拖动工作表来填充值/公式。

接下来，我们需要在第一行的Column B中输入索引/匹配公式，输入IP地址。如果您的第一个IP地址位于单元A2中，则第一个公式应该在单元B2中紧邻它。

=IFERROR(索引(数组！$I$1：$I$255，MATCH(A1，$I$1：$I$255，0))=A1，“是”，“否”)

如果您想了解更多关于这些工作表函数的信息，可以在页面上阅读它们。

• IFERROR
• 如果
• 索引
• 匹配

接下来，将公式从列中一直填充到最后一个IP地址行。您可以通过单击单元格右下角并向下拖动工作表来填充值/公式。

现在，在Column B中，在Column A中有一个IP地址旁边的每个单元应该有上面的公式--唯一的区别将是MATCH函数中对A1的引用，因为它不是绝对引用(看起来像$A$1) --因为它是一个相对引用，它将增加每一行的数目。

当您开始在"Arrays"表上填充"Arrays"时，"HelperSheet"上的Column B中的值将开始更改为Yes，只要它们与Column A中的相应值相匹配。当然，当您从Column I on "Arrays"上删除IP地址时，Column B on "HelperSheet"中的值将更改为NO。输入的任何IP地址如果与"HelperSheet"上的地址不匹配，将被忽略。

现在要填充Combobox

在VBE中，单击左边列表中的Arrays工作表，并将下拉列表更改为“工作表”和“更改”。这将在每次对"Arrays"表进行更改时运行代码。

​

​

下面是一个示例代码(将注释添加到极右以帮助解释每一行)。

您需要更改UserForm2的名称，以匹配包含ComboBox的用户表单的名称。

Private Sub Worksheet_Change(ByVal Target As Range)                'Target is the cell/cells that a change has been made on to make the code run.

Dim IPRangeItem As Long
Dim myArray As Variant
Dim IPRange As Range
Dim LastRow As Long
Dim ArrayCounter As Long

If Target.Column = 9 Then                                                         'This will only run the code below if the column the cells was changed in is I (the 9th column) otherwise it will exit the subroutine.

    LastRow = ThisWorkbook.Sheets("HelperSheet").Cells(Rows.Count, 1).End(xlUp).Row    'Finding the last row on our helper sheet where the IP addresses are entered. 
    Set IPRange = ThisWorkbook.Sheets("HelperSheet").Range("A1:B" & LastRow)
    myArray = IPRange                                                             'This line puts the range defined above straight into an Array.

        UserForm2.ComboBox1.Clear                                              'Ensures the combobox is always empty before values are assigned. 
        ArrayCounter = 1
        For IPRangeItem = 1 To UBound(myArray)
            If myArray(ArrayCounter, 2) = "No" Then                           'Looking to see if the value in Column B was "No" (remember we put the entire helper range into an array which is faster and easier to use)
                UserForm2.ComboBox1.AddItem myArray(ArrayCounter, 1)                'If it was a "Yes" the IP address value is added to the Combobox list otherwise as below nothing happens.
            Else
                'Do nothing
            End If
            ArrayCounter = ArrayCounter + 1
        Next IPRangeItem
Else
    'Do nothing
End If
End Sub

现在，每次在您的Column I表上对"Arrays"进行更改时，将重新填充combobox中所有未使用的地址，同时考虑到所做的更改。

注意：在"HelperSheet"上输入的公式仅计算到第255行，如果需要扩展该公式，则需要相应地更新INDEX和MATCH部分中的范围。

注意:由于此代码在每次对"Arrays"工作表进行任何更改时都会运行，如果经常对工作表进行大量更改，则可能会导致性能较差--当任何其他工作表被更新/更改时，不会运行此代码。

下面是一个范围和组合框的示例：

数组表：

​

​

HelperSheet Sheet:

​

​

ComboBox list:

​

​

注意，combobox丢失了您输入到"Arrays"表中的地址，因为它们是基于"HelperSheet"值“正在使用”的。

Answer 2

从组合框的RowSource中删除第一列，并在窗体初始化时添加所需的项。

Private Sub UserForm_Initialize()

    Dim ws As Worksheet, iLastRow As Long, i As Long
    Set ws = ThisWorkbook.Sheets("Arrays")
    Me.ComboBox1.Clear
    iLastRow = ws.Cells(Rows.Count, "I").End(xlUp).Row
    For i = 3 To iLastRow
        If Len(ws.Cells(i, "H")) = 0 Then ' not assigned
            Me.ComboBox1.AddItem ws.Cells(i, "I")
        End If
    Next

End Sub