Prev: Before right click:
Next: Find and Replace using a Loop won't stop
From: Helmut Meukel on 26 Apr 2010 14:22
Hi,

I'm new to this group.
From a VB6-Program I create a new workbook with 1 worksheet.
After writing the title row I want to set the first column and the first
row non-scrollable. This is my actual code in the VB6 program:
ws.Range("B2").Select
ActiveWindow.FreezePanes = True

I tested it, it works.

My Question: is there another way to do this WITHOUT selecting
the cell first? Doing it with an non-active window?

I've searched to no avail, but probably haven't asked the right questions
or used the correct terms. (English isn't my first language).

TIA,

Helmut.



From: JLGWhiz on 26 Apr 2010 16:18

Sub FP()
With ActiveWindow
.SplitColumn = 0
.SplitRow = 2
.FreezePanes = True
End With
End Sub



"Helmut Meukel" <NoSpam(a)NoProvider.de> wrote in message
news:%23kl1n1W5KHA.3576(a)TK2MSFTNGP05.phx.gbl...
> Hi,
> I'm new to this group.
> From a VB6-Program I create a new workbook with 1 worksheet.
> After writing the title row I want to set the first column and the first
> row non-scrollable. This is my actual code in the VB6 program:
> ws.Range("B2").Select
> ActiveWindow.FreezePanes = True
>
> I tested it, it works.
> My Question: is there another way to do this WITHOUT selecting
> the cell first? Doing it with an non-active window?
>
> I've searched to no avail, but probably haven't asked the right questions
> or used the correct terms. (English isn't my first language).
>
> TIA,
>
> Helmut.
>
>
>


From: Peter T on 26 Apr 2010 18:20
Short answer is No!

You can, indeed should, do almost everything in Excel without use of
activate or select. However there are just a few things with the Window
object you do need to, and what you are trying to do is one of them. If you
are using VB6 and automating a hidden instance, there's no need to make the
application itself visible. Try this in VBA

Sub test()
Dim xl As Excel.Application, wb As Excel.Workbook, wn As Excel.Window

Set xl = New Application
Set wb = xl.Workbooks.Add

wb.Worksheets(2).Activate
wb.Worksheets(2).Range("D4").Activate

xl.Windows(1).FreezePanes = True
xl.Visible = True

' might want to do xl.usercontrol = true

End Sub

There are also one or two settings that can only be set with at least one
visible workbook.

Regards,
Peter T

"Helmut Meukel" <NoSpam(a)NoProvider.de> wrote in message
news:%23kl1n1W5KHA.3576(a)TK2MSFTNGP05.phx.gbl...
> Hi,
> I'm new to this group.
> From a VB6-Program I create a new workbook with 1 worksheet.
> After writing the title row I want to set the first column and the first
> row non-scrollable. This is my actual code in the VB6 program:
> ws.Range("B2").Select
> ActiveWindow.FreezePanes = True
>
> I tested it, it works.
> My Question: is there another way to do this WITHOUT selecting
> the cell first? Doing it with an non-active window?
>
> I've searched to no avail, but probably haven't asked the right questions
> or used the correct terms. (English isn't my first language).
>
> TIA,
>
> Helmut.
>
>
>


From: Javed on 28 Apr 2010 02:50
On Apr 26, 11:22 pm, "Helmut Meukel" <NoS...(a)NoProvider.de> wrote:
> Hi,
>
> I'm new to this group.
> From a VB6-Program I create a new workbook with 1 worksheet.
> After writing the title row I want to set the first column and the first
> row non-scrollable. This is my actual code in the VB6 program:
>     ws.Range("B2").Select
>     ActiveWindow.FreezePanes = True
>
> I tested it, it works.
>
> My Question: is there another way to do this WITHOUT selecting
> the cell first? Doing it with an non-active window?
>
> I've searched to no avail, but probably haven't asked the right questions
> or used the correct terms. (English isn't my first language).
>
> TIA,
>
> Helmut.

If you want to avoid select for speeding up the code you can use
following line

application.goto reference:=ws.Range("b2"),scroll:=true
 | 
Pages: 1
Prev: Before right click:
Next: Find and Replace using a Loop won't stop