Locked cells are unlocked when I reopen worksheet

Abdulkhadar

Board Regular
Joined
Nov 10, 2013
Messages
165
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Do you have VBA code in the "ThisWorkbook" module or any of the Sheet modules?
If so, can you post it here?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top