Tonysdilemma
New Member
- Joined
- Aug 2, 2020
- Messages
- 30
- Office Version
- 2016
- Platform
- Windows
Hi All
I have a sheet to receive parcels. It is Sheet 7 named Parcels Received. on this sheet I have an excel table. When I protect sheet, I check Sort and Use AutoFilter. I also check Select unlocked cells and Protect worksheet and contents of locked cells. The filter works fine when Sheet 7 is protected.
I have another sheet named Parcels Collected 3, when it is time for a parcel to be picked up.
Sheet 7 and Parcels Collected 3 are linked.
When parcel is picked up, Parcels Collected 3 is saved to file.
After saving, the idea is to delete contents from certain cells from both Sheet 7 and Parcels Collected 3 and start afresh.
The VBA code I am using is as follows;
The problem I encounter occurs when I execute the VBA for Parcels Collected 3 (Module Parcels_Collected_3). Even though Sheet 7 is still protected, For some reason the Sort box and Use Autofilter box become unchecked and I can not filter the table on Sheet 7.
Any assistance would be appreciated
I have a sheet to receive parcels. It is Sheet 7 named Parcels Received. on this sheet I have an excel table. When I protect sheet, I check Sort and Use AutoFilter. I also check Select unlocked cells and Protect worksheet and contents of locked cells. The filter works fine when Sheet 7 is protected.
I have another sheet named Parcels Collected 3, when it is time for a parcel to be picked up.
Sheet 7 and Parcels Collected 3 are linked.
When parcel is picked up, Parcels Collected 3 is saved to file.
After saving, the idea is to delete contents from certain cells from both Sheet 7 and Parcels Collected 3 and start afresh.
The VBA code I am using is as follows;
VBA Code:
Sub SaveSheetToPDF5()
Dim ws As Worksheet
Set ws = ActiveSheet
Sheet7.UnProtect Password:="debra"
ws.UnProtect Password:="debra"
ws.Range("A1:I12").ExportAsFixedFormat xlTypePDF, Filename:= _
"C:\Users\Tonyg\Desktop\Altair Tony\Parcels Collected\" & ws.Range("A3").Value & ws.Range("B1").Value & ws.Range("D3"), Openafterpublish:=False
Sheet7.Range("B3:C3").ClearContents
Sheet7.Range("E3:G3").ClearContents
ws.Range("C6:E6").ClearContents
ws.Range("G7:H12").ClearContents
ws.Range("G7:H12").Delete
ws.Range("C9:E12").ClearContents
ws.Protect Password:="debra", UserInterfaceOnly:=True
Sheet7.Protect Password:="debra", UserInterfaceOnly:=True
Sheet7.Range("A3").Value = Sheet7.Range("A3").Value Mod 99 + 1.01
Sheet7.Protect Password:="debra", DrawingObjects:=False, Contents:=True, Scenarios:=True
Sheet7.Activate
End Sub
The problem I encounter occurs when I execute the VBA for Parcels Collected 3 (Module Parcels_Collected_3). Even though Sheet 7 is still protected, For some reason the Sort box and Use Autofilter box become unchecked and I can not filter the table on Sheet 7.
Any assistance would be appreciated