run time error for ignore list in comobox

[KDS14589]

I have he following code for a combobox on a userform, which works fine

Sub CommandButton1_save_Click()



Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("general.color2")

Dim i As Integer

Dim cancel As Integer



If ComboBox2.ListIndex = -1 Then

ComboBox2.BackColor = vbYellow

cancel = 1

MsgBox " ComboBox for is empty"

ComboBox2.SetFocus

Exit Sub

End If

If Not Intersect(ActiveCell, ActiveSheet.Range("i2:i100")) Is Nothing Then

i = sh.Range("i" & Application.Rows.Count).End(xlUp).Row + 1

ActiveCell.Value = Me.ComboBox2.Value

Else

MsgBox "Please limit selection to the 'Color Name' column (I column)." & vbCrLf & "Selection has been deleted." & vbCrLf & "Color selector form will close.", vbExclamation, "Wrong Column"

Unload Me

Exit Sub

End If

MsgBox "Data will be saved!!!", vbInformation

Unload Me

ShGE06.Worksheet_ShGE06_Activate



End Sub

It generated a list then placed the choice in column I

BUT I wanted the list it generated to ignore the choices' that were chosen before. I placed the following code as sub procedure,

Private Sub ComboPop()

''sintek @ excelforum 7.19.21

Dim z As Long



Me.ComboBox2.Clear

For z = 1 To Sheets.Count

If Not IsNumeric(Application.Match(Sheets(z).Name, Sheets("general.color2").Range("I:I"), 0)) Then

Me.ComboBox2.AddItem Sheets(z).Name

End If

Next z



Me.ComboBox2.Value = ""

End Sub

Which is code I use on another userform ( I just changed the worksheet and range, also the 'dim' reference from I to z so there is no cross referencing between user forms)

But now I get

"Run-time error '-2147467259 (80004005)': Unspecified error"

I looked on the web but all the solutions are for Microsoft Access.

I tried several code changes but with no luck.

Can someone tell me what I'm doing wrong, or suggest a different VBA code?

 

[Jeffrey Mahoney]

I ran a version of your code on my end with no error. Do you have your ComboPop sub in the FORM module? How are you calling the ComboPop sub? I put mine in like this...

Private Sub UserForm_Initialize()
  Call ComboPop
  
End Sub

 

[KDS14589]

I ran a version of your code on my end with no error. Do you have your ComboPop sub in the FORM module? How are you calling the ComboPop sub? I put mine in like this...

Private Sub UserForm_Initialize()
  Call ComboPop
 
End Sub

here is the complet code

Sub CommandButton1_save_Click()
 '''sintek @  excelforum   7.19.21
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("general.color2")
Dim i As Integer
Dim cancel As Integer

If ComboBox2.ListIndex = -1 Then
ComboBox2.BackColor = vbYellow
cancel = 1
MsgBox " ComboBox for is empty", vbExclamation, "Empty"
ComboBox2.SetFocus
Exit Sub
End If
If Not Intersect(ActiveCell, ActiveSheet.Range("i2:i100")) Is Nothing Then
i = sh.Range("i" & Application.Rows.Count).End(xlUp).Row + 1
ActiveCell.Value = Me.ComboBox2.Value
Else
MsgBox "Please limit selection to the 'Color Name' column (I column)." & vbCrLf & "Selection has been deleted." & vbCrLf & "Color selector form will close.", vbExclamation, "Wrong Column"
Unload Me
Exit Sub
End If
MsgBox "Data will be saved!!!", vbInformation, "Saved"
Unload Me
ShGE06.Worksheet_ShGE06_Activate

End Sub


Private Sub CommandButton2_exit_Click()

Unload Me

End Sub

Private Sub CommandButton3_refresh_Click()

Unload Me
ColorNameSelect.Show

End Sub

Private Sub UserForm_Initialize()
Call ComboPop
End Sub

Private Sub ComboPop()

Dim z As Long

Me.ComboBox2.Clear
For z = 1 To Sheets.Count
    If Not IsNumeric(Application.Match(Sheets(z).Name, Sheets("general.color2").Range("I:I"), 0)) Then
        Me.ComboBox2.AddItem Sheets(z).Name
    End If
Next z

Me.ComboBox2.Value = ""
End Sub

its the last one that's giving me fits
I can comment it out then everthing is fine, but as soon as i uncomment it the errors start