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