Allow VBA to modify cells in protected sheet

[eromilly]

I wrote a workbook and want protect the sheets from "people edit", but still allow vba code to update cell values and sort ranges, etc. Is this possible?

 

[doofusboy]

Sure, just unprotect and protect the sheet around your code; something like:

ActiveSheet.Unprotect
' your vba code here
ActiveSheet.Protect

 

[Jonmo1]

There are 2 ways...

1. Unprotect Sheet - run code - reprotect sheet

Sheets("Sheet1").Unprotect "passwordgoeshere"
'Do stuff
Sheets("Sheet1").Protect "passwordgoeshere"


2. UserInterfaceOnly option

Sheets("Sheet1").Protect Password:="passwordgoeshere", userinterfaceonly:=True



I prefer the 1st method because the userinterfaceonly option is NOT remembered upon save/close/reopen.
You have to reset that option each time the book is opened.

 

[ac66bronco]

Am I doing this right?

I checked it on a co-workers computer and it worked after she clicked on the enable editing pop up

But they went back in and it did not update.

Function GetUserName()
Sheets("Sheet1").Unprotect "123"
GetUserName = Application.UserName
Sheets("Sheet1").Protect "123"
End Function

Sorry I know its been dead 6 years

 

[Jonmo1]

You don't need to unprotect the sheet to get the application.username

 

[ac66bronco]

So just this should work?

Function GetUserName()
GetUserName = Application.UserName
End Function

Even with the cell being locked and the sheet being password protected?

 

[Jonmo1]

Which cell ?

Are you putting that in a cell as a formula, like in A1 you put =GetUserName()
?

In that case, the sheet needs to be unprotected by hand, then enter the formula in the cell, then reprotect the sheet.

 

[ac66bronco]

yes that is exactly what i am doing.

ugh. no vba work around magic???!?

 

[Jonmo1]

You could use a separate macro to do it (not contained within your custom function)

Sub EnterFormula()
Sheets("Sheet1").Unprotect "123"
Sheets("Sheet1").Range("A1").Formula = "=GetUserName()"
Sheets("Sheet1").Protect "123"
End Sub

 

[ac66bronco]

That worked beautifully! Thank you!