firasath
08-02-2017, 06:26 AM
Hi,
I have multiple sheets which requires Password Protection. Protecting & Unprotecting each sheet every time is very tedious process. Hence, I have put a below VBA code to protect/unprotect all sheets together in a Module.
Sub UnProtectAll()
Dim wSheet As Worksheet
Dim Pwd As String
Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
On Error Resume Next
For Each wSheet In Worksheets
wSheet.UnProtect Password:=Pwd
Next wSheet
If Err <> 0 Then
MsgBox "You have entered an incorrect password. Please try again.", vbCritical, "Incorrect Password"
End If
On Error GoTo 0
End Sub
Sub ProtectAll()
Dim wSheet As Worksheet
Dim Pwd As String
Pwd = InputBox("Enter Password to Prtotect all Sheets", "Password Input")
For Each wSheet In Worksheets
wSheet.Protect Password:=Pwd, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True
Next wSheet
End Sub
I have one more code which I have put in each sheet which makes a button to float with the current selection on the sheet. The Code is listed below.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
ActiveSheet.UnProtect Password:="pgdb"
On Error GoTo 0
With Cells(Windows(1).ScrollRow, Windows(1).ScrollColumn)
GoToIndex.Top = .Top + 0
GoToIndex.Left = .Left + 900
End With
ActiveSheet.Protect Password:="pgdb", _
DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
The above code is working fine and the button is moving with the change in location on the sheet in case if I protect/unprtotect sheets individually without code. But with the code to protect/unprotect all sheets, the button location is not changing.
Please advise how can I make the macro work with the macro to protect/unprtect all sheets.
I have multiple sheets which requires Password Protection. Protecting & Unprotecting each sheet every time is very tedious process. Hence, I have put a below VBA code to protect/unprotect all sheets together in a Module.
Sub UnProtectAll()
Dim wSheet As Worksheet
Dim Pwd As String
Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
On Error Resume Next
For Each wSheet In Worksheets
wSheet.UnProtect Password:=Pwd
Next wSheet
If Err <> 0 Then
MsgBox "You have entered an incorrect password. Please try again.", vbCritical, "Incorrect Password"
End If
On Error GoTo 0
End Sub
Sub ProtectAll()
Dim wSheet As Worksheet
Dim Pwd As String
Pwd = InputBox("Enter Password to Prtotect all Sheets", "Password Input")
For Each wSheet In Worksheets
wSheet.Protect Password:=Pwd, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True
Next wSheet
End Sub
I have one more code which I have put in each sheet which makes a button to float with the current selection on the sheet. The Code is listed below.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
ActiveSheet.UnProtect Password:="pgdb"
On Error GoTo 0
With Cells(Windows(1).ScrollRow, Windows(1).ScrollColumn)
GoToIndex.Top = .Top + 0
GoToIndex.Left = .Left + 900
End With
ActiveSheet.Protect Password:="pgdb", _
DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
The above code is working fine and the button is moving with the change in location on the sheet in case if I protect/unprtotect sheets individually without code. But with the code to protect/unprotect all sheets, the button location is not changing.
Please advise how can I make the macro work with the macro to protect/unprtect all sheets.