Did LostFocus ever exist or is just a dream?!

[uglyvb]

Hi all,
I get stuck again in macro in Excel...What I need is fairly simple:
Immediately after a cell, say C6, lost focus, a corresponding cell, say
C12 is getting updated with the same/new value as C6. I tried to use
SelectionChange as follows:

Public Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim RNQVal As Integer
Dim RowVal As Integer
Dim ColVal As Integer

' Now get the row, column number and the value of the RNQ cell
(analogous to C6 in the description)
RowVal = Target.Row
ColVal = Target.Column

If (ColVal = 7 And RowVal > 25) Then
' validate the value just entered into the cell
RNQVal = Target.Value

' Set the corresponding DNQ cell(analogous to C12 in the
description) with the same value
Cells(RowVal, ColVal + 6).Locked = False
Cells(RowVal, ColVal + 6).Value = RNQVal
Cells(RowVal, ColVal + 6).Locked = True

End If
End Sub

But the above code snippet does not do exactly as expected. It does NOT
update C12 immediately after moving the cursor from C6, but does so when
C6 regains the focus afterwards. I searched the Internet almost for the
whole day trying to get through, but I just cannot!! There's no helpful
information at all on LostFocus, though it seems like what I need. I
could not even find any code sample on LostFocus either
....Any help would be highly appreciated. Thanks in advance!!

 

[Guest]

Immediately after a cell, say C6, lost focus, a corresponding cell, say

C12 is getting updated with the same/new value as C6.

Here's one way...

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
Static Origin As Range
On Error Resume Next
If Origin.Address = "$C$6" Then
Range("C12") = Origin.Value
End If
Set Origin = Target
End Sub

I'm presuming there's a valid reason you've chosen not to use a simple
worksheet based formula to achieve the same result.


Regards,
Vic Eldridge

 

[kounoike]

Hi

Would this one be some hints for you?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static bfrng As Range
Static bfval
On Error Resume Next
If bfrng.Value <> bfval Then
bfrng.Offset(6, 0) = bfrng.Value
End If
Set bfrng = Target
bfval = Target.Value
End Sub

keizi

 

[uglyvb]

Thanks to both Vic and Keizi for your kind help, especially with the
code sample! I finally get the synchronisation between two closely
related cells done by using basic Excel fomulae(Thanks Vic!). Was
hijacked by Macro and could not jump out of it I guess....
Although the problem has been solved, I still would like to know how
this can be worked out in Macro.
I have tried to adapted it to my case, which becomes:

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
Static Origin As Range
Dim RNQVal As Integer
Dim RowVal As Integer
Dim ColVal As Integer
Dim newColVal As Integer

On Error Resume Next

RowVal = Origin.Row
ColVal = Origin.Column
newColVal = ColVal + 6

' React only to certain cells
If (ColVal = 7 And RowVal > 25) Then
RNQVal = Origin.Value

Cells(RowVal, newColVal).Value = Origin.Value
End If
Set Origin = Target
End Sub

It is not working properly. So what did I do wrong again?
I am not sure what the following commands do:
1. On Error Resume Next
What does it do? My guess is that in case of error(what kind of
error???), the rest of the method will be totally skipped. Correct?

2. Static declaration
After some research online, static declaration functions similar to
normal procedure-level ones(see
http://support.microsoft.com/default.aspx?scid=kb;en-us;843144#XSLTH3233121122120121120120)
So can I say 'Static' in our declaration is optional?

Can someone please elaborate on the puzzles haunting in my head?

Thanks & regards to you both
Frank

 

[kounoike]

Hi

It is not working properly. So what did I do wrong again?

i wonder how your code does'nt work properly?

I am not sure what the following commands do:
1. On Error Resume Next
What does it do? My guess is that in case of error(what kind of
error???), the rest of the method will be totally skipped. Correct?

When this Macro run for the first time, code like RowVal = Origin.Row cause
an error because Origin isn't set to any range. so On Error Resume Next
ignore the error and make it possible to run the next code, not skip.

2. Static declaration
After some research online, static declaration functions similar to
normal procedure-level ones(see
http://support.microsoft.com/default.aspx?scid=kb;en-us;843144#XSLTH3233121122120121120120)
So can I say 'Static' in our declaration is optional?

not optional, you need to declare Static for the macro working properly.
without this, when this macro run at second time, Origin can not retain
previous range.

keizi

 

[uglyvb]

thank you keizi for the explanation! but im still a bit confused why the
code after adaption(pls see post #4) from the examples just does not
work? i ran it in debugging mode and noticed 'cells(rowval,
newcolval).value = origin.value' is not working properly cos, after
execution, cells(rowval, newcolval) still retains the old value. but
there may be some other things going wrong..can you please advise the
possible mistakes? thanks in advance for your diagnosis.

 

[kounoike]

Hi

i might not be understood correctly what you want to do.
in my thought, this code would do the same thing as your code do.
so this will be also not working properly in your sheet. Right?
Then what is the unexpected behaviour this code cause?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static Origin As Range
On Error Resume Next
If Origin.Column = 7 And Origin.Row > 25 Then
Origin.Offset(0, 6) = Origin.Value
End If
Set Origin = Target
End Sub

keizi

 

[uglyvb]

Thank you Keizi for your reply. Your code works perfectly! I tried out
mine and it seems also working this time...Not sure what happened the
other day when it did not work at all...the unexpected behaviour was
nothing happened when moving the cursor. Probably because I had a bad
day and did not do it properly at the end. My apologies. Thanks again
for your help and wish I can learn some more on macro from you/this
forum in the future.

Frank