Cells: Any way to code an event behind one?

  • Thread starter (PeteCresswell)
  • Start date
P

(PeteCresswell)

I'm thinking I'd like to automatically select the next cell in a series after
the user pastes something into the current cell.

Can I write event code for a given cell, like "Cell(2,3).AfterUpdate()"?
 
B

Bob Phillips

It should be easy with a change event, but what does ... next cell in a
series ... mean?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
P

(PeteCresswell)

Per Bob Phillips:
It should be easy with a change event, but what does ... next cell in a
series ... mean?
Click to expand...

If I'm in R1C3, I'd want to move focus to R2C3.

How would the macro be named?

Or can it be any name and somehow associated with the cell via the UI?
 
C

Chip Pearson

Pete,

You need to use the Change event code. Right-click the appropriate sheet tab
and choose View Code. This will take you to that sheet's object module in
the VBA editor. Paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
'''''''''''''''''''''''''''''
' Restrict actions to A1:A10)
'''''''''''''''''''''''''''''
Dim ISect As Range
Set ISect = Application.Intersect(Me.Range("A1:A10"), Target)
If ISect Is Nothing Then
Exit Sub
Else
If ISect.Cells.Count <> Target.Cells.Count Then
Exit Sub
End If
End If
Application.EnableEvents = False
Target.Cells(Target.Cells.Count)(2, 1).Select
Application.EnableEvents = True

End Sub

This code will select the cell below the paste operation after the paste. As
written, it will apply only to changes in the range A1:A10. Change that
range reference as appropriate.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
P

(PeteCresswell)

Per Chip Pearson:
You need to use the Change event code. Right-click the appropriate sheet tab
and choose View Code. This will take you to that sheet's object module in
the VBA editor. Paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
'''''''''''''''''''''''''''''
Click to expand...

Thanks Chip.

The big revelation to Yours Truly was the concept of Worksheet_Change().
 
Top