ComboBox Permission Error

[nubranger]

I am getting "Permission Denied" error when I change the value of one of the ComboBoxes. Error happens starting on the 3rd change. I am getting error on these lines

Worksheets("Settings").ComboBox1.List = Split(Left2Use, ",")
Worksheets("Settings").ComboBox2.List = Split(Left2Use, ",")

Full Code

Private Sub Workbook_Open()

  Worksheets("Settings").ComboBox1.Value = Null
  Worksheets("Settings").ComboBox2.Value = Null
   
Worksheets("Settings").ComboBox1.List = Split(" ,1,2,3,4,5", ",")
Worksheets("Settings").ComboBox2.List = Split(" ,1,2,3,4,5", ",")

End Sub

Sub ComboBox1_Change()
   ManageDropDowns (1)
End Sub

Sub ComboBox2_Change()
   ManageDropDowns (2)
End Sub


Sub ManageDropDowns(IDNum As Integer)

   Dim UsedList As String, FullList As String, Left2Use As String
   Dim FullArray As Variant, UsedArray(1 To 2) As String

   UL1 = ""
   UL1 = UL1 & IIf(Trim(ComboBox2.Value) = "", "", "" & Trim(ComboBox2.Value) & ",")
   While Right(UL1, 1) = ","
      UL1 = Left(UL1, Len(UL1) - 1)
   Wend

   UL2 = ""
   UL2 = UL2 & IIf(Trim(ComboBox1.Value) = "", "", "" & Trim(ComboBox1.Value) & ",")

   FullList = "1,2,3,4,5"
   FullArray = Split(FullList, ",")
   Left2Use = " ,"

   If IDNum <> 1 Then
      UsedList = UL1
      Left2Use = " "  ' Space
      For i = 0 To UBound(FullArray)
         iTxt = Trim("" & i + 1)
         If Not (InStr(1, UsedList, iTxt) > 0) Then
            ' Not Already Used - Add it to Left2Use
            Left2Use = Left2Use & "," & iTxt
         End If
      Next i
      Worksheets("Settings").ComboBox1.List = Split(Left2Use, ",")
   
   End If
   If IDNum <> 2 Then
      UsedList = UL2
      Left2Use = " "  ' Space
      For i = 0 To UBound(FullArray)
         iTxt = Trim("" & i + 1)
         If Not (InStr(1, UsedList, iTxt) > 0) Then
            ' Not Already Used - Add it to Left2Use
            Left2Use = Left2Use & "," & iTxt
         End If
      Next i
      Worksheets("Settings").ComboBox2.List = Split(Left2Use, ",")
   End If
End Sub

 

[nubranger]

your link doesn’t work unfortunately

Here

 

[davesexcel]

Sorry, kept looking for this, did not see the post moved to a 2nd page.

I believe you are in an infinite loop because of the combobox change events.

You can turn off events before the code changes the other combobox, the enable events again once the code has finished.

Sub ManageDropDowns(IDNum As Integer)

    Dim UsedList As String, FullList As String, Left2Use As String
    Dim FullArray As Variant, UsedArray(1 To 2) As String
    Dim i As Integer
    Application.EnableEvents = False

    UL1 = ""
    UL1 = UL1 & IIf(Trim(ComboBox2.Value) = "", "", "" & Trim(ComboBox2.Value) & ",")
    While Right(UL1, 1) = ","
        UL1 = Left(UL1, Len(UL1) - 1)
    Wend

    UL2 = ""
    UL2 = UL2 & IIf(Trim(ComboBox1.Value) = "", "", "" & Trim(ComboBox1.Value) & ",")

    FullList = "1,2,3,4,5"
    FullArray = Split(FullList, ",")
    Left2Use = " ,"

    If IDNum <> 1 Then
        UsedList = UL1
        Left2Use = " "                           ' Space
        For i = 0 To UBound(FullArray)
            iTxt = Trim("" & i + 1)
            If Not (InStr(1, UsedList, iTxt) > 0) Then
                ' Not Already Used - Add it to Left2Use
                Left2Use = Left2Use & "," & iTxt
            End If
        Next i
        With Worksheets("Settings").ComboBox1
            .Clear
            .List = Split(Left2Use, ",")
        End With
    End If
    If IDNum <> 2 Then
        UsedList = UL2
        Left2Use = " "                           ' Space
        For i = 0 To UBound(FullArray)
            iTxt = Trim("" & i + 1)
            If Not (InStr(1, UsedList, iTxt) > 0) Then
                ' Not Already Used - Add it to Left2Use
                Left2Use = Left2Use & "," & iTxt
            End If
        Next i
        With Worksheets("Settings").ComboBox2
            .Clear
            .List = Split(Left2Use, ",")
        End With
    End If
    Application.EnableEvents = True
End Sub