Prev: How Do I DO IF with DATE
Next: Comparing Lists
From: iperlovsky on 3 Jul 2008 12:43
Does anyone know how to change the password using VBA for the entire workbook
from "x" to nothing - so that the user is not prompted to enter a password
next time the workbook is opened?

Thanks...
From: Gary Brown on 3 Jul 2008 12:54
You'll need the password, but here's some code I've had that you can adapt.

'/============================================/
Private Sub ProtectPswd()
'template for unprotecting/protecting worksheet
Dim blnProtectContents As Boolean
Dim blnProtectDrawingObjects As Boolean
Dim blnProtectScenarios As Boolean
Dim strPassword As String

'set default for whether worksheet is protected or not
blnProtectContents = False
blnProtectDrawingObjects = False
blnProtectScenarios = False

'check if worksheet unprotected
' if it's protected, get various information
On Error Resume Next
If Application.ActiveSheet.ProtectContents = True Then
blnProtectContents = True
If Application.ActiveSheet.ProtectDrawingObjects = True Then
blnProtectDrawingObjects = True
End If
If Application.ActiveSheet.ProtectScenarios = True Then
blnProtectScenarios = True
End If
'protected so try password
strPassword = InputBox("Enter Password: " & vbCr & vbCr & _
"If there is no password, press ENTER." & vbCr & vbCr & _
"ONLY enter Password if source of this macro is TRUSTED!!!", _
"Password to Unprotect Worksheet...", "")
ActiveSheet.Unprotect password:=strPassword
'password didn't work - still not unprotected so stop process
If Application.ActiveSheet.ProtectContents = True Then
Exit Sub
End If
End If
On Error GoTo 0

' ++++++++++++++++++++++ put coding here +++++++++++++++++

'set worksheet back to original protected/unprotected state
On Error Resume Next
ActiveSheet.Protect password:=strPassword, _
DrawingObjects:=blnProtectDrawingObjects, _
Contents:=blnProtectContents, Scenarios:=blnProtectScenarios

End Sub
'/============================================/

--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown


"iperlovsky" wrote:

> Does anyone know how to change the password using VBA for the entire workbook
> from "x" to nothing - so that the user is not prompted to enter a password
> next time the workbook is opened?
>
> Thanks...
From: iperlovsky on 3 Jul 2008 13:02
Is this for a workbook or a worksheet. It looks like it is for a worksheet.

"Gary Brown" wrote:

> You'll need the password, but here's some code I've had that you can adapt.
>
> '/============================================/
> Private Sub ProtectPswd()
> 'template for unprotecting/protecting worksheet
> Dim blnProtectContents As Boolean
> Dim blnProtectDrawingObjects As Boolean
> Dim blnProtectScenarios As Boolean
> Dim strPassword As String
>
> 'set default for whether worksheet is protected or not
> blnProtectContents = False
> blnProtectDrawingObjects = False
> blnProtectScenarios = False
>
> 'check if worksheet unprotected
> ' if it's protected, get various information
> On Error Resume Next
> If Application.ActiveSheet.ProtectContents = True Then
> blnProtectContents = True
> If Application.ActiveSheet.ProtectDrawingObjects = True Then
> blnProtectDrawingObjects = True
> End If
> If Application.ActiveSheet.ProtectScenarios = True Then
> blnProtectScenarios = True
> End If
> 'protected so try password
> strPassword = InputBox("Enter Password: " & vbCr & vbCr & _
> "If there is no password, press ENTER." & vbCr & vbCr & _
> "ONLY enter Password if source of this macro is TRUSTED!!!", _
> "Password to Unprotect Worksheet...", "")
> ActiveSheet.Unprotect password:=strPassword
> 'password didn't work - still not unprotected so stop process
> If Application.ActiveSheet.ProtectContents = True Then
> Exit Sub
> End If
> End If
> On Error GoTo 0
>
> ' ++++++++++++++++++++++ put coding here +++++++++++++++++
>
> 'set worksheet back to original protected/unprotected state
> On Error Resume Next
> ActiveSheet.Protect password:=strPassword, _
> DrawingObjects:=blnProtectDrawingObjects, _
> Contents:=blnProtectContents, Scenarios:=blnProtectScenarios
>
> End Sub
> '/============================================/
>
> --
> Hope this helps.
> If this post was helpfull, please remember to click on the ''''YES''''
> button at the bottom of the screen.
> Thanks,
> Gary Brown
>
>
> "iperlovsky" wrote:
>
> > Does anyone know how to change the password using VBA for the entire workbook
> > from "x" to nothing - so that the user is not prompted to enter a password
> > next time the workbook is opened?
> >
> > Thanks...
From: Gary Brown on 3 Jul 2008 13:08
Sorry, misread the post. The code IS for a worksheet.
--
Gary Brown


"iperlovsky" wrote:

> Is this for a workbook or a worksheet. It looks like it is for a worksheet.
>
> "Gary Brown" wrote:
>
> > You'll need the password, but here's some code I've had that you can adapt.
> >
> > '/============================================/
> > Private Sub ProtectPswd()
> > 'template for unprotecting/protecting worksheet
> > Dim blnProtectContents As Boolean
> > Dim blnProtectDrawingObjects As Boolean
> > Dim blnProtectScenarios As Boolean
> > Dim strPassword As String
> >
> > 'set default for whether worksheet is protected or not
> > blnProtectContents = False
> > blnProtectDrawingObjects = False
> > blnProtectScenarios = False
> >
> > 'check if worksheet unprotected
> > ' if it's protected, get various information
> > On Error Resume Next
> > If Application.ActiveSheet.ProtectContents = True Then
> > blnProtectContents = True
> > If Application.ActiveSheet.ProtectDrawingObjects = True Then
> > blnProtectDrawingObjects = True
> > End If
> > If Application.ActiveSheet.ProtectScenarios = True Then
> > blnProtectScenarios = True
> > End If
> > 'protected so try password
> > strPassword = InputBox("Enter Password: " & vbCr & vbCr & _
> > "If there is no password, press ENTER." & vbCr & vbCr & _
> > "ONLY enter Password if source of this macro is TRUSTED!!!", _
> > "Password to Unprotect Worksheet...", "")
> > ActiveSheet.Unprotect password:=strPassword
> > 'password didn't work - still not unprotected so stop process
> > If Application.ActiveSheet.ProtectContents = True Then
> > Exit Sub
> > End If
> > End If
> > On Error GoTo 0
> >
> > ' ++++++++++++++++++++++ put coding here +++++++++++++++++
> >
> > 'set worksheet back to original protected/unprotected state
> > On Error Resume Next
> > ActiveSheet.Protect password:=strPassword, _
> > DrawingObjects:=blnProtectDrawingObjects, _
> > Contents:=blnProtectContents, Scenarios:=blnProtectScenarios
> >
> > End Sub
> > '/============================================/
> >
> > --
> > Hope this helps.
> > If this post was helpfull, please remember to click on the ''''YES''''
> > button at the bottom of the screen.
> > Thanks,
> > Gary Brown
> >
> >
> > "iperlovsky" wrote:
> >
> > > Does anyone know how to change the password using VBA for the entire workbook
> > > from "x" to nothing - so that the user is not prompted to enter a password
> > > next time the workbook is opened?
> > >
> > > Thanks...
 | 
Pages: 1
Prev: How Do I DO IF with DATE
Next: Comparing Lists