Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSelectionChange
I want to track prior cell address before "selectionchange" event.
I'd much appreciate any help in this respect. -- Thanx & regards, Asif |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSelectionChange
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 -- HTH, Bernie MS Excel MVP "Asif" wrote in message ... I want to track prior cell address before "selectionchange" event. I'd much appreciate any help in this respect. -- Thanx & regards, Asif |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSelectionChange
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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "Asif" wrote in message ... I want to track prior cell address before "selectionchange" event. I'd much appreciate any help in this respect. -- Thanx & regards, Asif |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSelectionChange
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. Asif wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "Asif" wrote in message ... I want to track prior cell address before "selectionchange" event. I'd much appreciate any help in this respect. -- Thanx & regards, Asif -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSelectionChange
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 -- Thanx & regards, Asif "Dave Peterson" wrote: 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. Asif wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "Asif" wrote in message ... I want to track prior cell address before "selectionchange" event. I'd much appreciate any help in this respect. -- Thanx & regards, Asif -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSelectionChange
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. Asif wrote: 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 -- Thanx & regards, Asif "Dave Peterson" wrote: 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. Asif wrote: 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 "Bernie Deitrick" wrote: 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 -- HTH, Bernie MS Excel MVP "Asif" wrote in message ... I want to track prior cell address before "selectionchange" event. I'd much appreciate any help in this respect. -- Thanx & regards, Asif -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|