User defined error
 

What line throws the error?

"Pasty" wrote:

I have these two bits of code which are called cmbName and cmbMonth - I
borrow this code from another spreadsheet I have and although it does what I
want it to it keeps popping up with Error 1004 messages. Can anyone help?

Code is:

Private Sub cmbName_Click()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
On Error GoTo ErrHandler

If cmbName.ListIndex < -1 Then
Application.EnableEvents = False
With Worksheets("Sheet1")
.Range("IU1").Value = cmbName.Value
Application.Calculate
Set rng = .Range(.Cells(1, 256), .Cells(1, 256).End(xlDown))
Set rng1 = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
Set rng2 = Intersect(rng1.EntireRow, .Columns(1))
rng.AutoFilter Field:=1, Criteria1:="=Show"
Set rng3 = Nothing
On Error Resume Next
Set rng3 = rng2.SpecialCells(xlVisible)
On Error GoTo ErrHandler
cmbMonth.Enabled = True
End With
If Not rng3 Is Nothing Then
RemoveDuplicates cmbMonth, rng3
Else
MsgBox "No data for " & rng.Parent.Range("IU").Value
End If
End If

ErrHandler:
Application.EnableEvents = True
If Err.Number < 0 Then
MsgBox "Error in cmbName_Click" & vbNewLine & vbNewLine & _
Err.Number & vbNewLine & _
Err.Description
End If
End Sub

Private Sub cmbMonth_Click()
Dim rng As Range
With Worksheets("Sheet1")
On Error GoTo ErrHandler
Application.EnableEvents = False
With Worksheets("Sheet1")
.Range("IT1").Value = cmbName.Value
Set rng = .Range(.Cells(1, 256), .Cells(1, 256).End(xlDown))
End With
If cmbMonth.ListIndex = -1 Then
.Range("IT1").ClearContents
Application.Calculate
rng.AutoFilter Field:=1, Criteria1:="=Show"
Else
.Range("IT1").Value = cmbMonth.Value
Application.Calculate
rng.AutoFilter Field:=1, Criteria1:="=Show"
End If
End With
ErrHandler:
Application.EnableEvents = True
If Err.Number < 0 Then
MsgBox "Error in cmbMonth_Click" & vbNewLine & vbNewLine & _
Err.Number & vbNewLine & _
Err.Description
End If
End Sub

User defined error
 

That's the problem I am having it is not highlighting any code or going to
the debug bit it is just saying:

error in cmbName_click
Error 1004 application-defined or object-defined error

And the same happens with cmbMonth

Its also making the dropdown in cmbName show the contents of columns C & D
in the drop down rather than column B (which is where the departments are
stored)

"JLGWhiz" wrote:

What line throws the error?

"Pasty" wrote:

I have these two bits of code which are called cmbName and cmbMonth - I
borrow this code from another spreadsheet I have and although it does what I
want it to it keeps popping up with Error 1004 messages. Can anyone help?

Code is:

Private Sub cmbName_Click()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
On Error GoTo ErrHandler

If cmbName.ListIndex < -1 Then
Application.EnableEvents = False
With Worksheets("Sheet1")
.Range("IU1").Value = cmbName.Value
Application.Calculate
Set rng = .Range(.Cells(1, 256), .Cells(1, 256).End(xlDown))
Set rng1 = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
Set rng2 = Intersect(rng1.EntireRow, .Columns(1))
rng.AutoFilter Field:=1, Criteria1:="=Show"
Set rng3 = Nothing
On Error Resume Next
Set rng3 = rng2.SpecialCells(xlVisible)
On Error GoTo ErrHandler
cmbMonth.Enabled = True
End With
If Not rng3 Is Nothing Then
RemoveDuplicates cmbMonth, rng3
Else
MsgBox "No data for " & rng.Parent.Range("IU").Value
End If
End If

ErrHandler:
Application.EnableEvents = True
If Err.Number < 0 Then
MsgBox "Error in cmbName_Click" & vbNewLine & vbNewLine & _
Err.Number & vbNewLine & _
Err.Description
End If
End Sub

Private Sub cmbMonth_Click()
Dim rng As Range
With Worksheets("Sheet1")
On Error GoTo ErrHandler
Application.EnableEvents = False
With Worksheets("Sheet1")
.Range("IT1").Value = cmbName.Value
Set rng = .Range(.Cells(1, 256), .Cells(1, 256).End(xlDown))
End With
If cmbMonth.ListIndex = -1 Then
.Range("IT1").ClearContents
Application.Calculate
rng.AutoFilter Field:=1, Criteria1:="=Show"
Else
.Range("IT1").Value = cmbMonth.Value
Application.Calculate
rng.AutoFilter Field:=1, Criteria1:="=Show"
End If
End With
ErrHandler:
Application.EnableEvents = True
If Err.Number < 0 Then
MsgBox "Error in cmbMonth_Click" & vbNewLine & vbNewLine & _
Err.Number & vbNewLine & _
Err.Description
End If
End Sub