PDA

View Full Version : Problems with Worksheet_SelectionChange



anghelm
11-21-2011, 10:34 AM
Hello,

I am trying to accomplish the following:
I have 2 sheets, and i want to exchange some data between them.
Each time sheet2 is clicked on, i need a script to run.

Let's say sheet1 is selected, and i have the cell C4 active.
Eache time i go to sheet2 i need a script to run automaticly.
The script should take the value from the cell that was active in sheet1(so C4),
and copy in in sheet2, when i switch to it, in another cell, let's say D8.

I figured out i need the Worksheet_SelectionChange(ByVal Target As Range) function.
I guess that "Target", holds the value of the cell that was active in the previous sheet?
How can i access that value?
I tried Target.Value but it didn't work.

If someone could help me i would appriciate it verry much!

Thank you! :)

anghelm
11-21-2011, 11:35 AM
I have created a new module and added:
Dim rLastCell As Range


Sub ExampleProcedure()
Run "LastUsedCell", 1
If Not rLastCell Is Nothing Then
MsgBox rLastCell.Value
Else
MsgBox "The Sheet is empty"
End If
End Sub

Function LastUsedCell(lWsIndex As Long, Optional strColumn As String) As Range




If strColumn = vbNullString Then
With Sheets(1).UsedRange
Set rLastCell = .Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
End With
Else
With Sheets(lWsIndex)
Set rLastCell = .Cells(.Rows.Count, strColumn).End(xlUp)
End With
End If
End Function

But i get the value of the cell in the last row&column, not the last active one, any thoughts?

Thank you!

Bob Phillips
11-21-2011, 12:10 PM
Use SelectionChange on Sheet1 to save the (last) cell selected, and use the activate event on Sheet2 to check if that cell's value and run the script.