Write to a sheet if it is protected

[gmazza76]

Afternoon,

I have a spreadsheet that i have had to protect to stop people removing info of it. I also need the spreadsheet to be updated by a macro saving info from a userform to it.

Is there anyway i can proctect it using VB while allowing the userform to submit info to the same sheet.

I hope this makes sense.

Many Thanks
Gavin

 

[AlphaFrog]

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    ActiveSheet.Unprotect Password:="Secret"<br>        <SPAN style="color:#007F00">'Write data</SPAN><br>    ActiveSheet.Protect Password:="Secret"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Peter_SSs]

Is there anyway i can proctect it using VB while allowing the userform to submit info to the same sheet.

Yes. Protect the sheet from vba, not manually, like this.

Sub ProtectUIOnly()
    Sheets("Sheet9").Protect Password:="Secret", UserInterfaceOnly:=True
End Sub

Then your userform code doesn't need to Unprotect/Protect each time. It can just be like this

Private Sub CommandButton1_Click()
    'Write Data
End Sub

 

[gmazza76]

Good Afternoon,

Thanks for the information.
Where would i write the first part.
ie in the workbook initalise or in each sheet that needs protecting.

Cheers

 

[Peter_SSs]

Good Afternoon,

Thanks for the information.
Where would i write the first part.
ie in the workbook initalise or in each sheet that needs protecting.

Cheers

If you are referring to my suggestion then ..

1. Manually Unprotect the sheet.

2. Paste my first code in to a standard module (post back if you need specific directions for how to do that).

3. Edit the code to have the correct sheet name (where I have Sheet9).

4. Back in your workbook, press Alt+F8, select the ProtectUIOnly macro and click run.

Now other code should be able to write to the sheet without unprotecting/reprotecting each time.

 

[gmazza76]

Many Thanks,

I will have a look tonight and message you if i have any issue.

Cheers

 

[Peter_SSs]

I just noticed that the last line of my previous post had a typo which changed its meaning greatly.

I have fixed it now but previously it said
Now other code should be able to write to the sheet with unprotecting/reprotecting each time.
It now says
Now other code should be able to write to the sheet without unprotecting/reprotecting each time.