Macro fails after sharing workbook

G'Day All, I need some help from the masters please.

Explanation:

Worksheet "Sheet1" contains a listing of multiple addresses in columns containing "City" and "Street". This worksheet is protected to prevent users from changing the format,
layout, etc; plus to protect command buttons used for navigation and initiating other macros.

Worksheet "Sheet2" contains only temporary data (sourced from Sheet1) that is used as a lookup from a dropdown box.

This macro performs the following:

Sheet2 - cleared of all previous data
Sheet1 - unprotect sheet to allow for next actions to take place
Sheet1 - filters removed to ensure all data is displayed
Sheet1 - sort data to incorporate any new entries added since last sort
Sheet1 - because there are duplicate "cities" in the City column, the entries are filtered into unique values (to produce only one instance of each City), then copied to Sheet2.
This filtered list now on Sheet2 is later referenced by a dropdown box, with the output being displayed on a Userform.
Sheet1 - autofilter is restored and is protection turned back on

Sub TestMacro()
    
    Dim txt As String
    Dim i As Integer
    Dim DataSheet As Worksheet
    Set DataSheet = Sheets("Sheet1")
    Set ws4 = Worksheets("Sheet2")
    
    With ws4
        .Range("A1:B300").ClearContents
    End With
    
    With DataSheet
        .Unprotect
        .AutoFilterMode = False
    End With
    
    Range("A4:K300").Select
    Range("K300").Activate
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B4:B299"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A4:K299")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Range("B4").Select
       
    Sheets("Sheet1").Select
    Range(Range("B4"), Range("B4").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ThisWorkbook.Worksheets("Sheet2").Range("A2"), Unique:=True
    
    With DataSheet
        .Range("A3:K3").AutoFilter
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowDeletingRows:=True, AllowSorting:=True _
        , AllowFiltering:=True
    End With
        
End Sub

The macro works perfectly until the workbook is SHARED. Then it crashes due to the following:

Sheet PROTECTION cannot be turned OFF and ON when in shared state
The FILTERING to unique values cannot be performed when in shared state

Question: Is there another way to do these actions so that everything works when the workbook is in a shared state?

huumm, not good. I guess I can drop the sheet protection from the macro. Is there a way to at least lock down the command buttons on an unprotected sheet so that they do not get deleted or relocated by accident?

Common areas for everyone

Ideally the share would be required to make it multi-user editable without having to wait until another user closes it.

I believe I have solved the problem of the unique value portion of my code that doesn't work in the SHARE state by adapting
the "Compare" code provided elsewhere in the forum by Forum Contributor "cantosh" for user ncarrotine on 03-16-2016, 11:28 AM, under
the title "macro to compare and clear cells in two columns"

By first copying the non-static (changing) data on Sheet1, to the rows on the SAME COLUMN well BELOW the static data on Sheet2, I then run the Compare to separate out all the entries that do not appear on the Sheet2 list. I then SORT the entire range so that the new entries are incorporated and
alphabetised into the one list. i have tested it in the SHARE state and it works!

Here it is:

Sub Test()
    
    Dim j As Range
    Dim k As Range
    Dim txt As String
    Dim i As Integer
    Dim DataSheet As Worksheet
    Set DataSheet = Sheets("Sheet1")
    Set ws4 = Worksheets("Sheet2")
    
    Application.ScreenUpdating = False
    
    With ws4
        .Range("A301:A800").ClearContents
    End With
    
    With DataSheet
        .AutoFilterMode = False
    End With
    
    Range("A4:K300").Select
    Range("K300").Activate
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B4:B299"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A4:K299")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    With DataSheet
        .Range("B4:B300").Copy
        .Paste ws4.Range("A301")
    End With

    Sheets("Sheet2").Select
    For Each j In Range("A1:A300")
    If Application.WorksheetFunction.CountIf(ActiveSheet.Range("A301:A500"), j.Value) < 1 Then
        j.ClearContents
    End If
    Next j
    
    For Each k In Range("A301:A500")
    If Application.WorksheetFunction.CountIf(Range("A1:A300"), k.Value) > 0 Then
        k.ClearContents
    End If
    Next k
    
        Range("A2:A800").Select
        ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A2:A800") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet2").Sort
            .SetRange Range("A2:A800")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Range("A1").Select

    With DataSheet
        .Range("A3:K3").AutoFilter
    End With
        
    Sheets("Sheet2").Select
    Application.ScreenUpdating = True
     
End Sub

Thanks to all who assisted with this problem. In respect to the UNPROTECT-PROTECT issue in the SHARE state, I will leave that for now until I have either inspiration to find a way around it, or frustration and just remove protection all together.