User defined error
 

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
 

Pasty,
You need to prevent execution of the Error handler if there is no error:

.......
End If

Exit Sub

ErrHandler:
Application.EnableEvents....

NickHK

"Pasty" wrote in message
...
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