PDA

View Full Version : UI and Cell Event Routines?



deyken
01-17-2012, 11:35 PM
Hi All,

I have a formatted worksheet with a number of rows with categorised information in these rows (accross several cols). In Col "A" I have a little 'code' and I want the row number to change based on this 'code' as entered by the user. That is, I want the entire row to Cut/Paste to a different location on the same Sheet, based on the category value typed into Columb "A". This Sub must fire every time the user clicks out of the cell.

As such, I want to know if there is some sort of Cell_OnExit() routine I can call to make this happen every the user clicks out of Cell(x, "A")?

mancubus
01-18-2012, 12:57 AM
hi.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'adopted from:
'http://www.ozgrid.com/forum/showthread.php?t=36223

Dim strName As String
Dim rngTemp As Range

strName = "'" & Target.Parent.Name & "'!lastcell"
With ActiveWorkbook
On Error Resume Next
Set rngTemp = .Names(strName).RefersToRange
On Error GoTo 0
If Not rngTemp Is Nothing Then
.Names(strName).RefersTo = "='" & Target.Parent.Name & "'!" & Target.Address
If rngTemp.Column = 1 And Target.Column <> 1 Then
'your cut/paste code goes here
'your cut/paste code goes here
'your cut/paste code goes here
'your cut/paste code goes here
End If
Else
.Names.Add Name:=strName, RefersTo:="='" & Target.Parent.Name & "'!" & Target.Address
End If
End With

End Sub

Aflatoon
01-18-2012, 01:58 AM
I would suggest a worksheet_change event since you presumably want the code triggered when you change a cell in column A rather than every time you select a different cell.