Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Defined Type not Defined error | Excel Programming | |||
"User-defined type not defined" error when trying to send e-mail | Excel Programming | |||
please help me with this error...user defined not defined. | Excel Programming | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
error with user defined | Excel Programming |