Locked cells are unlocked when I reopen worksheet

[Abdulkhadar]

Hello Excel Experts,
I have a worksheet that is protected by password using VB. The cells in sheets some cells are locked and some are unlocked. This seem to work fine until I close the worksheet. When I reopen the sheet the cells are unlocked automatically but sheet is protected.
Thanks in advance.

 

[Joe4]

Do you have VBA code in the "ThisWorkbook" module or any of the Sheet modules?
If so, can you post it here?

 

[Abdulkhadar]

Do you have VBA code in the "ThisWorkbook" module or any of the Sheet modules?
If so, can you post it here?

Thank for quick reply sir,
I have use below code in module

Sub CheckUser()
Dim UserRow, SheetCol As Long
Dim SheetNm As String
With Sheet1
.Calculate

If .Range("B5").Value = Empty Then 'incorrect Username
MsgBox "Please enter a correct user name"
Exit Sub
End If

If .Range("B7").Value <> True Then 'incorrect password
MsgBox "Please enter a correct password"
Exit Sub
End If

LoginForm.Hide

.Range("B6").Value = ""
MsgBox "WEL COME, SELECT YOUR CLASS SHEET BELOW AND ENTER INFORMATION"

UserRow = .Range("B8").Value 'User row

For SheetCol = 8 To 200
SheetNm = .Cells(4, SheetCol).Value 'sheet Name

If .Cells(UserRow, SheetCol).Value = "Ð" Then
Sheets(SheetNm).Unprotect "danish"
Sheets(SheetNm).Visible = xlSheetVisible

End If

If .Cells(UserRow, SheetCol).Value = "Ï" Then
Sheets(SheetNm).Protect "danish"
Sheets(SheetNm).Visible = xlSheetVisible
End If
If .Cells(UserRow, SheetCol).Value = "x" Then Sheets(SheetNm).Visible = xlVeryHidden
Next SheetCol
End With

End Sub

 

[Joe4]

What triggers this code to run, or is it being manually called?

What I am really after is code in the specific "ThisWorkbook" or "Sheet" modules.
Code in this modules typically automatically runs on some event happening, like the opening of your file, or selecting of a particular sheet.
Is there any code in these specific modules?

 

[Abdulkhadar]

What triggers this code to run, or is it being manually called?

What I am really after is code in the specific "ThisWorkbook" or "Sheet" modules.
Code in this modules typically automatically runs on some event happening, like the opening of your file, or selecting of a particular sheet.
Is there any code in these specific modules?

This Workbook module following vb are there
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.DisplayFullScreen = False
.CommandBars("Full Screen").Visible = False
.CommandBars("Worksheet Menu Bar").Enabled = True
End With
CloseWorkbook
End Sub


Private Sub Workbook_Activate()
Call ToggleCutCopyAndPaste(False)
End Sub

Private Sub Workbook_Deactivate()
Call ToggleCutCopyAndPaste(True)
End Sub


There are 52 Sheets some sheets have following code

Private Sub CommandButton1_Click()
ActiveSheet.PageSetup.PrintArea = "$BG$1:$BR$" & Cells(Rows.Count, "BG").End(xlUp).Row
Sheets("VIIIB").PrintOut
End Sub


Thanks

 

[Joe4]

It looks like you have a bunch of automated code running. I am guessing that is where your problem lies.
It is probably just a matter of tracking down the right procedure and see what it is doing.
What does your code for the "ToggleCutCopyAndPaste" procedure look like? That is a prime candidate.

 

[Abdulkhadar]

It looks like you have a bunch of automated code running. I am guessing that is where your problem lies.
It is probably just a matter of tracking down the right procedure and see what it is doing.
What does your code for the "ToggleCutCopyAndPaste" procedure look like? That is a prime candidate.

Thanks for your patience sir,
there is code at module which disable cut paste option in worksheet

 

[Joe4]

It seems like you have an awful lot of code interacting here.
Here is how I would probably approach it. I would search my entire VBA Project (all modules) for the word "Lock" or "Locked", and add breakpoints there, or message boxes saying something to the effect of "Lock/Unlocking cells".
Then, go about your normal steps of saving and closing your workbook, and then opening it up again.
Because of the breakpoint/MsgBox, it should alert you when it hits those lines. So you can inspect and see what code is running when and what it is doing.
Then just click the Run button to continue on with the code, and see what else it hits.

Hopefully, this will highlight to you when and where your cells are becoming unlocked.

 

[Abdulkhadar]

It seems like you have an awful lot of code interacting here.

Hopefully, this will highlight to you when and where your cells are becoming unlocked.

Ok Thanks for your help i will try.
thanks once again

 

[Joe4]

You are welcome.