Search code examples
excelvbauserform

VBA Clear Macro Not Working Unless on Filter Criteria Sheet

I built a userform for doing a simple filter search. I can open the form and filter while any worksheet is selected. However, when I use the clear button from the userform but I am not on the filter criteria worksheet (Sheet1) I get a "1004: Select Method of Range class failed" error for the first select statement.

Sub Clear()
'
' Clear Macro
'
    Sheet1.Range("A2:H2").Select
    Selection.ClearContents
    Sheet1.Range("A5:H1725").Select
    Selection.ClearContents
    Sheet1.Range("A2").Select

End Sub

Am I forced to have the filter sheet open or is my code just not working correctly? I wanted to have a sheet with the open form button with the data (Sheet2) and filter criteria (Sheet1) hidden.

On cmdClear_click() I am calling this before running Clear:

Private Sub ClearForm()
'
'clear values
    With Me
        .txtCustId = ""
        .txtCustName = ""
        .txtAddress = ""
        .txtCity = ""
        .txtState = ""
        .txtZip = ""
        .txtCountry = ""
        .txtStatus = ""
    End With
End Sub

The only other thing I can think of that may screw it up is the Me....

Solution

  • .Select only works if you are on the specified Sheet, in your code you are using Sheet1(..).Select meaning that you are selecting something on Sheet1, if you are on a different Sheet/Tab it will fail. To avoid this, you have to avoid using Select.

    Same outcome without Select:

    Sub Clear()
'
' Clear Macro
    With ThisWorkbook
        'Replace "Sheet1" if you have renamed your Sheet to something else
        .Sheets("Sheet1").Range("A2:H2").ClearContents
        .Sheets("Sheet1").Range("A5:H1725").ClearContents
    End With
End Sub