Peter Chan
asked on
Problem to line
Hi,
I get problem
due to last line below
I get problem
due to last line below
Sub Unprotect_Workbook(Para_Message As String)
Application.ScreenUpdating = False
Worksheets("Main Sheet").Unprotect Password:="abc"
Worksheets("Month Status").Unprotect Password:="abc"
Worksheets("Code Sheet").Unprotect Password:="abc"
Worksheets("Code Sheet2").Unprotect Password:="abc"
Worksheets("Code Sheet3").Unprotect Password:="abc"
Worksheets("Code Sheet4").Unprotect Password:="abc"
Worksheets("Code Sheet5").Unprotect Password:="abc"
'Worksheets("Code Sheet6").Unprotect Password:="abc"
End Sub
Unprotect_Workbook Message0
...
Debug.Print CStr(RowID) & "zz" & CStr(ActiveSheet.ProtectContents)
Worksheets("Main Sheet").Cells(RowID, 5).Value = Right(Worksheets("Main Sheet").Cells(RowID, 4).Value, 3)
...
even if I've unprotected the current worksheet which is called "Main Sheet".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you attach the file
ASKER
Here is the file
Asset-Template-Test2.xlsm
Asset-Template-Test2.xlsm
When you run the Open event then Message0 does not have a value, hence the code is failing
What is the purpose of using Message0?
Unprotect_Workbook Message0
Refresh_Site_Code Country1
What is the purpose of using Message0?
ASKER
I put
instead of
but I still get the same problem to the same line.
Message0 = " ": Unprotect_Workbook Message0
instead of
Unprotect_Workbook Message0
but I still get the same problem to the same line.
You aren't answering my questions why are you using Message0?
You use the same variable name in several procedures called within procedures, so it is being reset when you don't want it to be. Use different variable names. I know I have said this in previous posts.
You should use Option Explicit to prevent mistakes, when I fix one error another occurs.
You should not use Application.ScreenUpdating all the time, you should restore it when you do use it.
I believe this is what you are trying to do
Take a look at the amendments. It works fine from the Open event now, but the sub procedures being run are re-protecting the sheets. You need to work your way through the code and check it line by line. Also, tidy it up. There are much tidier and simpler ways of coding.
Asset-Template-Test2.xlsm
You use the same variable name in several procedures called within procedures, so it is being reset when you don't want it to be. Use different variable names. I know I have said this in previous posts.
You should use Option Explicit to prevent mistakes, when I fix one error another occurs.
You should not use Application.ScreenUpdating
I believe this is what you are trying to do
Sub Unprotect_Workbook(Optional sPw As String)
' Application.ScreenUpdating = False
Worksheets("Main Sheet").Unprotect Password:=sPw
Worksheets("Month Status").Unprotect Password:=sPw
Worksheets("Code Sheet").Unprotect Password:=sPw
Worksheets("Code Sheet2").Unprotect Password:=sPw
Worksheets("Code Sheet3").Unprotect Password:=sPw
Worksheets("Code Sheet4").Unprotect Password:=sPw
Worksheets("Code Sheet5").Unprotect Password:=sPw
'Worksheets("Code Sheet6").Unprotect Password:=para_message
End Sub
Sub Protect_Workbook(Optional sPw As String)
' Application.ScreenUpdating = True
Worksheets("Main Sheet").Protect Password:=sPw
Worksheets("Month Status").Protect Password:=sPw
Worksheets("Code Sheet").Protect Password:=sPw
Worksheets("Code Sheet2").Protect Password:=sPw
Worksheets("Code Sheet3").Protect Password:=sPw
Worksheets("Code Sheet4").Protect Password:=sPw
Worksheets("Code Sheet5").Protect Password:=sPw
'Worksheets("Code Sheet6").Protect Password:=spw
End Sub
Take a look at the amendments. It works fine from the Open event now, but the sub procedures being run are re-protecting the sheets. You need to work your way through the code and check it line by line. Also, tidy it up. There are much tidier and simpler ways of coding.
Asset-Template-Test2.xlsm
ASKER
I did change the parameter of events like
Sub Unprotect_Workbook(Para_Message2 As String)
'Application.ScreenUpdating = False
...
End Sub
Sub Protect_Workbook(Para_Message3 As String)
'Application.ScreenUpdating = True
....
Sub Refresh_List(Para_Message5 As String)
...
but the problem persists, while there are not many other functions being called, when opening the file.
I've explained what your problem is. Using the same variable name - message0, in several procedures assigning different values. You need one string variable for the password, in fact I would use a Constant. Using that method removed the issue with the code.
ASKER
Hi,
In your attached file, I did not see code inside. Can you please point it out clearly where the problem is?
In your attached file, I did not see code inside. Can you please point it out clearly where the problem is?
I've used the sPw to hold the password, and tested it on the Open event of the workbook.
ASKER
Hi,
I did adjust the events like
I did adjust the events like
Sub Unprotect_Workbook(Optional Para_Message2 As String)
'Application.ScreenUpdating = False
Worksheets("Main Sheet").Unprotect Password:="abc"
Debug.Print "0227--"
Worksheets("Month Status").Unprotect Password:="abc"
Worksheets("Code Sheet").Unprotect Password:="abc"
Worksheets("Code Sheet2").Unprotect Password:="abc"
Worksheets("Code Sheet3").Unprotect Password:="abc"
Worksheets("Code Sheet4").Unprotect Password:="abc"
Worksheets("Code Sheet5").Unprotect Password:="abc"
'Worksheets("Code Sheet6").Unprotect Password:="abc"
End Sub
Sub Protect_Workbook(Optional Para_Message3 As String)
'Application.ScreenUpdating = True
Worksheets("Main Sheet").Protect Password:="abc"
Worksheets("Month Status").Protect Password:="abc"
Worksheets("Code Sheet").Protect Password:="abc"
Worksheets("Code Sheet2").Protect Password:="abc"
Worksheets("Code Sheet3").Protect Password:="abc"
Worksheets("Code Sheet4").Protect Password:="abc"
Worksheets("Code Sheet5").Protect Password:="abc"
'Worksheets("Code Sheet6").Protect Password:="abc"
End Sub
but the problem persists. Why?
How are you calling those procedures?
ASKER
Within Open event, I did not call Validate_List but do you know why I got problem to one line, (of event Validate_List), when opening file?
This is an unrelated question.
You should know how to debug code by now.
Click debug and the you will see the line is highlighted yellow. Hover the cursor over the line and you will see that Range3 is "a2:a0", this will obviously error because it is not a valid address, there is no row zero.
This will stop that error but your code is full of such problems.
You should know how to debug code by now.
Click debug and the you will see the line is highlighted yellow. Hover the cursor over the line and you will see that Range3 is "a2:a0", this will obviously error because it is not a valid address, there is no row zero.
This will stop that error but your code is full of such problems.
Dim Range3 As String
Range3 = "a2:a" & Trim(CStr(TotRow))
If TotRow < 1 Then Exit Sub
ASKER
Sorry, I am only reporting the problem, due to this line
when opening the file.
Worksheets("Main Sheet").Cells(RowID, 5).Value = Right(Worksheets("Main Sheet").Cells(RowID, 4).Value, 3)
when opening the file.
I don't get that error. The amendment that I made on your attachment stops the problem As I said this is unrelated to the original question is down to the way you write code. I have said before in previous questions that you need to write code that is more robust.
ASKER
Sorry, do you mean, everything is fine, when open the Excel file?
I was quite confused that I got problem to such line, within Share-point.
I was quite confused that I got problem to such line, within Share-point.
First time that you have mentioned Share Point.
I've told you what the issues are and made suggestions.
I've told you what the issues are and made suggestions.
ASKER
Sorry, can I know which comment it is?
You need to check your code line by line. Each time a problem is solved another arises, I am not checking it all. I think I have more than answeredthe original question.
ASKER
I did not get your clear comment to the error.
Read the thread properly, debug your code.
ASKER
What to check/adjust to the file, due to the current problem?