Call Up A Macro When Exiting A Cell

[Minitman]

Greetings,

I would like a macro to run when I leave a certain cell

Example: I enter in a date in C5. I hit ENTER and would like Macro2
to start running.

Is this possible?

Any help would be appreciated.

TIA

-Minitman

 

[Rob van Gelder]

Right-click the sheet tab and select View Code

Paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5")) Is Nothing Then
Macro2
End If
End Sub

 

[Minitman]

Hey Rob,

Thanks for the assistance, it is getting me closer to where I need to
be. I tried this code and it works, mostly,

However, I may have been too vague in what I was trying to accomplish.

Here is what I an trying to do:
I click on a button to refresh my form (the code goes to sheet of
lists, "Lists", and copies one of two forms that I am using (which
button I click on determines which form is copied). It then goes back
to the main sheet, "Enter", and does a paste to overwrite the existing
form with the fresh one). The code then stops in cell C5
(Range("C4").Select). I then type in an invoice number and hit
<Enter>. This is when I need that macro to run (it calls up a
UserForm for more decisions).

The code below works up until the form is pasted, then it fires
automatically without waiting for an entry or the <Enter> key to be
pressed.

Any ideas on how to modify this code?

-Minitman

 

[Steve Garman]

Does this do what you're looking for?

Dim prevCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If prevCell Is Nothing Then
Set prevCell = Target
Exit Sub
End If
If Not Intersect(Target, Range("C5")) Is Nothing Then
'still in C5
Else
If Not Intersect(prevCell, Range("C5")) Is Nothing Then
MsgBox "You have left C5"
End If
End If
Set prevCell = Target
End Sub

 

[Steve Garman]

Sorry, that was messy. It looks better as:

Dim prevcell As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If prevcell Is Nothing Then
Set prevcell = Target
Exit Sub
End If
If Intersect(Target, Range("C5")) Is Nothing Then
If Not Intersect(prevcell, Range("C5")) Is Nothing Then
MsgBox "You have left C5"
End If
End If
Set prevcell = Target
End Sub

 

[Minitman]

Hey Steve,

That does indeed do the trick. All I had to do was change the MsgBox
with a call to my UserForm and it works perfectly.

Thank you very much.

-Minitman

 

[Minitman]

Hey Steve,

A slight problem, for some reason this seems to be calling up my
UserForm twice in quick succession when I choose the NONE choice.

The code for the NONE choice is:

Private Sub InvoiceProblem_None_Option_Click()
Sheets("Enter").Select
Range("AG2").Select
Unload Me
End Sub

(AG2 is the date cell)

This is how I modified your code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If prevcell Is Nothing Then
Set prevcell = Target
Exit Sub
End If
If Intersect(Target, Range("C5")) Is Nothing Then
If Not Intersect(prevcell, Range("C5")) Is Nothing Then
InvoiceProblemUserForm.Show
End If
End If
Set prevcell = Target
End Sub

Basically I replaced the MsgBox with InvoiceProblemUserForm.Show.
When I leave C5 the UserForm pops up. I then choose NONE and the
UserForm flickers once and there are no chosen selections - I choose
NONE again. This time it follows though and sets the cursor in AG2.

Any ideas on why this is happening and how to correct it?

_Minitman

 

[Steve Garman]

You're changing the selection without ever reaching
Set prevCell = Target

Try this instead:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static prevCell As Range
If Not prevCell Is Nothing Then
If Intersect(Target, Range("C5")) Is Nothing Then
If Not Intersect(prevCell, Range("C5")) Is Nothing Then
Set prevCell = Target
InvoiceProblemUserForm.Show
End If
End If
End If
Set prevCell = Target
End Sub

 

[Minitman]

Hey Steve,

Thanks. I missed that entirely.

One other question, I am planning to use this code on many other cells
in succession, will they interfere with each other? Are there any
other limitations I should be aware of?

-Minitman

 

[Steve Garman]

In that case I recommend doing it a little differently.

Don't use the Static prevCell
Go back to using the original
Dim prevcell As Range
at the top of the sheet.

Do all your range tests before you
Set prevCell = Target

Instead of setting prevCell before you show your form, do this
Application.EnableEvents = False
InvoiceProblemUserForm.Show
Application.EnableEvents = True

It may be easier to move the body of the code out of the event sub and
into a separate sub, so that you can call it multiple times.

Hey Steve,

Thanks. I missed that entirely.

One other question, I am planning to use this code on many other cells
in succession, will they interfere with each other? Are there any
other limitations I should be aware of?

-Minitman

<snip>

 

[Minitman]

Thanks Steve,

That is what I am looking for.

-Minitman