Range variables and Freeze Panes

[Conan Kelly]

Hello all,

First, how do I assign a cell (or range) to a range variable (you will
see in my code posted below).

Second, can I refer to the Home Cell (the cell at which the panes are
frozen--[Ctrl] + [Home]) without actually selecting it?

What I'm trying to do is create a macro that will:
- Freeze the panes where the cursor is at if they are not already
frozen
- toggle frozen panes if panes are frozen and the active cell is
the Home Cell
- move the frozen panes to the current cell if panes are frozen
and active cell is different than the Home Cell

The one thing I'm concerned with by using the sendkeys command to get
the Home Cell address and then reselecting the current cell is that my
screen will move. That is why I want to be able to refer to the Home
Cell w/o actually selecting it.

Thanks for any help anyone can provide,

Conan Kelly

 

[Conan Kelly]

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 2/17/2006 by Conan Kelly
'

'
Dim prngCurrentCell As Range
Dim prngHomeCell As Range

Set prngCurrentCell = ActiveCell.Address
SendKeys "^{HOME}"
Set prngHomeCell = ActiveCell.Address

prngCurrentCell.Select

If ActiveWindow.FreezePanes = True Then
If prngCurrentCell = prngHomeCell Then
ActiveWindow.FreezePanes = Not (ActiveWindow.FreezePanes)
Else
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
End If
Else
ActiveWindow.FreezePanes = True
End If

End Sub

 

[Charlie]

The frozen cell will be:

Cells(ActiveWindow.ActivePane.ScrollRow, ActiveWindow.ActivePane.ScrollColumn)

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 2/17/2006 by Conan Kelly
'

'
Dim prngCurrentCell As Range
Dim prngHomeCell As Range

Set prngCurrentCell = ActiveCell.Address
SendKeys "^{HOME}"
Set prngHomeCell = ActiveCell.Address

prngCurrentCell.Select

If ActiveWindow.FreezePanes = True Then
If prngCurrentCell = prngHomeCell Then
ActiveWindow.FreezePanes = Not (ActiveWindow.FreezePanes)
Else
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
End If
Else
ActiveWindow.FreezePanes = True
End If

End Sub

Hello all,

First, how do I assign a cell (or range) to a range variable (you
will see in my code posted below).

Second, can I refer to the Home Cell (the cell at which the panes
are frozen--[Ctrl] + [Home]) without actually selecting it?

What I'm trying to do is create a macro that will:
- Freeze the panes where the cursor is at if they are not already
frozen
- toggle frozen panes if panes are frozen and the active cell is
the Home Cell
- move the frozen panes to the current cell if panes are frozen
and active cell is different than the Home Cell

The one thing I'm concerned with by using the sendkeys command to
get the Home Cell address and then reselecting the current cell is
that my screen will move. That is why I want to be able to refer to
the Home Cell w/o actually selecting it.

Thanks for any help anyone can provide,

Conan Kelly

 

[Conan Kelly]

Charlie,

Thanks for the info, but actually the ScrollRow and ScrollColumn are
the left and top most visible columns/rows in the pane (will only
match those of the frozen cell when the frozen cell is visible).

For example: Say that my frozen cell is B2 (so the first column and
first row is always visible) and the active cell is AH203. The
visible rows are 1, 186,187, 188... and visible columns are A, AD, AE,
AF... The ScrollRow will be 186 and the ScrollColumn will be AD (well
the number representing AD: 30).

But since I'm usually moving the frozen cell over or down only 1 or 2
cells, I figure that the frozen cell will always be visible, so I was
able to use this information.

Thanks again,

Conan

The frozen cell will be:

Cells(ActiveWindow.ActivePane.ScrollRow,
ActiveWindow.ActivePane.ScrollColumn)

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 2/17/2006 by Conan Kelly
'

'
Dim prngCurrentCell As Range
Dim prngHomeCell As Range

Set prngCurrentCell = ActiveCell.Address
SendKeys "^{HOME}"
Set prngHomeCell = ActiveCell.Address

prngCurrentCell.Select

If ActiveWindow.FreezePanes = True Then
If prngCurrentCell = prngHomeCell Then
ActiveWindow.FreezePanes = Not
(ActiveWindow.FreezePanes)
Else
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
End If
Else
ActiveWindow.FreezePanes = True
End If

End Sub

Hello all,

First, how do I assign a cell (or range) to a range variable (you
will see in my code posted below).

Second, can I refer to the Home Cell (the cell at which the panes
are frozen--[Ctrl] + [Home]) without actually selecting it?

What I'm trying to do is create a macro that will:
- Freeze the panes where the cursor is at if they are not
already
frozen
- toggle frozen panes if panes are frozen and the active cell
is
the Home Cell
- move the frozen panes to the current cell if panes are
frozen
and active cell is different than the Home Cell

The one thing I'm concerned with by using the sendkeys command to
get the Home Cell address and then reselecting the current cell
is
that my screen will move. That is why I want to be able to refer
to
the Home Cell w/o actually selecting it.

Thanks for any help anyone can provide,

Conan Kelly