Link to home
Start Free TrialLog in
Avatar of Peter Chan
Peter ChanFlag for Hong Kong

asked on

Problem to line

Hi,
I get problem
User generated image
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)
        ...

Open in new window

even if I've unprotected the current worksheet which is called "Main Sheet".
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Peter Chan

ASKER

Hi,
What to check/adjust to the file, due to the current problem?
Can you attach the file
Here is the file
Asset-Template-Test2.xlsm
When you run the Open event then Message0 does not have a value, hence the code is failing


 
 Unprotect_Workbook Message0
    Refresh_Site_Code Country1

Open in new window


What is the purpose of using Message0?
I put

Message0 = " ": Unprotect_Workbook Message0

Open in new window


instead of

Unprotect_Workbook Message0

Open in new window


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

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

Open in new window


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
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)
    ...

Open in new window

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.
Hi,
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.
Hi,
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

Open in new window

but the problem persists. Why?
How are you calling those procedures?
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.

User generated image
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

Open in new window

Sorry, I am only reporting the problem, due to this line

Worksheets("Main Sheet").Cells(RowID, 5).Value = Right(Worksheets("Main Sheet").Cells(RowID, 4).Value, 3)

Open in new window


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.
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.
First time that you have mentioned Share Point.

I've told you what the issues are and made suggestions.
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.
I did not get your clear comment to the error.
Read the thread properly, debug your code.