Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    ambiguous name detected (Excel 2002/2003)

    Hi

    I have this maco which works fine, I am trying to call it when I leave the worksheet, but I get an Ambiguous name detected, any ideas please

    Sub CustListClear()

    Sheets("Customer Price List").Unprotect
    Selection.AutoFilter Field:=1

    Sheets("Customer Price List").Protect
    End Sub
    *************************************************
    Private Sub Worksheet_Deactivate()
    Sheets("Customer Price List").Unprotect

    CustListClear
    Sheets("Customer Price List").Protect

    End Sub
    If you are a fool at forty, you will always be a fool

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ambiguous name detected (Excel 2002/2003)

    There is a duplication in the names in your VBAProject somewhere. Does the error box show a name? If so, try a find for that name (search in Current project) and see where you've duplicated the name.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: ambiguous name detected (Excel 2002/2003)

    Hi Jan Karel

    I removed the duplicate, but now the macro stops here: Selection.AutoFilter Field:=1 ( it errors here)


    Sub CustListClear()
    '
    ' CustListClear Macro
    ' Macro recorded 11/07/2005 by XX0824
    '

    '
    Sheets("Customer Price List").Unprotect
    Selection.AutoFilter Field:=1 ( it errors here)

    Sheets("Customer Price List").Protect
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ambiguous name detected (Excel 2002/2003)

    Are you sure that the selection is suitable for autofilter? If the selection is an isolated blank cell, you cannot apply AutoFilter to it.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: ambiguous name detected (Excel 2002/2003)

    Hi Hans

    It's not an isolated Cell, please attached

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ambiguous name detected (Excel 2002/2003)

    But what is the Selection when you run this macro?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ambiguous name detected (Excel 2002/2003)

    OK, got it. When the Worksheet_Deactivate event runs, Customer Price List is no longer the active sheet. So instead of Selection, refer to the worksheet explicitly:

    Worksheets("Customer Price List").Range("A1").AutoFilter Field:=1

    By the way, there is no need to unprotect and protect the worksheet in both CustListClear *and* in Worksheet_Deactivate

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: ambiguous name detected (Excel 2002/2003)

    Hi Hans
    This Filters the data:

    ' CustListSort Macro
    ' Macro recorded 11/07/2005 by Alan Bradshaw,

    '
    Sheets("Customer Price List").Unprotect
    Range("C21").AutoFilter
    ActiveSheet.Protect AllowFiltering:=True

    'If you want to turn on AutoFilter after protecting, you must also specify that you can still modify the worksheet through macros:

    ActiveSheet.Protect AllowFiltering:=True, UserInterfaceOnly:=True
    Range("C21").AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="<>"


    Sheets("Customer Price List").Protect
    End Sub

    and this clears the filter:

    Sub CustListClear()
    '
    ' CustListClear Macro
    ' Macro recorded 11/07/2005 by XX0824
    '

    '
    Sheets("Customer Price List").Unprotect
    Selection.AutoFilter Field:=1

    Sheets("Customer Price List").Protect
    End Sub

    I also have a macro that ouputs this sheet ot another file and it won't copy if its filtered, hence the reason for removing the filter when the sheet is closed

    Regard

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ambiguous name detected (Excel 2002/2003)

    If you want to remove the filter, you can use

    If Me.FilterMode Then
    Me.ShowAllData
    End If

    in the Worsheet_Deactivate event procedure.

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: ambiguous name detected (Excel 2002/2003)

    Hi Hans

    That's excellent, thanks for the help and the guidance.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: ambiguous name detected (Excel 2002/2003)

    Hi Hans

    I tried this but it stops here: Me.ShowAllData
    If Me.FilterMode Then
    Me.ShowAllData
    End If
    If you are a fool at forty, you will always be a fool

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ambiguous name detected (Excel 2002/2003)

    I think we'll have to see (a stripped down copy of) the workbook to know what is wrong this time.

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: ambiguous name detected (Excel 2002/2003)

    Hi Hans

    This works for me.

    Private Sub Worksheet_Deactivate()
    Application.ScreenUpdating = False
    Worksheets("Customer Price List").Unprotect
    Worksheets("Customer Price List").Range("A1").AutoFilter Field:=1
    Worksheets("Customer Price List").Protect
    Application.ScreenUpdating = True

    End Sub

    Regards

    Bradd7
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •