Disable Tools >Protection >Protect Sheet for all users but one

From other post I realize that you disable the menu item by:
Application.CommandBars("Tools").Controls("Protection").Enabled = False
but, I want to disable it for everyone unless you log on as Administrator.
I have tried the following, but it does not work.
Private Sub Workbook_Open()
If Application.UserName <> "Administrator" Then
Application.CommandBars("Tools").Controls("Protection").Enabled = False
End If
End Sub
I cannot find the solution from other posts

Application.username
returns the name defined in Tools|Options|general

Maybe using the windows logon id would be better:

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function

sub testme()
if lcase(fosusername) = "administrator" then
'do what you want
else
'don't do it
end if
end sub



Win XP wrote:
>
> From other post I realize that you disable the menu item by:
> Application.CommandBars("Tools").Controls("Protection").Enabled = False
> but, I want to disable it for everyone unless you log on as Administrator.
> I have tried the following, but it does not work.
> Private Sub Workbook_Open()
> If Application.UserName <> "Administrator" Then
> Application.CommandBars("Tools").Controls("Protection").Enabled = False
> End If
> End Sub
> I cannot find the solution from other posts

--

Dave Peterson

Thank you for your response.
I tested this, but it disabled the function whenever any excel program was
opened after this one. I just wanted to disable the >protect sheet< option
if the user was not "Administrator" on this one particular workbook, and
allow the option on other open workbooks.

"Dave Peterson" wrote:

> Application.username
> returns the name defined in Tools|Options|general
>
> Maybe using the windows logon id would be better:
>
> Option Explicit
> Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
> "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
> Function fOSUserName() As String
> ' Returns the network login name
> Dim lngLen As Long, lngX As Long
> Dim strUserName As String
> strUserName = String$(254, 0)
> lngLen = 255
> lngX = apiGetUserName(strUserName, lngLen)
> If lngX <> 0 Then
> fOSUserName = Left$(strUserName, lngLen - 1)
> Else
> fOSUserName = ""
> End If
> End Function
>
> sub testme()
> if lcase(fosusername) = "administrator" then
> 'do what you want
> else
> 'don't do it
> end if
> end sub
>
>
>
> Win XP wrote:
> >
> > From other post I realize that you disable the menu item by:
> > Application.CommandBars("Tools").Controls("Protection").Enabled = False
> > but, I want to disable it for everyone unless you log on as Administrator.
> > I have tried the following, but it does not work.
> > Private Sub Workbook_Open()
> > If Application.UserName <> "Administrator" Then
> > Application.CommandBars("Tools").Controls("Protection").Enabled = False
> > End If
> > End Sub
> > I cannot find the solution from other posts
>
> --
>
> Dave Peterson
>

Ahhh.

My suggestion didn't disable or enable the protection option. Your code did
that. I was just supplying a way to check the network id.

If you only want this option disabled for a certain workbook, then you'll have
to enable and disable it when you change workbooks.

There are a couple of workbook events that you'll want to tie into:

Private Sub Workbook_Activate()
and
Private Sub Workbook_Deactivate()

And maybe just drop that workbook_open code, totally.

Check the username in the _activate event. And just enable it in the
_deactivate event.



Win XP wrote:
>
> Thank you for your response.
> I tested this, but it disabled the function whenever any excel program was
> opened after this one. I just wanted to disable the >protect sheet< option
> if the user was not "Administrator" on this one particular workbook, and
> allow the option on other open workbooks.
>
> "Dave Peterson" wrote:
>
> > Application.username
> > returns the name defined in Tools|Options|general
> >
> > Maybe using the windows logon id would be better:
> >
> > Option Explicit
> > Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
> > "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
> > Function fOSUserName() As String
> > ' Returns the network login name
> > Dim lngLen As Long, lngX As Long
> > Dim strUserName As String
> > strUserName = String$(254, 0)
> > lngLen = 255
> > lngX = apiGetUserName(strUserName, lngLen)
> > If lngX <> 0 Then
> > fOSUserName = Left$(strUserName, lngLen - 1)
> > Else
> > fOSUserName = ""
> > End If
> > End Function
> >
> > sub testme()
> > if lcase(fosusername) = "administrator" then
> > 'do what you want
> > else
> > 'don't do it
> > end if
> > end sub
> >
> >
> >
> > Win XP wrote:
> > >
> > > From other post I realize that you disable the menu item by:
> > > Application.CommandBars("Tools").Controls("Protection").Enabled = False
> > > but, I want to disable it for everyone unless you log on as Administrator.
> > > I have tried the following, but it does not work.
> > > Private Sub Workbook_Open()
> > > If Application.UserName <> "Administrator" Then
> > > Application.CommandBars("Tools").Controls("Protection").Enabled = False
> > > End If
> > > End Sub
> > > I cannot find the solution from other posts
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Thank you,
I got it to disable when the workbook is opened and enable when the workbook
is closed based on the user. While the workbook is open if you use any other
excel book, the protect option is disabled. If you wanted to use the protect
option on another open workbook you would have to close the one with this
code. It looks like there is no way to have workbooks open at the same time,
and have one enabled and the other not. Or have this one open and select new
and have the new sheet have the option to protect while original workbook is
open.

"Dave Peterson" wrote:

> Ahhh.
>
> My suggestion didn't disable or enable the protection option. Your code did
> that. I was just supplying a way to check the network id.
>
> If you only want this option disabled for a certain workbook, then you'll have
> to enable and disable it when you change workbooks.
>
> There are a couple of workbook events that you'll want to tie into:
>
> Private Sub Workbook_Activate()
> and
> Private Sub Workbook_Deactivate()
>
> And maybe just drop that workbook_open code, totally.
>
> Check the username in the _activate event. And just enable it in the
> _deactivate event.
>
>
>
> Win XP wrote:
> >
> > Thank you for your response.
> > I tested this, but it disabled the function whenever any excel program was
> > opened after this one. I just wanted to disable the >protect sheet< option
> > if the user was not "Administrator" on this one particular workbook, and
> > allow the option on other open workbooks.
> >
> > "Dave Peterson" wrote:
> >
> > > Application.username
> > > returns the name defined in Tools|Options|general
> > >
> > > Maybe using the windows logon id would be better:
> > >
> > > Option Explicit
> > > Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
> > > "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
> > > Function fOSUserName() As String
> > > ' Returns the network login name
> > > Dim lngLen As Long, lngX As Long
> > > Dim strUserName As String
> > > strUserName = String$(254, 0)
> > > lngLen = 255
> > > lngX = apiGetUserName(strUserName, lngLen)
> > > If lngX <> 0 Then
> > > fOSUserName = Left$(strUserName, lngLen - 1)
> > > Else
> > > fOSUserName = ""
> > > End If
> > > End Function
> > >
> > > sub testme()
> > > if lcase(fosusername) = "administrator" then
> > > 'do what you want
> > > else
> > > 'don't do it
> > > end if
> > > end sub
> > >
> > >
> > >
> > > Win XP wrote:
> > > >
> > > > From other post I realize that you disable the menu item by:
> > > > Application.CommandBars("Tools").Controls("Protection").Enabled = False
> > > > but, I want to disable it for everyone unless you log on as Administrator.
> > > > I have tried the following, but it does not work.
> > > > Private Sub Workbook_Open()
> > > > If Application.UserName <> "Administrator" Then
> > > > Application.CommandBars("Tools").Controls("Protection").Enabled = False
> > > > End If
> > > > End Sub
> > > > I cannot find the solution from other posts
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Thank you,
I actually got the whole thing to work now. I'm not quite sure why this
wasn't working properly before. Thank you again for your help.

Private Sub Workbook_Open()
If Application.UserName <> "Administrator" Then
Application.CommandBars("Tools").Controls("Protection").Enabled = False
End If
End Sub
---------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.UserName <> "Administrator" Then
Application.CommandBars("Tools").Controls("Protection").Enabled = True
End If
End Sub


"Win XP" wrote:

> Thank you,
> I got it to disable when the workbook is opened and enable when the workbook
> is closed based on the user. While the workbook is open if you use any other
> excel book, the protect option is disabled. If you wanted to use the protect
> option on another open workbook you would have to close the one with this
> code. It looks like there is no way to have workbooks open at the same time,
> and have one enabled and the other not. Or have this one open and select new
> and have the new sheet have the option to protect while original workbook is
> open.
>
> "Dave Peterson" wrote:
>
> > Ahhh.
> >
> > My suggestion didn't disable or enable the protection option. Your code did
> > that. I was just supplying a way to check the network id.
> >
> > If you only want this option disabled for a certain workbook, then you'll have
> > to enable and disable it when you change workbooks.
> >
> > There are a couple of workbook events that you'll want to tie into:
> >
> > Private Sub Workbook_Activate()
> > and
> > Private Sub Workbook_Deactivate()
> >
> > And maybe just drop that workbook_open code, totally.
> >
> > Check the username in the _activate event. And just enable it in the
> > _deactivate event.
> >
> >
> >
> > Win XP wrote:
> > >
> > > Thank you for your response.
> > > I tested this, but it disabled the function whenever any excel program was
> > > opened after this one. I just wanted to disable the >protect sheet< option
> > > if the user was not "Administrator" on this one particular workbook, and
> > > allow the option on other open workbooks.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Application.username
> > > > returns the name defined in Tools|Options|general
> > > >
> > > > Maybe using the windows logon id would be better:
> > > >
> > > > Option Explicit
> > > > Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
> > > > "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
> > > > Function fOSUserName() As String
> > > > ' Returns the network login name
> > > > Dim lngLen As Long, lngX As Long
> > > > Dim strUserName As String
> > > > strUserName = String$(254, 0)
> > > > lngLen = 255
> > > > lngX = apiGetUserName(strUserName, lngLen)
> > > > If lngX <> 0 Then
> > > > fOSUserName = Left$(strUserName, lngLen - 1)
> > > > Else
> > > > fOSUserName = ""
> > > > End If
> > > > End Function
> > > >
> > > > sub testme()
> > > > if lcase(fosusername) = "administrator" then
> > > > 'do what you want
> > > > else
> > > > 'don't do it
> > > > end if
> > > > end sub
> > > >
> > > >
> > > >
> > > > Win XP wrote:
> > > > >
> > > > > From other post I realize that you disable the menu item by:
> > > > > Application.CommandBars("Tools").Controls("Protection").Enabled = False
> > > > > but, I want to disable it for everyone unless you log on as Administrator.
> > > > > I have tried the following, but it does not work.
> > > > > Private Sub Workbook_Open()
> > > > > If Application.UserName <> "Administrator" Then
> > > > > Application.CommandBars("Tools").Controls("Protection").Enabled = False
> > > > > End If
> > > > > End Sub
> > > > > I cannot find the solution from other posts
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

You may want to look at _activate and _deactivate instead.

Win XP wrote:
>
> Thank you,
> I actually got the whole thing to work now. I'm not quite sure why this
> wasn't working properly before. Thank you again for your help.
>
> Private Sub Workbook_Open()
> If Application.UserName <> "Administrator" Then
> Application.CommandBars("Tools").Controls("Protection").Enabled = False
> End If
> End Sub
> ---------
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> If Application.UserName <> "Administrator" Then
> Application.CommandBars("Tools").Controls("Protection").Enabled = True
> End If
> End Sub
>
> "Win XP" wrote:
>
> > Thank you,
> > I got it to disable when the workbook is opened and enable when the workbook
> > is closed based on the user. While the workbook is open if you use any other
> > excel book, the protect option is disabled. If you wanted to use the protect
> > option on another open workbook you would have to close the one with this
> > code. It looks like there is no way to have workbooks open at the same time,
> > and have one enabled and the other not. Or have this one open and select new
> > and have the new sheet have the option to protect while original workbook is
> > open.
> >
> > "Dave Peterson" wrote:
> >
> > > Ahhh.
> > >
> > > My suggestion didn't disable or enable the protection option. Your code did
> > > that. I was just supplying a way to check the network id.
> > >
> > > If you only want this option disabled for a certain workbook, then you'll have
> > > to enable and disable it when you change workbooks.
> > >
> > > There are a couple of workbook events that you'll want to tie into:
> > >
> > > Private Sub Workbook_Activate()
> > > and
> > > Private Sub Workbook_Deactivate()
> > >
> > > And maybe just drop that workbook_open code, totally.
> > >
> > > Check the username in the _activate event. And just enable it in the
> > > _deactivate event.
> > >
> > >
> > >
> > > Win XP wrote:
> > > >
> > > > Thank you for your response.
> > > > I tested this, but it disabled the function whenever any excel program was
> > > > opened after this one. I just wanted to disable the >protect sheet< option
> > > > if the user was not "Administrator" on this one particular workbook, and
> > > > allow the option on other open workbooks.
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Application.username
> > > > > returns the name defined in Tools|Options|general
> > > > >
> > > > > Maybe using the windows logon id would be better:
> > > > >
> > > > > Option Explicit
> > > > > Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
> > > > > "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
> > > > > Function fOSUserName() As String
> > > > > ' Returns the network login name
> > > > > Dim lngLen As Long, lngX As Long
> > > > > Dim strUserName As String
> > > > > strUserName = String$(254, 0)
> > > > > lngLen = 255
> > > > > lngX = apiGetUserName(strUserName, lngLen)
> > > > > If lngX <> 0 Then
> > > > > fOSUserName = Left$(strUserName, lngLen - 1)
> > > > > Else
> > > > > fOSUserName = ""
> > > > > End If
> > > > > End Function
> > > > >
> > > > > sub testme()
> > > > > if lcase(fosusername) = "administrator" then
> > > > > 'do what you want
> > > > > else
> > > > > 'don't do it
> > > > > end if
> > > > > end sub
> > > > >
> > > > >
> > > > >
> > > > > Win XP wrote:
> > > > > >
> > > > > > From other post I realize that you disable the menu item by:
> > > > > > Application.CommandBars("Tools").Controls("Protection").Enabled = False
> > > > > > but, I want to disable it for everyone unless you log on as Administrator.
> > > > > > I have tried the following, but it does not work.
> > > > > > Private Sub Workbook_Open()
> > > > > > If Application.UserName <> "Administrator" Then
> > > > > > Application.CommandBars("Tools").Controls("Protection").Enabled = False
> > > > > > End If
> > > > > > End Sub
> > > > > > I cannot find the solution from other posts
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >

--

Dave Peterson