Hi everyone,

I am trying to apply a conditional formula to a cell B1. Basically what
will happen is that

If A1 = "Good" Then
B1 = A2
//then lock B1
Else
//make B1 editable


I have done the first step using IF(A1 = "Good", A2, "") [not sure
about "" is suitable though] and setting cell property - tick 'Locked'
option. But how can the lock be released in the Else situation?

I wonder the neat way to do this is to code this in VB(similar to the
syntax above) rather than using Excel menu tools. But how can it make
sure that any changes to A1 will lead to INSTANT adjustment(To make
sure it happens IMMEDIATELY, should I put such a piece of code at a
certain place?) to B1, i.e. (either =A2 and then locked, or simply make
B1 editable as a normal cell).

Sorry Im relatively new to excel macro and the question above is
probably quite basic. Appreciate help of any kind.

Regards
Frank

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False
If Target.Address = "$A$1" Then
Me.Unprotect
Range("B1").Locked = False
If Target.Value = "Good" Then
Range("B1").Value = Range("A2").Value
Range("B1").Locked = True
End If
Me.Protect
End If

ws_exit:
Application.EnableEvents = True
On Error GoTo 0
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 Phillips

(remove nothere from the email address if mailing direct)

"OrientalPearl" wrote in message
oups.com...
Hi everyone,

I am trying to apply a conditional formula to a cell B1. Basically what
will happen is that

If A1 = "Good" Then
B1 = A2
//then lock B1
Else
//make B1 editable


I have done the first step using IF(A1 = "Good", A2, "") [not sure
about "" is suitable though] and setting cell property - tick 'Locked'
option. But how can the lock be released in the Else situation?

I wonder the neat way to do this is to code this in VB(similar to the
syntax above) rather than using Excel menu tools. But how can it make
sure that any changes to A1 will lead to INSTANT adjustment(To make
sure it happens IMMEDIATELY, should I put such a piece of code at a
certain place?) to B1, i.e. (either =A2 and then locked, or simply make
B1 editable as a normal cell).

Sorry Im relatively new to excel macro and the question above is
probably quite basic. Appreciate help of any kind.

Regards
Frank

Thanks Bob. I have adapted and pasted it into VB pane of the
corresponding sheet. However, the Worksheet_Change is not activated(The
breakpoint set at 'Private Sub Worksheet_Change....' is not even
reached) when moving the cursor out of A2 after modifying its value
....B1 is not updated according to A1 and A2 as expected. Can I somehow
trigger such an event when certain cell, say A2, loses focus? That
might be helpful.

Regards
Frank

Frank,

If you aren't even entering it, you must have events turned off somehow. In
the immediate window, do

Application.EnableEvents = True

I have coded it so that it fires on a change in A1, is that not what is
wanted?


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"OrientalPearl" wrote in message
oups.com...
Thanks Bob. I have adapted and pasted it into VB pane of the
corresponding sheet. However, the Worksheet_Change is not activated(The
breakpoint set at 'Private Sub Worksheet_Change....' is not even
reached) when moving the cursor out of A2 after modifying its value
...B1 is not updated according to A1 and A2 as expected. Can I somehow
trigger such an event when certain cell, say A2, loses focus? That
might be helpful.

Regards
Frank

Thanks again Bob.

Not sure what does the 'immediate' window mean...Sorry.

In your code supplied previously, the 2nd line in the Sub
Worksheet_Change body is 'Application.EnableEvents = False', which
disables events handling? I have changed it back to 'True' and tried
again but the problem still remains. Value and subsequent focus changes
among cells do not trigger any updating or other events.

Well I have to say B1 is thus populated all depending on both A1 and
A2, with A1 controls whether or not/how and A2 determines the actual
value to put into B1.

That is just error handling, change it back as supplied, then goto the VBIDE
(Alt-F11), and invoke the immediate window (Ctrl-G), then enter that code,
and go back and try again by changing A1

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"OrientalPearl" wrote in message
oups.com...
Thanks again Bob.

Not sure what does the 'immediate' window mean...Sorry.

In your code supplied previously, the 2nd line in the Sub
Worksheet_Change body is 'Application.EnableEvents = False', which
disables events handling? I have changed it back to 'True' and tried
again but the problem still remains. Value and subsequent focus changes
among cells do not trigger any updating or other events.

Well I have to say B1 is thus populated all depending on both A1 and
A2, with A1 controls whether or not/how and A2 determines the actual
value to put into B1.

Thank you very much Bob. You instructions have been followed and the
problem has been resolved.
Apart from the problem itself, I would also rather figure out (and
hopefully learn) a bit more on what I did not and do not know...

In terms of immediate window, I searched Excel help facility for it but
did not get much insights at all. So basically, when should I use an
immediate window and what can it do besides enabling events?
Regarding general macro programming, should I always run
'Application.EnableEvents = True' in the immediate window to make sure
events are enabled? I thought they are enabled as default. Does the
event invocation command work at workbook or excel level?

I have adapted your code(thanks again) in my case, which when EITHER a
user selects an option from a dropdown list OR certain cell, say A10,
loses focus, an event handler will assess the situation to determine
population of B10. In case of focus change in A10, it also acts
differently in accordance with the current entry from the dropdown
list. So such an event handler is associated with both the dropdown
list and focus change(placed at the sheet level), to make sure both
behaviours can be captured. I am not sure whether it's a smart move but
it simply does the job as far as I can see from now.

Thanks again Bob for your kind help and best regards!
Frank