Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Cell reference/pane errors

  Asked By: Holly    Date: Mar 25    Category: MS Office    Views: 971
  

What's up with pane references...A real "pane in the Window"

Maybe this'll help...
RE-post of new problem, with better subject line.

With only one pane in the window, I get correct cell references into
my macro and all works as expected.

When I split the window into two panes (L/R), my "ActiveCell..."
references do not return the correct cell reference (location). I
only get the last selected cell in the *left pane* even if a cell in
the right pane *IS* selected (when the macro is entered).
Two more symptoms:
1- If I select a left pane cell and "arrow" to the right (selecting
cells to the right), moving the selected cell into the area currently
covered by the right pane (also causing the left pane to
automatically scroll to the right - you now see the selected cell in
poth panes since they cover common areas), THEN my macro gets the
correct cell reference.
2- If I "Freeze" the window panes, then I appear to always get the
correct cell referenced into the macro - regardless whether it is in
the left or right pane.
I see a "Panes" object or method (I can't tell the difference just
yet) AND have been able to write a statement which returns a value
(in the immediate window via a Debug.Pring statement) showing how
many panes are open (1, 2, or 4), but do not know what I need to do
(other than freezing the window) to always get a correct cell
reference regardless of which pane it happens to be visible in.

It's getting to be a pain!
Steve
N.
Here's my test macro, but the problem has to be obvious above
the '+++++++++++++++++++
====================================================
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.
'
Dim SelectedCellContents As Variant
InSortCol = 0 'A "selection is in sort col" Flag for fixing things
later...
'
SortColumn = 4 ' Col D sort routine
Application.ScreenUpdating = False ' Suspend screen updating
'
ActiveCell.Activate ' These three don't enlighten the macro.
ActiveCell.Cells.Activate
ActiveCell.Cells.Select

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 variables 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 in error.

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Latoya Murray     Answered On: Mar 25

I'm confused with your code. You added three statements that are
selecting a cell  and so that's the address it's returning which makes
sense. It's rarely needed to specifically select/activate a cell and
the macro  recorder does that a lot.

From your code:
ActiveCell.Activate ' These three don't enlighten the macro.
ActiveCell.Cells.Activate
ActiveCell.Cells.Select

These do nothing since 'activecell' is where the current cell (the
one where you can enter data) so it's already active. Selecting a
cell doesn't necessarily make it active.

In my own tests, I created a sheet with two panes, data in each pane.
Then in the IDE immediate window  (ctrl+g), I do '?activecell.address'
and get the correct  address for the cell which is activate no matter
what pane  it is in. There is only one activecell  per sheet. The panes
collection just tells you how many panes you have and the addresses
of cells in that pane.

What's the object  of your code, it's not clear since it's doing
exactly as it's written. Let's start over by asking what you're
trying to do. You have two panes in a single sheet and you want code
that does what???

?activewindow.panes(1).VisibleRange.Address

This returns  the cells that are in pane #1 (left most). There is
nothing in the panes collection that would limit how you read a cell.

 
Answer #2    Answered By: Shobhana R.     Answered On: Mar 25

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()

 
Answer #3    Answered By: Carl Woods     Answered On: Mar 25

Why don't you have your code unsplit the window  before it runs, and then
resplit it after your code is finished?

 
Answer #4    Answered By: Adal Fischer     Answered On: Mar 25

I guess that's an option. Makes it more complex(have to
figure out how to get is re-split in the same place), though. I
could also freeze / un-freeze, since it appears to remove the problem
when frozen...

 
Didn't find what you were looking for? Find more on Cell reference/pane errors Or get search suggestion and latest updates.




Tagged: