Extrange Protection Behavior

[Edoloto]

Greetings!

I have a big, big issue with protection. This is my scenario.

1- I have two spreadsheets "Allocation Calcualtions" & "Allocation Expenses"

2- The two spreadsheets have a button that is called "Add New Vendor" that call UserForm1.

3- When they click the "Add New Vendor" Button in any of the two spreadsheets, the following code will run as I call this method from both spreadsheets button objects.

Code from spreadsheets (it's the same in both the spreadsheets) button:

Private Sub btnAddNewVendor_Click()
    UnProtectSheet
    UserForm1.Show
End Sub

Code in module

Sub UnProtectSheet()
    'UnProtect Spreadsheet
    ActiveSheet.Protect Scenarios:=False, UserInterfaceOnly:=True
End Sub

4- The form appears, the users do what they have to do, and then they click the "Close Form" button (Remember that this form is accessed by both the spreadsheet) where the following code will run:

Code From Button:

Private Sub btnCloseForm_Click()
ProtectSheet (Page)
Unload Me
End Sub

Code in Module

Sub ProtectSheet(Page As String)
'Protect SpreadSheet
    Dim Test As String
    Test = CStr(ActiveSheet.Name)
    ActiveSheet.Protect Scenarios:=True, UserInterfaceOnly:=True
    UnlockWorkingCells (Page)
End Sub

Sub UnlockWorkingCells(Page As String)
    'Unlock the cells where user will input data
    If Page = "Allocation Calculations" Then
        Worksheets(Page).Range("D5:F90").Locked = False
    Else
        'Allocation Expenses
        Worksheets(Page).Range("D5:K90").Locked = False
    End If
End Sub

Where "Page" is actually the name of the spreadsheet ... Are you following me?

Now, this works perfectly for the "Allocation Calcualtions" spreadsheet, but it does not work for the "Allocation Expenses" spreadsheet.

Now, believe me, I have created a variable "Test" that was equal to ActiveSheet.Name to see if really the active sheet is the correct sheet. ALso, remember that I'm passign the "Page" parameter which also contains the name of the spreadsheet.

Why would this code only work for one spreadsheet and not for the other, knowing that the parameters passed are correct and I see that both spreadsheets (when they are active) are being passed correctly?

Please, some insights would be greatly appreciate it.

Thank you,

Eduardo

[GTO]

Greetings Eduardo,

Presuming you copied the code accurately from your project, the first thing I noted was that you don't actually pass the argument "Page", leastwise not so as I could see it. While its a bit foggy to me what is supposed to be going on, if you already have assigned a Value to Page before clicking the button, then that could be your first problem. Maybe?

Try substituting ActiveSheet.Name like:

[VBA]Private Sub CommandButton1_Click()
'ProtectSheet (Page)
ProtectSheet ActiveSheet.Name
Unload Me
End Sub[/VBA]

After that, I suspect you may not be showing us all that is supposed to be happening, cuz as far as I can tell, the overall effect of the userform is simply that the sheet gets left protected, with a range of cells unlocked.


Are you actually trying to leave the range locked between calls to the userform?

Hope this helps,

Mark