Excel Cell Lost Focus

Hi,
I need a VBA which will execute if I press a tab or arrow (Lost Focus?) on cell D9...Whatever value D9 contains it should get incremented by 1...

So if D9 contains value 10 and I press tab after selecting cell D9 it should change the value as 11 ... and repeats the same if I repeat the action... Please help.
LVL 9
suvmitraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

clarkscottCommented:
Create a variable in the worksheets declaration section

dim vVal as double

In the worksheets SelectionChange event - paste this code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If vVal > 0 Then
    vVal = vVal + 1
    Range("$D$9").Value = vVal
    vVal = 0
End If

If Target.Address = "$D$9" Then

    vVal = Target.Value
End If

End Sub

scott c
0
clarkscottCommented:
further...
don't paste the 1st or last lines.
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)"
or
"END SUB"

SCOTT C
0
suvmitraAuthor Commented:
This is working fine ... but I also need a procedure which will be re-setting the values to the original value .. is that possible?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

clarkscottCommented:
Are you talking about the original value when you first open the sheet?
0
suvmitraAuthor Commented:
Yes SCOTT..just want to have the value before running that procedure...and also I noticed even if I am changing manually the previous value .. it is still incrementing with the value stored in the variable..
So if I ran that procedure thrice on the value 26 ... next time whether I change the value again to 26 .. it is giving me 30..
0
clarkscottCommented:
Well, you didn't state that in your question.....
give me a minute to figure it out.
0
clarkscottCommented:
Scenario....
If you manually change the value - you must move to another cell to actually 'set the value' in D9.
Therefore, we won't want to auto increment if it's a MANUAL change.
But, if you focus on this cell and then leave the cell without manually changing, it should increment.
Correct?
Scott C
0
suvmitraAuthor Commented:
Correct Scott .. Your procedure is exactly what I wanted.. but now we need a reset procedure for this..so if perhaps some thing went wrong we can rset and have the original value..
0
clarkscottCommented:
this code should go into the sheet's   CHANGE event.

If Target.Address = "$D$9" Then
    vVal = 0
End If

Now, for the RESET you will have to INSERT A MODULE.
In the declaration section of this new module....

public glbOriginalVal as double

Save the module (FILE-SAVE)

Now, in the WORKSHEET - ACTIVATE event  type:
glbOriginalVal = Range("$D$9").Value

In the WORKSHEET- DEACTIVATE event type:
Range("$D$9").Value = glbOriginalVal

This will track the D9 value when you click on the sheet.  As soon as you click OFF the sheet (to another sheet), the D9 value will return to it's original value.

Scott C
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
suvmitraAuthor Commented:
Thank you very much :)
0
clarkscottCommented:
You are welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.