Hi,

I have a problem to establish horizontal pagebreaks for my worksheet.

The PrintArea is set from cell A1 to cell M300. I hide rows if they may not
be seen.

this is the code I use:

sLijst.HPageBreaks.Add Befo=sLijst.Cells(vRow, 1)

where sLijst is the reference to the worksheet and vRow is the row (integer
< 300) before which the pagebreak must be established. I make the breaks in
a loop where vRow is added by 40 each time. No breaks are made.

Thanks for your help
Jos Vens

Hi
this statement looks o.k (depending on your variables). You may post
the complete code for checking the variable assignment

--
Regards
Frank Kabel
Frankfurt, Germany


Jos Vens wrote:
Hi,

I have a problem to establish horizontal pagebreaks for my worksheet.

The PrintArea is set from cell A1 to cell M300. I hide rows if they
may not be seen.

this is the code I use:

sLijst.HPageBreaks.Add Befo=sLijst.Cells(vRow, 1)

where sLijst is the reference to the worksheet and vRow is the row
(integer < 300) before which the pagebreak must be established. I
make the breaks in a loop where vRow is added by 40 each time. No
breaks are made.

Thanks for your help
Jos Vens

Jos,
On the Page setup,do you have the Scaling set to "Fit To" ?

NickHK

"Jos Vens" wrote in message
...
Hi,

I have a problem to establish horizontal pagebreaks for my worksheet.

The PrintArea is set from cell A1 to cell M300. I hide rows if they may
not
be seen.

this is the code I use:

sLijst.HPageBreaks.Add Befo=sLijst.Cells(vRow, 1)

where sLijst is the reference to the worksheet and vRow is the row
(integer
< 300) before which the pagebreak must be established. I make the breaks
in
a loop where vRow is added by 40 each time. No breaks are made.

Thanks for your help
Jos Vens

Yes I have,

this is the code which comes before

sLijst.PageSetup.PrintArea = ""
sLijst.ResetAllPageBreaks

sLijst.PageSetup.PrintArea = "$A$1:$" & GET_Col(sLijst.Cells(1016, 2)) & "$"
& sLijst.Cells(1013, 2)

sLijst.PageSetup.FitToPagesWide = 1
sLijst.PageSetup.FitToPagesTall = Int(40 / sLijst.Cells(1017, 2)) + 1

sLijst.PageSetup.PrintTitleRows = ""
If (sLijst.Cells(1018, 2) 0) Then
sLijst.PageSetup.PrintTitleRows = "$1:$" & sLijst.Cells(1018, 2)
End If

I will remove this and see what happens.

Thanks
Jos

"NickHK" schreef in bericht
...
Jos,
On the Page setup,do you have the Scaling set to "Fit To" ?

NickHK

"Jos Vens" wrote in message
...
Hi,

I have a problem to establish horizontal pagebreaks for my worksheet.

The PrintArea is set from cell A1 to cell M300. I hide rows if they may
not
be seen.

this is the code I use:

sLijst.HPageBreaks.Add Befo=sLijst.Cells(vRow, 1)

where sLijst is the reference to the worksheet and vRow is the row
(integer
< 300) before which the pagebreak must be established. I make the
breaks
in
a loop where vRow is added by 40 each time. No breaks are made.

Thanks for your help
Jos Vens

Look what strange thing I discovered now:

I removed the fit to- statements (see other reply) but the problem remains.

Now, I've checked the menu InsertPage End (in Dutch InvoegenPagina Einde)
but for that row, the menu is InsertRemove Page End (in Dutch
InvoegenPagina-einde verwijderen), which means that the pagebreak is set
(thus the code is right).

So I can't understand why excel does not execute the break???

Thanks for your reply

Jos


Here's the code of the whole procedure


Function TBA_ListCreate()

Dim vRowIncrement As Integer
Dim vC_LLN As Integer, vHPB As Integer
Dim vKlas As String
Dim vLijst As String, vInput As String
Dim sLijst As Worksheet, sInput As Worksheet, sTemp As Worksheet

Dim wbFile As Workbook

DCL_Sheets

'Als de lijst vereist dat er een deliberatielijst moet gedraaid worden
gebeurt dit hier
'vinkje cbDeliberatielijst op formulier!

INIT_AKlas

vKlas = [gKlas]

vLijst = [gRapport] & [gExt]
Set sLijst = Sheets(vLijst)

vInput = sLijst.Cells(1011, 2)
Set sInput = wbData.Sheets(vInput)

Set vCell = sInput.Cells.Find(What:=vKlas, After:=sInput.Cells(3, 1),
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False)
vRow_Input = vCell.Row

vStart = sLijst.Cells(1012, 2)
vStop = sLijst.Cells(1013, 2)
vRow = vStart
vRowIncrement = sLijst.Cells(1014, 2)

vC_LLN = 0
vHPB = 0

sLijst.Rows(vStart & ":" & vStop).Hidden = True

vLKLas = [gKlas]
vPswDsg = [gPswDsg]
vProtect = [gPProtect]

sLijst.PageSetup.PrintArea = ""
sLijst.ResetAllPageBreaks

sLijst.PageSetup.PrintArea = "$A$1:$" & GET_Col(sLijst.Cells(1016, 2)) & "$"
& sLijst.Cells(1013, 2)

'sLijst.PageSetup.FitToPagesWide = 1
'sLijst.PageSetup.FitToPagesTall = Int(40 / sLijst.Cells(1017, 2)) + 1

sLijst.PageSetup.PrintTitleRows = ""
If (sLijst.Cells(1018, 2) 0) Then
sLijst.PageSetup.PrintTitleRows = "$1:$" & sLijst.Cells(1018, 2)
End If

While (vKlas = [gKlas])

sLijst.Rows(vRow & ":" & vRow + vRowIncrement - 1).Hidden = False

'Leerlingenteller
vC_LLN = vC_LLN + 1

For i = 1 To 255

If (sLijst.Cells(1001, i) < "") Then

vCol = sLijst.Cells(1001, i)
sLijst.Cells(vRow, i) = sInput.Cells(vRow_Input, vCol)

vHide = sLijst.Cells(1002, i)

If (vHide < "") Then

If (sLijst.Cells(vRow, i) = vHide) Then
sLijst.Rows(vRow & ":" & vRow +
vRowIncrement).Hidden = True
vC_LLN = vC_LLN - 1

Exit For
End If

End If

End If

Next

vRow = vRow + vRowIncrement
vRow_Input = vRow_Input + 1
vKlas = sInput.Cells(vRow_Input, 1)

If (vC_LLN = sLijst.Cells(1017, 2)) Then
vC_LLN = 0

sLijst.HPageBreaks.Add Befo=sLijst.Cells(vRow, 1)

End If

Wend

sLijst.PrintOut Copies:=[gPCopies]

End Function

"Jos Vens" schreef in bericht
...
Hi,

I have a problem to establish horizontal pagebreaks for my worksheet.

The PrintArea is set from cell A1 to cell M300. I hide rows if they may
not
be seen.

this is the code I use:

sLijst.HPageBreaks.Add Befo=sLijst.Cells(vRow, 1)

where sLijst is the reference to the worksheet and vRow is the row
(integer
< 300) before which the pagebreak must be established. I make the breaks
in
a loop where vRow is added by 40 each time. No breaks are made.

Thanks for your help
Jos Vens

Hi,

you're right: when I remove the fit to-statements, everything works fine!
Why didn't it work at first? Because I had to manually reset the option in
page setup because my code didn't do so.

Thanks for your help!!!

Jos Vens


"NickHK" schreef in bericht
...
Jos,
On the Page setup,do you have the Scaling set to "Fit To" ?

NickHK

"Jos Vens" wrote in message
...
Hi,

I have a problem to establish horizontal pagebreaks for my worksheet.

The PrintArea is set from cell A1 to cell M300. I hide rows if they may
not
be seen.

this is the code I use:

sLijst.HPageBreaks.Add Befo=sLijst.Cells(vRow, 1)

where sLijst is the reference to the worksheet and vRow is the row
(integer
< 300) before which the pagebreak must be established. I make the
breaks
in
a loop where vRow is added by 40 each time. No breaks are made.

Thanks for your help
Jos Vens

I am automating Excel from VB6.
I ran into a similar problem using your method.
I have just stumbled upon this method that seems to work.

'select a cell, make it active
Range("A14").Select

'insert pagebreak in the active cell
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCells

Does it work for you?

Is this question about displaying the default pagebreaks in excel?
You can try following:

Application.ActiveWindow.View = xlPageBreakPreview
Application.ActiveWindow.View = xlNormalView

Once gone it to PageBreak view and come back to normal view
it continues to show the page breaks.
In your case, of course, since you are doing it thorough VB
you can refer to your excel application object istead of
Application.

Sharad



"microstoc" wrote in message
lkaboutsoftware.com...
I am automating Excel from VB6.
I ran into a similar problem using your method.
I have just stumbled upon this method that seems to work.

'select a cell, make it active
Range("A14").Select

'insert pagebreak in the active cell
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCells

Does it work for you?