Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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("Protect ion").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("Protect ion").Enabled = False End If End Sub I cannot find the solution from other posts |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Tools Protection Protect Sheet for all users but one
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("Protect ion").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("Protect ion").Enabled = False End If End Sub I cannot find the solution from other posts -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Tools Protection Protect Sheet for all users but one
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("Protect ion").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("Protect ion").Enabled = False End If End Sub I cannot find the solution from other posts -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Tools Protection Protect Sheet for all users but one
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("Protect ion").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("Protect ion").Enabled = False End If End Sub I cannot find the solution from other posts -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Tools Protection Protect Sheet for all users but one
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("Protect ion").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("Protect ion").Enabled = False End If End Sub I cannot find the solution from other posts -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Tools Protection Protect Sheet for all users but one
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("Protect ion").Enabled = False End If End Sub --------- Private Sub Workbook_BeforeClose(Cancel As Boolean) If Application.UserName < "Administrator" Then Application.CommandBars("Tools").Controls("Protect ion").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("Protect ion").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("Protect ion").Enabled = False End If End Sub I cannot find the solution from other posts -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Tools Protection Protect Sheet for all users but one
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("Protect ion").Enabled = False End If End Sub --------- Private Sub Workbook_BeforeClose(Cancel As Boolean) If Application.UserName < "Administrator" Then Application.CommandBars("Tools").Controls("Protect ion").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("Protect ion").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("Protect ion").Enabled = False End If End Sub I cannot find the solution from other posts -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is there anyway to make it so the users of my excel spread sheet cant view the macro code w/o a password? | Excel Worksheet Functions | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
Protecting Workbook | Excel Discussion (Misc queries) | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |