0

I have a workbook with many worksheets. Some of them must be protected, and some of them unlocked. The tricky part is that a userform created using Excel VBA will be used to add data to one of the protected worksheets, and the code fails when said worksheet is protected because it is supposed to add rows to a table to save the data.

I tried using the following code to fix this:

Sheets("Sales").Protect Password:="123", AllowInsertingRows:=True, UserInterFaceOnly:=True

Although after running it I can manually add rows through the user interface, it seems that the property I am trying to use only works that way, VBA code still can not and new rows to the protected worksheet.

So, the workaround I thought of is to unprotect the worksheet when the userform is initialized, like this:

Private Sub UserForm_Initialize()
  Sheets("Sales").Unprotect "123"
End Sub

This way the userform can save all the data without any issues. However, my question is how to properly protected it again. There are two ways of closing the userform, one is with a cancel button that just runs the Unload Me piece of code, and the second one is using the X on the top right corner of the form. I would like to keep those two options available, but also make sure that the worksheet is protected again before the user can try to edit it (it is imposible to select anything with the userform open).

What I am thinking would be the best option is the following code:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  Sheets("Sales").Protect Password:="123"
End Sub

After testing it I believe it works, but I am not sure if this is the proper way of doing this. Maybe there is a specific situation that I am not considering that would end up with the user being able to edit the worksheet that is supposed to be protected. Could please let my know if there are any flaws in my code?

Fernanda
  • 51
  • 6
  • 1
    https://stackoverflow.com/a/14162717/11683? – GSerg Jul 31 '20 at 18:56
  • @GSerg I am sure that I am running the `UserInterFaceOnly:=True` property when required. The issue is that it only allows you to add data, not to modify the structure of the worksheet itself. Thank you for your suggestion anyway. – Fernanda Jul 31 '20 at 19:01
  • Use Qery_Close `Event` if you want checking something and stopping the close action, since it is triggered before the form has been closed, or Terminate `Event` if you only want to be triggered when the form is (already) closed. For your purpose, both of them will work. Query_close is fired before Terminate. – FaneDuru Jul 31 '20 at 19:15
  • @FaneDuru Great, thank you for the help! – Fernanda Jul 31 '20 at 22:33

0 Answers0