All I want is to know (in the macro) which cell is selected (has the
border around it). No more, no less. I could send you the whole
sheet since it is only a development sheet with a small test database.
Background:
When working with the target sheet, it is necessary to sort it
often to put various things close together for human analysis. These
sorts are done on four columns. Then, we may be looking at (have
clicked the mouse on) a cell in some random location -- and -- we
want to look at the same cell (the same data) in its new location
after the sort. So I need to know which cell is active (I think that
is the correct term) as I enter the sort macro. This is so I can
find it after the sort (by stuffing it with a "find key"). I have
all this working as long as I have only one pane showing.
When I split the window (L/R) I have the problem as described when
the window is NOT frozen.
Those three un needed lines were an attempt to clear up the problem
{an unsuccessful attempt to get Excel to "see" the right pane
selection] , but they had no effect on the problem that I could see.
Delete them, then look at my cell references and see if I am doing it
incorrectly.
As I write this, I now wonder if it has to with the fact that my sort
buttons are all in the left pane and when I click one to do a sort,
Excel sees a left pane action and settles for the last selected left
pane cell.... huh, huh, huh ? I'll have to try moving one button
into the right pane and see. ... But, still, if I freeze the window
the problem goes away...
How to eliminate this problem?
I duplicate my code below without the bogus lines. While I show the
entire subroutine, the problem is apparent above the '++++++++
line. The references in these three lines do not see the selection
in the right pane and since I use the same technique to to the actual
work below, it also winds up "seeing" only the previous (but now non-
existent) left pane selection.
'gards, Steve N.
Sub Sort_N_Select()
'
' Sort_N_Select Macro
' macro recorded (and edited) 10/21/04 by Steve Noskowicz
'
' This macro has the selected cell follow along with a sort.
' Except when two, unfrozen panes show.....
'
Dim SelectedCellContents As Variant
InSortCol = 0 'A "selection is in sort col" Flag for fixing things
later...[you don't want to sort on the "Find Key"]
'
SortColumn = 4 ' Col D sort routine
Application.ScreenUpdating = False ' Suspend screen updating
'
' Here, Let's see where Excel thinks we are on the spreadsheet...
Debug.Print "Enter, Col D Sort ==========="
Debug.Print " ActiveCell="; ActiveCell ' this is the
contents '.Value' optional
Debug.Print " ActiveCell.Row="; ActiveCell.Row ' the row?
Debug.Print " ActiveCell.column "; ActiveCell.Column ' the Column?
' The three cell location references above this line all show the
last selected _Left_ pane selection
' rather than the _current_ (upon macro entry) right pane
selection. Arrrgg! (:-(
' Therefore, the remaining cell references are also in error.
'++++++++++++++++++++++++++++++++++++
' If you are in the sorted column, put the find key elsewhere (to the
right)
If ActiveCell.Column = SortColumn Then
InSortCol = 1 ' In the sort colume
Debug.Print " You're in the sort column=";
ActiveCell.Column ' is a digit
'select one cell to the right first.
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
Debug.Print " Move to the right, to Col ";
ActiveCell.Column
ActiveCell.Select 'Select the new. Not sure if it is needed,
try W/O
End If
'
'*** Here is where I "get" the selected cell to insert my find 'key'.
SelectedCellContents = ActiveCell.Value ' Hold the selected
cell's contents
Debug.Print " Save contents < "; SelectedCellContents
SelectedColumn = ActiveCell.Column ' Note the selected cell's
column
Debug.Print " Key Column noted = "; SelectedColumn
ActiveCell.Value = "ZUJQRX" ' Insert a search key in the selected
cell
Debug.Print " Insert Key > "; ActiveCell.Value
'Show the key substitution for debugging purposes only.
Application.ScreenUpdating = True
For A = 0 To 100000 ' blow some time so I can see it
For B = 0 To 500: Next
Next
Application.ScreenUpdating = False
Debug.Print " Do Sort..."
'Do your col D sort (and, therefore, move the selected
cell).
Rows("5:13").Select ' select the database
Selection.Sort Key1:=Range("D5"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Debug.Print " ...Sort done."
Columns(SelectedColumn).Select ' Select the "Key" Column
Debug.Print " Select the Key's Column = "; SelectedColumn
' Now find the Keyed cell
Selection.Find(What:="ZUJQRX", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=True).Activate
Debug.Print " Key Found & cell selected"
'Show the key'd cell After the sort
ActiveCell.Select 'Select the new. Not sure if it is needed,
try W/O
Application.ScreenUpdating = True
For A = 0 To 100000 ' Human needed slow-down for development
For B = 0 To 300: Next
Next
Application.ScreenUpdating = False
ActiveCell.FormulaR1C1 = SelectedCellContents ' Restore the
contents.
Debug.Print " Replace contents >>"; SelectedCellContents
'
If InSortCol = 1 Then ' If you WERE in the sort column
'' BUT THis doesn't do it IF you start in column E=5 !!
' ignore the above line...
Debug.Print " You're right of the sort column=";
ActiveCell.Column ' is a digit
'Go back one cell to the left.
ActiveCell.Offset(rowOffset:=0, columnOffset:=-1).Activate
Debug.Print " Offset it back to the left, to Col ";
ActiveCell.Column
End If
ActiveCell.Select 'Select the new. Not sure if it is needed,
try W/O
Debug.Print " It doesn't clear the column selection if I
don't 'Select'."
Application.ScreenUpdating = True
Debug.Print "Exit, Col D Sort ==========="
End Sub
Sub Restore()