Protect / unprotect a shared workbook through VBA

mamidwar

New Member
Joined
Aug 13, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I have use VBA to protect and unprotect workbook and shared that sheet in network to use by multiuser and all user given access as per requirement by VBA code.
whenever workbook is unshared then that program run but when i was shared workbook then the below mentioned error comes please give me code

run time error 1004
method unprotect of object _workbook failed

please help me to solve the issue my program is mentioned below please correct it.
login form
Private Sub CommandButton1_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("User Management")

If Me.txt_UserName.Value = "" Then
MsgBox "Please enter the User name", vbCritical
Exit Sub
End If

If Me.txt_Password.Value = "" Then
MsgBox "Please enter the password", vbCritical
Exit Sub
End If


If Application.WorksheetFunction.CountIf(sh.Range("A:A"), Me.txt_UserName.Value) = 0 Then
MsgBox "User name does not exists", vbCritical
Exit Sub
End If

Dim user_row As Integer

user_row = Application.WorksheetFunction.Match(Me.txt_UserName.Value, sh.Range("A:A"), 0)

If CStr(sh.Cells(user_row, 3).Value) <> Me.txt_Password.Value Then
MsgBox "Invalid password", vbCritical
Exit Sub
End If

'''''''''''''' Check the Worksheet Access '''''''''''''''

Dim lock_worksheet, unlock_worksheet As Integer

lock_worksheet = Application.WorksheetFunction.CountIf(sh.Range("D" & user_row, "XFD" & user_row), "Ï")
unlock_worksheet = Application.WorksheetFunction.CountIf(sh.Range("D" & user_row, "XFD" & user_row), "Ð")

If sh.Cells(user_row, 2).Value <> "Admin" Then
If (lock_worksheet + unlock_worksheet) = 0 Then
MsgBox "You don't have the access for any worksheet, please contact with admin", vbCritical
Exit Sub
End If
End If


''''''''''''' Apply setting ''''''''
Dim wsh As Worksheet
Dim i As Integer

If sh.Cells(user_row, 2).Value = "Admin" Then '''' Admin role
sh.Unprotect 78520
sh.Cells.EntireColumn.Hidden = False
sh.Cells.EntireRow.Hidden = False

ThisWorkbook.Unprotect 78520
For Each wsh In ThisWorkbook.Worksheets
wsh.Visible = xlSheetVisible
wsh.Unprotect 78520
Next

Else '''''for User Role

ThisWorkbook.Unprotect 78520

For i = 5 To Application.WorksheetFunction.CountA(sh.Range("2:2"))
Set wsh = ThisWorkbook.Sheets(sh.Cells(2, i).Value)

If sh.Cells(user_row, i).Value = "x" Then
wsh.Visible = xlSheetVeryHidden
ElseIf sh.Cells(user_row, i).Value = "Ð" Then
wsh.Visible = xlSheetVisible
wsh.Unprotect 78520
ElseIf sh.Cells(user_row, i).Value = "Ï" Then
wsh.Visible = xlSheetVisible
wsh.Protect 78520
End If

Next i

sh.Visible = xlSheetVeryHidden
ThisWorkbook.Protect 78520

End If

ActiveWindow.DisplayWorkbookTabs = True

Unload Me

End Sub



Private Sub CommandButton2_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("User Management")

If Me.txt_UserName.Value = "" Then
MsgBox "Please enter the User name", vbCritical
Exit Sub
End If

If Me.txt_Password.Value = "" Then
MsgBox "Please enter the old password", vbCritical
Exit Sub
End If


If Application.WorksheetFunction.CountIf(sh.Range("A:A"), Me.txt_UserName.Value) = 0 Then
MsgBox "User name does not exists", vbCritical
Exit Sub
End If

Dim user_row As Integer

user_row = Application.WorksheetFunction.Match(Me.txt_UserName.Value, sh.Range("A:A"), 0)

If CStr(sh.Cells(user_row, 3).Value) <> Me.txt_Password.Value Then
MsgBox "Invalid current password", vbCritical
Exit Sub
End If

With frm_Password_Reset
.txt_UserName.Value = Me.txt_UserName.Value
.txt_User_Row.Value = user_row
.Show False
End With

End Sub

Private Sub UserForm_Activate()

Dim sh As Worksheet
Dim wsh As Worksheet

ThisWorkbook.Unprotect 78520

Set sh = ThisWorkbook.Sheets("User Management")
sh.Visible = xlSheetVisible

For Each wsh In ThisWorkbook.Worksheets
If wsh.Name <> "User Management" Then
wsh.Visible = xlSheetVeryHidden
End If
Next


sh.Unprotect 78520
sh.Cells.EntireColumn.Hidden = True
sh.Cells.EntireRow.Hidden = True
sh.Protect 78520

ActiveWindow.DisplayWorkbookTabs = False

ThisWorkbook.Protect 78520

End Sub
password reset



Private Sub CommandButton1_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("User Management")

If Me.txt_Password.Value = "" Then
MsgBox "Please enter new password", vbCritical
Exit Sub
End If

sh.Unprotect 78520
sh.Cells(Me.txt_User_Row.Value, 3).Value = Me.txt_Password.Value
sh.Protect 78520

frm_Login.Show False
frm_Login.txt_UserName.Value = ""
frm_Login.txt_Password.Value = ""
Unload Me

MsgBox "Password has been reset!!!", vbInformation

End Sub



Private Sub CommandButton2_Click()

frm_Login.Show False
Unload Me




End Sub

Private Sub UserForm_Activate()

Dim sh As Worksheet
Dim wsh As Worksheet

ThisWorkbook.Unprotect 78520

Set sh = ThisWorkbook.Sheets("User Management")
sh.Visible = xlSheetVisible

For Each wsh In ThisWorkbook.Worksheets
If wsh.Name <> "User Management" Then
wsh.Visible = xlSheetVeryHidden
End If
Next


sh.Unprotect 78520
sh.Cells.EntireColumn.Hidden = True
sh.Cells.EntireRow.Hidden = True
sh.Protect 78520

ActiveWindow.DisplayWorkbookTabs = False

ThisWorkbook.Protect 78520




End Sub
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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