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

  • Thread starter Thread starter Helmut Meukel
  • Start date Start date
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.
 
Sub FP()
With ActiveWindow
.SplitColumn = 0
.SplitRow = 2
.FreezePanes = True
End With
End Sub
 
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
 
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.
Click to expand...

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

application.goto reference:=ws.Range("b2"),scroll:=true
 
Back
Top