Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet Protection
I wrote a Micro to change something is DataValidation window with a shortcut
key ctrl-F. When I protect the sheet I am unable to use that micro. I even tried allowing all the functions that are listed in protection window and still nothing. Is there a way to get pass that? Or is DataValidation just LOCKED when you protect the sheet. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet Protection
A lot of functions cannot be changed on a protected sheet.
Usually you provide code to unprotect, do what you want then re-protect. ActiveSheet.Unprotect Password:="justme" do things ActiveSheet.Protect Password:="justme" Post the macro code that you wrote to change something in DataValidation. Gord Dibben MS Excel MVP On Tue, 4 Aug 2009 07:50:02 -0700, Damian wrote: I wrote a Micro to change something is DataValidation window with a shortcut key ctrl-F. When I protect the sheet I am unable to use that micro. I even tried allowing all the functions that are listed in protection window and still nothing. Is there a way to get pass that? Or is DataValidation just LOCKED when you protect the sheet. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet Protection
OK here is my Code:
Sub CustomCell() ' ' CustomCell Macro ' Macro recorded 8/4/2009 by IT Department ' ' Keyboard Shortcut: Ctrl+f ' With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub Basically it clears the drop down cell so custom data can be entered rather then picked from drop down menu. So when I put your command in it will work? Thanks "Gord Dibben" wrote: A lot of functions cannot be changed on a protected sheet. Usually you provide code to unprotect, do what you want then re-protect. ActiveSheet.Unprotect Password:="justme" do things ActiveSheet.Protect Password:="justme" Post the macro code that you wrote to change something in DataValidation. Gord Dibben MS Excel MVP On Tue, 4 Aug 2009 07:50:02 -0700, Damian wrote: I wrote a Micro to change something is DataValidation window with a shortcut key ctrl-F. When I protect the sheet I am unable to use that micro. I even tried allowing all the functions that are listed in protection window and still nothing. Is there a way to get pass that? Or is DataValidation just LOCKED when you protect the sheet. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet Protection
Sub CustomCell()
' ' CustomCell Macro ' Macro recorded 8/4/2009 by IT Department ' ' Keyboard Shortcut: Ctrl+f ' ActiveSheet.Unprotect Password:="justme" With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, _ AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With With ActiveSheet .Protect Password:="justme" .EnableSelection = xlNoRestrictions End With End Sub Gord On Tue, 4 Aug 2009 09:01:01 -0700, Damian wrote: OK here is my Code: Sub CustomCell() ' ' CustomCell Macro ' Macro recorded 8/4/2009 by IT Department ' ' Keyboard Shortcut: Ctrl+f ' With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub Basically it clears the drop down cell so custom data can be entered rather then picked from drop down menu. So when I put your command in it will work? Thanks "Gord Dibben" wrote: A lot of functions cannot be changed on a protected sheet. Usually you provide code to unprotect, do what you want then re-protect. ActiveSheet.Unprotect Password:="justme" do things ActiveSheet.Protect Password:="justme" Post the macro code that you wrote to change something in DataValidation. Gord Dibben MS Excel MVP On Tue, 4 Aug 2009 07:50:02 -0700, Damian wrote: I wrote a Micro to change something is DataValidation window with a shortcut key ctrl-F. When I protect the sheet I am unable to use that micro. I even tried allowing all the functions that are listed in protection window and still nothing. Is there a way to get pass that? Or is DataValidation just LOCKED when you protect the sheet. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet Protection
Works Like a charm. Thank you.
One last thing. I want the user to only select unlocked cells and not locked cells. How do I wright that for the restrictions? In the Protect Sheet window under: Allow all users of this worksheet to: (the only thing checked is:) Select unlocked cells Thank you in advance "Gord Dibben" wrote: Sub CustomCell() ' ' CustomCell Macro ' Macro recorded 8/4/2009 by IT Department ' ' Keyboard Shortcut: Ctrl+f ' ActiveSheet.Unprotect Password:="justme" With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, _ AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With With ActiveSheet .Protect Password:="justme" .EnableSelection = xlNoRestrictions End With End Sub Gord On Tue, 4 Aug 2009 09:01:01 -0700, Damian wrote: OK here is my Code: Sub CustomCell() ' ' CustomCell Macro ' Macro recorded 8/4/2009 by IT Department ' ' Keyboard Shortcut: Ctrl+f ' With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub Basically it clears the drop down cell so custom data can be entered rather then picked from drop down menu. So when I put your command in it will work? Thanks "Gord Dibben" wrote: A lot of functions cannot be changed on a protected sheet. Usually you provide code to unprotect, do what you want then re-protect. ActiveSheet.Unprotect Password:="justme" do things ActiveSheet.Protect Password:="justme" Post the macro code that you wrote to change something in DataValidation. Gord Dibben MS Excel MVP On Tue, 4 Aug 2009 07:50:02 -0700, Damian wrote: I wrote a Micro to change something is DataValidation window with a shortcut key ctrl-F. When I protect the sheet I am unable to use that micro. I even tried allowing all the functions that are listed in protection window and still nothing. Is there a way to get pass that? Or is DataValidation just LOCKED when you protect the sheet. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet Protection
Replace the other line with.............
..EnableSelection = xlUnlockedCells Gord On Tue, 4 Aug 2009 10:24:02 -0700, Damian wrote: Works Like a charm. Thank you. One last thing. I want the user to only select unlocked cells and not locked cells. How do I wright that for the restrictions? In the Protect Sheet window under: Allow all users of this worksheet to: (the only thing checked is:) Select unlocked cells Thank you in advance "Gord Dibben" wrote: Sub CustomCell() ' ' CustomCell Macro ' Macro recorded 8/4/2009 by IT Department ' ' Keyboard Shortcut: Ctrl+f ' ActiveSheet.Unprotect Password:="justme" With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, _ AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With With ActiveSheet .Protect Password:="justme" .EnableSelection = xlNoRestrictions End With End Sub Gord On Tue, 4 Aug 2009 09:01:01 -0700, Damian wrote: OK here is my Code: Sub CustomCell() ' ' CustomCell Macro ' Macro recorded 8/4/2009 by IT Department ' ' Keyboard Shortcut: Ctrl+f ' With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub Basically it clears the drop down cell so custom data can be entered rather then picked from drop down menu. So when I put your command in it will work? Thanks "Gord Dibben" wrote: A lot of functions cannot be changed on a protected sheet. Usually you provide code to unprotect, do what you want then re-protect. ActiveSheet.Unprotect Password:="justme" do things ActiveSheet.Protect Password:="justme" Post the macro code that you wrote to change something in DataValidation. Gord Dibben MS Excel MVP On Tue, 4 Aug 2009 07:50:02 -0700, Damian wrote: I wrote a Micro to change something is DataValidation window with a shortcut key ctrl-F. When I protect the sheet I am unable to use that micro. I even tried allowing all the functions that are listed in protection window and still nothing. Is there a way to get pass that? Or is DataValidation just LOCKED when you protect the sheet. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet protection | Excel Discussion (Misc queries) | |||
Sheet protection | Excel Discussion (Misc queries) | |||
sheet Protection | Excel Discussion (Misc queries) | |||
sheet protection | Excel Discussion (Misc queries) | |||
Sheet Protection | Excel Discussion (Misc queries) |