Marco that will run if the condition met
 

Hello

I need some help with this please. I want to create a Marco that will run if
the condition met.


A B
1 yes 100
2 yes 656
3 yes 556
4 yes 964
5 no 632
6 yes 496
7 no 656464
8 no 656
9 yes 656

For example
If A1 equal to €œYes€� then Cell B1 will be unprotected and if its equal to
€œNo€� then the cell will remain protected. So after the Marco, the Cell
B1,B2,B3,B4,B6,B9 will be unprotected.

Do I need to create a Marco button for this or would it be possible just for
the Marco to run when Cell A1 equal to Yes.

Thank you very much!!!

Harn

Marco that will run if the condition met
 

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Me.Unprotect myPassword
With Target

.Offset(0, 1).Locked = LCase(.Value) = "yes"
End With
Me.Protect myPassword
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Harn88" wrote in message
...
Hello

I need some help with this please. I want to create a Marco that will run
if
the condition met.


A B
1 yes 100
2 yes 656
3 yes 556
4 yes 964
5 no 632
6 yes 496
7 no 656464
8 no 656
9 yes 656

For example
If A1 equal to "Yes" then Cell B1 will be unprotected and if it's equal to
"No" then the cell will remain protected. So after the Marco, the Cell
B1,B2,B3,B4,B6,B9 will be unprotected.

Do I need to create a Marco button for this or would it be possible just
for
the Marco to run when Cell A1 equal to Yes.

Thank you very much!!!

Harn

Marco that will run if the condition met
 

Hi Bob

Thank you very much for your reply. Your Code work great but it is nearly
what I wanted. From my understanding, your code in the last e mail is when
input €œyes€�, the whole sheet will protect.

A B
1 yes 100
2 yes 656
3 yes 556
4 yes 964
5 no 632
6 yes 496
7 no 656464
8 no 656
9 yes 656


I would like to apology for not making the email clearer last time, below is
what I am trying to said in my last e mail.

My situation is the worksheet is already protected by my password (for
example my password is €œLucky€�) however what I want is that if A1 equal to
€œYes€� then Cell B1 will be unprotected only. The other Cell in the worksheet
will remain protected and if it is equal to €œNo€� then the cell will remain
protected. So after the Marco, the Cell B1,B2,B3,B4,B6,B9 will be unprotected
only. i hope I make more sense this time.

Thank you very much for your help!!!

Best regards,

Harn


"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Me.Unprotect myPassword
With Target

.Offset(0, 1).Locked = LCase(.Value) = "yes"
End With
Me.Protect myPassword
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Harn88" wrote in message
...
Hello

I need some help with this please. I want to create a Marco that will run
if
the condition met.


A B
1 yes 100
2 yes 656
3 yes 556
4 yes 964
5 no 632
6 yes 496
7 no 656464
8 no 656
9 yes 656

For example
If A1 equal to "Yes" then Cell B1 will be unprotected and if it's equal to
"No" then the cell will remain protected. So after the Marco, the Cell
B1,B2,B3,B4,B6,B9 will be unprotected.

Do I need to create a Marco button for this or would it be possible just
for
the Marco to run when Cell A1 equal to Yes.

Thank you very much!!!

Harn

Marco that will run if the condition met
 

Hi Bob

The example did not come out too great, number 1 to 9 is row number and A&B
is column. Sorry for the Confusion. So "yes" will be in Row 1 Column A and
the amount will bein Row 1 column B

Row/Column A B
1 yes 100
2 yes 656
3 yes 556
4 yes 964
5 no 632
6 yes 496
7 no 656464
8 no 656
9 yes 656

Thank you

best regards,

Harn

"Harn88" wrote:

Hi Bob

Thank you very much for your reply. Your Code work great but it is nearly
what I wanted. From my understanding, your code in the last e mail is when
input €œyes€�, the whole sheet will protect.

A B
1 yes 100
2 yes 656
3 yes 556
4 yes 964
5 no 632
6 yes 496
7 no 656464
8 no 656
9 yes 656


I would like to apology for not making the email clearer last time, below is
what I am trying to said in my last e mail.

My situation is the worksheet is already protected by my password (for
example my password is €œLucky€�) however what I want is that if A1 equal to
€œYes€� then Cell B1 will be unprotected only. The other Cell in the worksheet
will remain protected and if it is equal to €œNo€� then the cell will remain
protected. So after the Marco, the Cell B1,B2,B3,B4,B6,B9 will be unprotected
only. i hope I make more sense this time.

Thank you very much for your help!!!

Best regards,

Harn


"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Me.Unprotect myPassword
With Target

.Offset(0, 1).Locked = LCase(.Value) = "yes"
End With
Me.Protect myPassword
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Harn88" wrote in message
...
Hello

I need some help with this please. I want to create a Marco that will run
if
the condition met.


A B
1 yes 100
2 yes 656
3 yes 556
4 yes 964
5 no 632
6 yes 496
7 no 656464
8 no 656
9 yes 656

For example
If A1 equal to "Yes" then Cell B1 will be unprotected and if it's equal to
"No" then the cell will remain protected. So after the Marco, the Cell
B1,B2,B3,B4,B6,B9 will be unprotected.

Do I need to create a Marco button for this or would it be possible just
for
the Marco to run when Cell A1 equal to Yes.

Thank you very much!!!

Harn