Strange error in VBA

[Daniel Bonallack]

I have a line in my code that adjusts the page so that there is no vertical
page break

ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1

This line works fine if you F8 through the code, but if you run the macro,
it returns an error (err.number = 1004, Application-defined or object defined
error)

Any help appreciated!

Daniel

 

[JLGWhiz]

Just guessing since I can't see the full code, but if page breaks are not
displayed it gives that error message. Also, if the print area is user
defined it will apply VPageBreak to all pages not just one, so it might balk
at the VPageBreaks(1).

 

[Daniel Bonallack]

Thanks for your help - I'll play around with your suggestions and see if I
can work it out.
A couple of further notes:
- screenupdating is off
- I'm in page break preview
- I can see the page breaks, and the print area is user defined.

 

[SeanC UK]

Hi Daniel,

I've had a look at this and the only time I seem to get the error is when
the view is Normal. In Page Break Preview the code works whether stepping
through the code using F8 or running it through a macro. Try something like
this to force the Page Break Preview view:

Sub PBreakSub()
Dim intWinView As Integer
intWinView = ActiveWindow.View
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, _
regionindex:=1
ActiveWindow.View = intWinView
End Sub

I have done this with screen updating on and off and it works.

If you are still having troubles with this then please give more code so
that we can duplicate your process more accurately.

I hope this helps.

Sean.

 

[Daniel Bonallack]

Thanks! That worked!

Hi Daniel,

I've had a look at this and the only time I seem to get the error is when
the view is Normal. In Page Break Preview the code works whether stepping
through the code using F8 or running it through a macro. Try something like
this to force the Page Break Preview view:

Sub PBreakSub()
Dim intWinView As Integer
intWinView = ActiveWindow.View
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, _
regionindex:=1
ActiveWindow.View = intWinView
End Sub

I have done this with screen updating on and off and it works.

If you are still having troubles with this then please give more code so
that we can duplicate your process more accurately.

I hope this helps.

Sean.