Sorting a table in a protected sheet when some cells are locked -MACRO needed

[GMC The Macro Man]

Hi Guys, I'm looking for some help here

I have a large Data Table with some of the columns LOCKED to protect the formulas in them. The worksheet is also protected so the users cannot "screw up" the formulas.
The issue i have is when the users add new data to the bottom rows, they cannot DATA SORT it as the sheet is protected .
I have read through this forum and many youtube sites and it seems you cannot DATA SORT on a protected sheet so a MACRO is needed.
I created a MACRO to
1. Un-protect the worksheet with the password
2. Run the DATA SORT
3. Protect the sheet with the password.
The issue here is when I run it, i get a pop up to input the password. Is it possible to embed the password in to the macro? Also, I would need the password to be hidden from the users. Is that possible too?
Many Thanks
GMC

 

[RoryA]

You can allow sorting on a protected sheet. You can't, however, add rows to a table on a protected sheet, so I'm a little confused by your question.

As regards your macro, the one argument that you can pass to Unprotect is the password so just add that to the code.

 

[GMC The Macro Man]

You can allow sorting on a protected sheet. You can't, however, add rows to a table on a protected sheet, so I'm a little confused by your question.

As regards your macro, the one argument that you can pass to Unprotect is the password so just add that to the code.

Hi RoryA, thanks for coming back to me.
I have a few columns in this table that have formulas so firstly I "Lock and Hide" theses cells using the "Protection" within the "Format Cells" option.

I then have to protect the worksheet as the columns with the formulas need to be locked but most importantly hidden as I do not want the client to see my calculations.
The client can add data at the first available row at bottom of the table as normal but the issue i have is not to SORT or FILTER as I can already do this, but it is to DATA SORT the table so it puts in the order according to the screenshot below
The sheet is sorted by Year Group/Class/Forename.

I hope this makes sense

Wishaw TEST Macro.xlsm
	A	B	C	D	E	F
8	SCN	Forename	Surname	Full Name	Year Group	Class
9	241474577	AJ	Parker Sinclair	AJ Parker Sinclair	P1	P1
10	241499499	Alison	Sangha	Alison Sangha	P1	P1
11	241499405	Allegra	Macdonald	Allegra Macdonald	P1	P1
12	241499472	Alyth	Port	Alyth Port	P1	P1
13	241499561	Amelia	Reid	Amelia Reid	P1	P1
14	241499340	Anna-Rose	Younis	Anna-Rose Younis	P1	P1
15	241499553	Aryah	Lafferty	Aryah Lafferty	P1	P1
16	241499308	Ava	Love	Ava Love	P1	P1
17	241499480	Ayrton	Sinclair	Ayrton Sinclair	P1	P1
18	241499421	Blair	Kennedy	Blair Kennedy	P1	P1
Pupil Data

Cell Formulas
Range	Formula
D9:D18	D9	=IF([@Forename]&[@Surname]="","",[@Forename]&" "&[@Surname])

 

[RoryA]

I'm still confused as a table on a protected sheet will not automatically expand to include new data, but I think I already answered your macro question anyway.