In VBA, which relates to the ability to sort/autofilter?

[TheJay]

I am using the following code:

Option Explicit

Private Sub Worksheet_Activate()
    Range("C4").Select
    With Worksheets("VO Areas")
    With ActiveWindow
        .DisplayFormulas = False
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
    End With
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
    End With
    With Application
        .CommandBars("Full Screen").Visible = True
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
End With
End Sub

I have three columns that I want to be able to use the dropdown list on to be able to sort. If I define a range to allow users to edit, they can change the text in the headings but it does not allow the dropdown lists to be used. If I unprotect the worksheet and then lock it again with "Allow all users of this workbook to:" and tick "Sort" and "Use AutoFilter", it works. I save and it works. Then, I close and open the file again and the ability is lost and the options have been unticked.

Can someone please tell me how I can stop these options being disabled?

 

[Alex Blakenburg]

I think you are going to have to post this as a separate thread. It seems likely that you will have to share the workbook, its just too much code to wade through especially with not having any sample data to work with also doesn't help.
Also missing is, which actual line of code it is failing on (and a copy of the error message) ? Which protection method is being applied to the sheet "Search" ?

 

[TheJay]

I think you are going to have to post this as a separate thread. It seems likely that you will have to share the workbook, its just too much code to wade through especially with not having any sample data to work with also doesn't help.
Also missing is, which actual line of code it is failing on (and a copy of the error message) ? Which protection method is being applied to the sheet "Search" ?

I think you are right, I will need to spend some time stripping it in order to be able to share it. I'll crosslink once I have done it in case you find time to help.

Thank you very much.

 

[TheJay]

Contacts search results produce error when email address is clicked

The workbook functions well, except if someone clicks on an email address on the search results page. It brings up the above error and I either need to stop the error coming up so that it doesn't stop the whole workbook functioning (and actually brings up an email as you would expect when...

www.mrexcel.com