Excel VBA, Can any body explain this for me

[kerrance76]

Hi
I have an access application with a linked excel file, there are to many fields to build a form in access so i am trying to implement a few custom search boxes using combo boxes, the code i am using is

Private Sub cboJobNumberSearch_Change()
Txt = cboJobNumberSearch
    If Not Txt = "Job Number" Then
        filterform
    Else
        Me.AutoFilterMode = False
    End If

End Sub

Private Sub cmdClear_Click()
    
    cboJobNumberSearch = "Job Number"
    
End Sub
Public Sub filterform()

    Worksheets("Sheet1").Range("A1").AutoFilter field:=1, Criteria1:=Txt
    
End Sub

The problem i am having is that i get error 1004 autofilter range error
If i put a breakpoint in everything works ok until i get to the autofilter line, the form is filtered ok but the code then goes back to the top and runs again, the second time i get to the autofilter line the error occurs.

Can anybody explain why the code runs twice and how to stop it or how to re write this, many thanks.

Spreadsheet attached

 

[Banana]

No Access here...

First- When you say the code runs first time, did you mean it goes all to End Sub then start again or did it stop at AutoFilter and return to the start?

Second- Why are you use Change event? This will trigger *every* time someone type in something or delete something, and therefore would be very inefficient. Why not AfterUpdate event?

 

[kerrance76]

Sorry i should have said, this is being built into an excel spreadsheet as i cant fit all the fields in access, i have a button in access that opens this file.

1. It stops at the auto filter then returns to the start of the combobox sub and runs again

I have it in the change event because that is the one that appeared after double clicking the combobox, I have no experience with excel and assumed that this was the only event available, on further inspection i cannot see a after update event in the list available.