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

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

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

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

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

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