BeforeSelectionChange

[Guest]

I want to track prior cell address before "selectionchange" event.

I'd much appreciate any help in this respect.

 

[Bernie Deitrick]

Asif,

In a regular codemodule (change the 10 to the number of steps you want to track):

Public mySel(1 To 10) As String

Sub ShowPastSelections()
Dim myPast As String
Dim i As Integer

myPast = ""
For i = LBound(mySel) To UBound(mySel)
myPast = myPast & Chr(10) & mySel(i)
Next i
MsgBox myPast
End Sub

Put this in the ThisWorkbook's Codemodule:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim i As Integer
For i = UBound(mySel) To LBound(mySel) + 1 Step -1
mySel(i) = mySel(i - 1)
Next i
mySel(LBound(mySel)) = Target.Address(, , , True)
End Sub

 

[Guest]

Thank you for your response. I get the following error on the public statement:

"Constants, fixed length strings, array... not allowed as public members of
object modules"

Please help.
--
Thanx & regards,
Asif

Asif,

In a regular codemodule (change the 10 to the number of steps you want to track):

Public mySel(1 To 10) As String

Sub ShowPastSelections()
Dim myPast As String
Dim i As Integer

myPast = ""
For i = LBound(mySel) To UBound(mySel)
myPast = myPast & Chr(10) & mySel(i)
Next i
MsgBox myPast
End Sub

Put this in the ThisWorkbook's Codemodule:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim i As Integer
For i = UBound(mySel) To LBound(mySel) + 1 Step -1
mySel(i) = mySel(i - 1)
Next i
mySel(LBound(mySel)) = Target.Address(, , , True)
End Sub

 

[Dave Peterson]

In a regular codemodule (change the 10 to the number of steps you want to track):

That portion doesn't go in the ThisWorkbook module. It goes in a general
module.

Thank you for your response. I get the following error on the public statement:

"Constants, fixed length strings, array... not allowed as public members of
object modules"

Please help.

 

[Guest]

Hi Dave,

I see nothing. No message box appears. Here is how I entered the code:

Module1:
-----------
Public mySel(1 To 10) As String

Sub ShowPastSelections()
Dim myPast As String
Dim i As Integer

myPast = ""
For i = LBound(mySel) To UBound(mySel)
myPast = myPast & Chr(10) & mySel(i)
Next i
MsgBox myPast
End Sub

This Workbook
------------------
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Dim i As Integer
For i = UBound(mySel) To LBound(mySel) + 1 Step -1
mySel(i) = mySel(i - 1)
Next i
mySel(LBound(mySel)) = Target.Address(, , , True)
End Sub

 

[Dave Peterson]

You still have to run the ShowPastSelections. That's not automatic.

So make a few selection changes and then tools|macros|macro and run that macro.