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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
clarkscottConnect With a Mentor Commented:
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
 
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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
 
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
 
suvmitraAuthor Commented:
Thank you very much :)
0
 
clarkscottCommented:
You are welcome!
0
All Courses

From novice to tech pro — start learning today.