asked on

Print excel sheet using VB6.5

i am looking for a script that in an active sheets selects the active range (all filled-in columns rows), in the page setup verifies if the papersize is set on A3 if not message to user, set to fit 1page by xx tall and then printout.

Can anyone help me ?

Rory Archibald

Something like:

With ActiveSheet.PageSetup
        If .PaperSize <> xlPaperA3 Then
            MsgBox "Paper is not A3"
        Else
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = False
            ActiveSheet.PrintOut
        End If
    End With

Open in new window

SPARC-DESIGN

ASKER

Thx, I worked on your solution and finally came up with
Although i am not sure where the macro detects the active range <
With ActiveSheet.PageSetup
If .PaperSize = xlPaperA3 Then
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
ActiveSheet.PrintOut
Else
If .PaperSize <> xlPaperA3 Then
askquest = MsgBox("Papersize is not A3. Do you wish to continue printing?", vbYesNo + vbQuestion, "Warning!!!")
If askquest = vbYes Then
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
ActiveSheet.PrintOut
Else
Cancel = False
End If
End If
End If
End With

SPARC-DESIGN

ASKER

Is there a way to change the paperzise to A3
like
if .papersize <> xlpapersizeA3 then xlpapersizeA3

Rory Archibald

if .papersize <> xlpapersizeA3 then .papersize = xlpapersizeA3

By default Excel will just print the used range, so I didn't see the relevance of that?

SPARC-DESIGN

ASKER

when i try this i get an error message :
Unable to set the papersizeproperty of the pagesetupclass

here is the 'full'code

Sub PrintPage()
With ActiveSheet.PageSetup
If .PaperSize = xlPaperA3 Then
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
ActiveSheet.PrintOut
Else
If .PaperSize <> xlPaperA3 Then
askquest = MsgBox("Papersize is not A3 and will be set to A3. Do you wish to continue printing?", vbYesNo + vbQuestion, "Warning!!!")
If askquest = vbYes Then
.PaperSize = xlpapersizeA3
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
ActiveSheet.PrintOut
Else
Cancel = False
End If
End If
End If
End With
End Sub

Rory Archibald

Sorry I just copied the constant you had posted - it's actually xlPaperA3 not xlpapersizea3- so:
If .PaperSize <> xlPaperA3 Then .PaperSize <> xlPaperA3

ASKER CERTIFIED SOLUTION

SPARC-DESIGN

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rory Archibald

You're welcome!
FYI, you can simply accept an answer rather than requesting the close process.