lock first row and first column WITHOUT selecting Cell "B2" first?

H

Helmut Meukel

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.
 
J

JLGWhiz

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

Peter T

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
 
J

Javed

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top