Lock Drop down Menu
 

Hello!
I made a drop down menu(DataValidation...), I want that menu to be locked
after a specific value is chosen from the list,
There is 3 options :
1-Open
2-Closed
3-On Hold

I want the cell to be locked after "closed" is choosen, and one more thing I
want colors !

Lock Drop down Menu
 

Hi Mubarak

1----By locking if you mean not to allow the user to change the value then
try the below macro. Right click the sheet tab, View code and paste the code.
I am assuming you have the drop down in Column D

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("D:D")) Is Nothing Then
If Target.Text = "Closed" Then Target.Offset(, 1).Select
End If
Application.EnableEvents = True
End Sub

2----For coloring the status use Conditional Formatting. Selec1. Select the
column/Range. From menu FormatConditional Formatting
For Condition1Select 'Cell value' select 'equal to' enter 'Closed'. Click
Format ButtonPattern and select your color. You can add condition2 and
condition 3 for the other status if you need.. and once done Hit OK.


If this post helps click Yes
---------------
Jacob Skaria


"Mubarak" wrote:

Hello!
I made a drop down menu(DataValidation...), I want that menu to be locked
after a specific value is chosen from the list,
There is 3 options :
1-Open
2-Closed
3-On Hold

I want the cell to be locked after "closed" is choosen, and one more thing I
want colors !

Lock Drop down Menu
 

Dear Jacob,

In case I want to re-open the locked ticket whats the needed code

Thanks in Advance

"Jacob Skaria" wrote:

Hi Mubarak

1----By locking if you mean not to allow the user to change the value then
try the below macro. Right click the sheet tab, View code and paste the code.
I am assuming you have the drop down in Column D

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("D:D")) Is Nothing Then
If Target.Text = "Closed" Then Target.Offset(, 1).Select
End If
Application.EnableEvents = True
End Sub

2----For coloring the status use Conditional Formatting. Selec1. Select the
column/Range. From menu FormatConditional Formatting
For Condition1Select 'Cell value' select 'equal to' enter 'Closed'. Click
Format ButtonPattern and select your color. You can add condition2 and
condition 3 for the other status if you need.. and once done Hit OK.


If this post helps click Yes
---------------
Jacob Skaria


"Mubarak" wrote:

Hello!
I made a drop down menu(DataValidation...), I want that menu to be locked
after a specific value is chosen from the list,
There is 3 options :
1-Open
2-Closed
3-On Hold

I want the cell to be locked after "closed" is choosen, and one more thing I
want colors !