Edoloto
11-05-2008, 08:25 AM
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
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