Freezing the top line of a sheet with visual basic

[jjfjr]

Hi;

I have a workbook with three sheets - Search Form, Results and Data Table.
The search form allows the user to type in information into one of ten
fields. Each field is aasociated with a column in the data table ; any hits
go into the results sheet. The header row of the data table goes in as the
top row of the results sheet. I'd like to freeze the results top row so that
when someone scrolls through, the top row stays on top so the user can see
the column descriptions. Also. I'd like to have the header row of the results
sheet print out no matter where the user has scrolled to.

I saw the following code in another post but it gave me a "run-time error 9:
subscript out of range" error; the second line was indicated as the culprit:


Worksheets("Sheet3").Activate
Sheet3.Range("A1:J1").Select
ActiveWindow.FreezePanes = True


Any help is greatly appreciated.
--
JJFJR

[Tim Williams]

> Sheet3.Range("A1:J1").Select

This will give an error if there is no worksheet with codename "Sheet3".
Note the codename can be distinct from the name on the sheet tab.

Maybe try
activeworkbook.sheets("Sheet3").Range("A1:J1").Select
or
thisworkbook.sheets("Sheet3").Range("A1:J1").Select

depending on where your code is running.

Tim

--
Tim Williams
Palo Alto, CA


"jjfjr" <[email protected]> wrote in message
news:[email protected]...
> Hi;
>
> I have a workbook with three sheets - Search Form, Results and Data Table.
> The search form allows the user to type in information into one of ten
> fields. Each field is aasociated with a column in the data table ; any
hits
> go into the results sheet. The header row of the data table goes in as the
> top row of the results sheet. I'd like to freeze the results top row so
that
> when someone scrolls through, the top row stays on top so the user can see
> the column descriptions. Also. I'd like to have the header row of the
results
> sheet print out no matter where the user has scrolled to.
>
> I saw the following code in another post but it gave me a "run-time error
9:
> subscript out of range" error; the second line was indicated as the
culprit:
>
>
> Worksheets("Sheet3").Activate
> Sheet3.Range("A1:J1").Select
> ActiveWindow.FreezePanes = True
>
>
> Any help is greatly appreciated.
> --
> JJFJR

[T-ｮex]

Hi jjfjr! I tried the code you gave below and it didn't issue an error. But, it froze the wrong panes - the panes above and to the left of cell G18.
If you want to freeze rows and/or columns, select the cell directly below the specified rows and to the right of the specified columns.
Example, if you want to freeze row 1 and column A, you select, cell B2, which is directly below row 1 and to the right of column A. Then, FREEZE!

Worksheets("Sheet3").Activate
Worksheets("Sheet3").Range("B2").Select
ActiveWindow.FreezePanes = True

Hi;

I have a workbook with three sheets - Search Form, Results and Data Table.
The search form allows the user to type in information into one of ten
fields. Each field is aasociated with a column in the data table ; any hits
go into the results sheet. The header row of the data table goes in as the
top row of the results sheet. I'd like to freeze the results top row so that
when someone scrolls through, the top row stays on top so the user can see
the column descriptions. Also. I'd like to have the header row of the results
sheet print out no matter where the user has scrolled to.

I saw the following code in another post but it gave me a "run-time error 9:
subscript out of range" error; the second line was indicated as the culprit:


Worksheets("Sheet3").Activate
Sheet3.Range("A1:J1").Select
ActiveWindow.FreezePanes = True


Any help is greatly appreciated.
--
JJFJR

[jjfjr]

Hi;

Thanks for the response. I want to freeze the top row (header row)
only...what do I select? I need all of the cells in the rest of the sheet to
scroll.
--
JJFJR


"T-ﾂｮex" wrote:

>
> Hi jjfjr! I tried the code you gave below and it didn't issue an error.
> But, it froze the wrong panes - the panes above and to the left of cell
> G18.
> If you want to freeze rows and/or columns, select the cell directly
> below the specified rows and to the right of the specified columns.
> Example, if you want to freeze row 1 and column A, you select, cell B2,
> which is directly below row 1 and to the right of column A. Then,
> FREEZE!
>
> Worksheets("Sheet3").Activate
> Worksheets("Sheet3").Range("B2").Select
> ActiveWindow.FreezePanes = True
>
>
> jjfjr Wrote:
> > Hi;
> >
> > I have a workbook with three sheets - Search Form, Results and Data
> > Table.
> > The search form allows the user to type in information into one of ten
> > fields. Each field is aasociated with a column in the data table ; any
> > hits
> > go into the results sheet. The header row of the data table goes in as
> > the
> > top row of the results sheet. I'd like to freeze the results top row so
> > that
> > when someone scrolls through, the top row stays on top so the user can
> > see
> > the column descriptions. Also. I'd like to have the header row of the
> > results
> > sheet print out no matter where the user has scrolled to.
> >
> > I saw the following code in another post but it gave me a "run-time
> > error 9:
> > subscript out of range" error; the second line was indicated as the
> > culprit:
> >
> >
> > Worksheets("Sheet3").Activate
> > Sheet3.Range("A1:J1").Select
> > ActiveWindow.FreezePanes = True
> >
> >
> > Any help is greatly appreciated.
> > --
> > JJFJR
>
>
> --
> T-ﾂｮex
> ------------------------------------------------------------------------
> T-ﾂｮex's Profile: http://www.excelforum.com/member.php...o&userid=26572
> View this thread: http://www.excelforum.com/showthread...hreadid=401601
>
>

[T-ｮex]

Hi! If you only want to freeze row 1, then you select cell A2.

Worksheets("Sheet3").Activate
Worksheets("Sheet3").Range("A2").Select
ActiveWindow.FreezePanes = True

Hi;

Thanks for the response. I want to freeze the top row (header row)
only...what do I select? I need all of the cells in the rest of the sheet to
scroll.
--
JJFJR


"T-ﾂｮex" wrote:

>
> Hi jjfjr! I tried the code you gave below and it didn't issue an error.
> But, it froze the wrong panes - the panes above and to the left of cell
> G18.
> If you want to freeze rows and/or columns, select the cell directly
> below the specified rows and to the right of the specified columns.
> Example, if you want to freeze row 1 and column A, you select, cell B2,
> which is directly below row 1 and to the right of column A. Then,
> FREEZE!
>
> Worksheets("Sheet3").Activate
> Worksheets("Sheet3").Range("B2").Select
> ActiveWindow.FreezePanes = True
>
>
> jjfjr Wrote:
> > Hi;
> >
> > I have a workbook with three sheets - Search Form, Results and Data
> > Table.
> > The search form allows the user to type in information into one of ten
> > fields. Each field is aasociated with a column in the data table ; any
> > hits
> > go into the results sheet. The header row of the data table goes in as
> > the
> > top row of the results sheet. I'd like to freeze the results top row so
> > that
> > when someone scrolls through, the top row stays on top so the user can
> > see
> > the column descriptions. Also. I'd like to have the header row of the
> > results
> > sheet print out no matter where the user has scrolled to.
> >
> > I saw the following code in another post but it gave me a "run-time
> > error 9:
> > subscript out of range" error; the second line was indicated as the
> > culprit:
> >
> >
> > Worksheets("Sheet3").Activate
> > Sheet3.Range("A1:J1").Select
> > ActiveWindow.FreezePanes = True
> >
> >
> > Any help is greatly appreciated.
> > --
> > JJFJR
>
>
> --
> T-ﾂｮex
> ------------------------------------------------------------------------
> T-ﾂｮex's Profile: http://www.excelforum.com/member.php...o&userid=26572
> View this thread: http://www.excelforum.com/showthread...hreadid=401601
>
>

[jjfjr]

Hi;

I inserted the following code into my Search button code:

Sheet3.Activate
Sheet3.Range("A2").Select
ActiveWindow.FreezePanes = True

The Results sheets does freeze the top row, specifically cells A1 to J1.
However, when I scroll, it seems that the entire area of A1 to J6 is frozen.
Also, when I try to scroll horizontally, the entire columns A through E are
also frozen. As I said before all I want frozen is the first row: cells A1 to
J1.

Before the above code is encountered, I clear out the first 1000 rows of the
results sheet:

Sheet3.Range("A2:J1000").ClearContents

This is done to clear out the results from any previous searches.

Also, just before the freeze code, I have the following code:

Sheet3.Columns.AutoFit
Sheet3.Rows.AutoFit

This is done to reduce the area of the results sheet as much as possible.

Any ideas? Any help is greatly appreciated.
--
JJFJR


"T-ﾂｮex" wrote:

>
> Hi! If you only want to freeze row 1, then you select cell A2.
>
> Worksheets("Sheet3").Activate
> Worksheets("Sheet3").Range("A2").Select
> ActiveWindow.FreezePanes = True
>
>
> jjfjr Wrote:
> > Hi;
> >
> > Thanks for the response. I want to freeze the top row (header row)
> > only...what do I select? I need all of the cells in the rest of the
> > sheet to
> > scroll.
> > --
> > JJFJR
> >
> >
> > "T-ﾃつｮex" wrote:
> >
> > >
> > > Hi jjfjr! I tried the code you gave below and it didn't issue an
> > error.
> > > But, it froze the wrong panes - the panes above and to the left of
> > cell
> > > G18.
> > > If you want to freeze rows and/or columns, select the cell directly
> > > below the specified rows and to the right of the specified columns.
> > > Example, if you want to freeze row 1 and column A, you select, cell
> > B2,
> > > which is directly below row 1 and to the right of column A. Then,
> > > FREEZE!
> > >
> > > Worksheets("Sheet3").Activate
> > > Worksheets("Sheet3").Range("B2").Select
> > > ActiveWindow.FreezePanes = True
> > >
> > >
> > > jjfjr Wrote:
> > > > Hi;
> > > >
> > > > I have a workbook with three sheets - Search Form, Results and
> > Data
> > > > Table.
> > > > The search form allows the user to type in information into one of
> > ten
> > > > fields. Each field is aasociated with a column in the data table ;
> > any
> > > > hits
> > > > go into the results sheet. The header row of the data table goes in
> > as
> > > > the
> > > > top row of the results sheet. I'd like to freeze the results top
> > row so
> > > > that
> > > > when someone scrolls through, the top row stays on top so the user
> > can
> > > > see
> > > > the column descriptions. Also. I'd like to have the header row of
> > the
> > > > results
> > > > sheet print out no matter where the user has scrolled to.
> > > >
> > > > I saw the following code in another post but it gave me a
> > "run-time
> > > > error 9:
> > > > subscript out of range" error; the second line was indicated as
> > the
> > > > culprit:
> > > >
> > > >
> > > > Worksheets("Sheet3").Activate
> > > > Sheet3.Range("A1:J1").Select
> > > > ActiveWindow.FreezePanes = True
> > > >
> > > >
> > > > Any help is greatly appreciated.
> > > > --
> > > > JJFJR
> > >
> > >
> > > --
> > > T-ﾃつｮex
> > >
> > ------------------------------------------------------------------------
> > > T-ﾃつｮex's Profile:
> > http://www.excelforum.com/member.php...o&userid=26572
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=401601
> > >
> > >
>
>
> --
> T-ﾂｮex
> ------------------------------------------------------------------------
> T-ﾂｮex's Profile: http://www.excelforum.com/member.php...o&userid=26572
> View this thread: http://www.excelforum.com/showthread...hreadid=401601
>
>

[T-ｮex]

Hi jjfjr! I don't think it's possible to freeze just specific cells using FreezePanes.
Sorry... I don't think I can help you with your problem...

Hi;

I inserted the following code into my Search button code:

Sheet3.Activate
Sheet3.Range("A2").Select
ActiveWindow.FreezePanes = True

The Results sheets does freeze the top row, specifically cells A1 to J1.
However, when I scroll, it seems that the entire area of A1 to J6 is frozen.
Also, when I try to scroll horizontally, the entire columns A through E are
also frozen. As I said before all I want frozen is the first row: cells A1 to
J1.

Before the above code is encountered, I clear out the first 1000 rows of the
results sheet:

Sheet3.Range("A2:J1000").ClearContents

This is done to clear out the results from any previous searches.

Also, just before the freeze code, I have the following code:

Sheet3.Columns.AutoFit
Sheet3.Rows.AutoFit

This is done to reduce the area of the results sheet as much as possible.

Any ideas? Any help is greatly appreciated.
--
JJFJR


"T-ﾂｮex" wrote:

>
> Hi! If you only want to freeze row 1, then you select cell A2.
>
> Worksheets("Sheet3").Activate
> Worksheets("Sheet3").Range("A2").Select
> ActiveWindow.FreezePanes = True
>
>
> jjfjr Wrote:
> > Hi;
> >
> > Thanks for the response. I want to freeze the top row (header row)
> > only...what do I select? I need all of the cells in the rest of the
> > sheet to
> > scroll.
> > --
> > JJFJR
> >
> >
> > "T-ﾃつｮex" wrote:
> >
> > >
> > > Hi jjfjr! I tried the code you gave below and it didn't issue an
> > error.
> > > But, it froze the wrong panes - the panes above and to the left of
> > cell
> > > G18.
> > > If you want to freeze rows and/or columns, select the cell directly
> > > below the specified rows and to the right of the specified columns.
> > > Example, if you want to freeze row 1 and column A, you select, cell
> > B2,
> > > which is directly below row 1 and to the right of column A. Then,
> > > FREEZE!
> > >
> > > Worksheets("Sheet3").Activate
> > > Worksheets("Sheet3").Range("B2").Select
> > > ActiveWindow.FreezePanes = True
> > >
> > >
> > > jjfjr Wrote:
> > > > Hi;
> > > >
> > > > I have a workbook with three sheets - Search Form, Results and
> > Data
> > > > Table.
> > > > The search form allows the user to type in information into one of
> > ten
> > > > fields. Each field is aasociated with a column in the data table ;
> > any
> > > > hits
> > > > go into the results sheet. The header row of the data table goes in
> > as
> > > > the
> > > > top row of the results sheet. I'd like to freeze the results top
> > row so
> > > > that
> > > > when someone scrolls through, the top row stays on top so the user
> > can
> > > > see
> > > > the column descriptions. Also. I'd like to have the header row of
> > the
> > > > results
> > > > sheet print out no matter where the user has scrolled to.
> > > >
> > > > I saw the following code in another post but it gave me a
> > "run-time
> > > > error 9:
> > > > subscript out of range" error; the second line was indicated as
> > the
> > > > culprit:
> > > >
> > > >
> > > > Worksheets("Sheet3").Activate
> > > > Sheet3.Range("A1:J1").Select
> > > > ActiveWindow.FreezePanes = True
> > > >
> > > >
> > > > Any help is greatly appreciated.
> > > > --
> > > > JJFJR
> > >
> > >
> > > --
> > > T-ﾃつｮex
> > >
> > ------------------------------------------------------------------------
> > > T-ﾃつｮex's Profile:
> > http://www.excelforum.com/member.php...o&userid=26572
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=401601
> > >
> > >
>
>
> --
> T-ﾂｮex
> ------------------------------------------------------------------------
> T-ﾂｮex's Profile: http://www.excelforum.com/member.php...o&userid=26572
> View this thread: http://www.excelforum.com/showthread...hreadid=401601
>
>