Pagebreaks are not made

[Jos Vens]

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 Before:=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

 

[Frank Kabel]

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

 

[NickHK]

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

NickHK

 

[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

 

[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 Insert>Page End (in Dutch Invoegen>Pagina Einde)
but for that row, the menu is Insert>Remove Page End (in Dutch
Invoegen>Pagina-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 Before:=sLijst.Cells(vRow, 1)

End If

Wend

sLijst.PrintOut Copies:=[gPCopies]

End Function

 

[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

 

[microstoc]

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 Before:=ActiveCells

Does it work for you?

 

[Sharad Naik]

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