sich48
12-28-2010, 05:17 AM
I have a workbook with several sheets and a userform with a commandbutton to show a OWC spreadsheet and another to print a selected area of this spreadsheet. On a regular basis (not always!) a message pops up:
“A form or sheets you want to move contains the name “Print_Area” which already exists on the destination worksheet. Do you want to use this version of the name? To use the name as defined in the destination sheet, click Yes. To create a new name for this form or sheet, click No.” The Yes button shows as the default one.
The error routine of course overrules this message but it nevertheless is visible for a short moment. Without the error routine in place, the code just stops and waits for the user’s response.
I’ve been searching desperately the internet to find similar issues. Is there something wrong with my code and how can I avoid this message appearing??
Here is my code:
Private Sub cmdPrint_Click()
cmdShowSpread_Click
End Sub
Private Sub cmdShowSpread_Click()
Dim ws As Worksheet
‘ here is the code to determine sline, eline
‘ ………and to Set ws as one of 4 worksheets [i.g. Set ws = “Worksheets(“November”)]
‘ ………
'Printing titlerows + selected area
Application.ScreenUpdating = False
On Error GoTo PrtError
With ws
.PageSetup.PrintArea = ""
.Range("A" & CStr(sline) & ":G" & CStr(eline)).Name = "Print_Area"
.PageSetup.PrintTitleRows = ""
.PageSetup.PrintTitleRows = .Range("$3:$5).Address
.PrintOut Copies:=1
End With
Application.ScreenUpdating = True
Exit Sub
PrtError:
MsgBox "Printer not found. Please check.", vbExclamation
Application.StatusBar = False
End Sub
“A form or sheets you want to move contains the name “Print_Area” which already exists on the destination worksheet. Do you want to use this version of the name? To use the name as defined in the destination sheet, click Yes. To create a new name for this form or sheet, click No.” The Yes button shows as the default one.
The error routine of course overrules this message but it nevertheless is visible for a short moment. Without the error routine in place, the code just stops and waits for the user’s response.
I’ve been searching desperately the internet to find similar issues. Is there something wrong with my code and how can I avoid this message appearing??
Here is my code:
Private Sub cmdPrint_Click()
cmdShowSpread_Click
End Sub
Private Sub cmdShowSpread_Click()
Dim ws As Worksheet
‘ here is the code to determine sline, eline
‘ ………and to Set ws as one of 4 worksheets [i.g. Set ws = “Worksheets(“November”)]
‘ ………
'Printing titlerows + selected area
Application.ScreenUpdating = False
On Error GoTo PrtError
With ws
.PageSetup.PrintArea = ""
.Range("A" & CStr(sline) & ":G" & CStr(eline)).Name = "Print_Area"
.PageSetup.PrintTitleRows = ""
.PageSetup.PrintTitleRows = .Range("$3:$5).Address
.PrintOut Copies:=1
End With
Application.ScreenUpdating = True
Exit Sub
PrtError:
MsgBox "Printer not found. Please check.", vbExclamation
Application.StatusBar = False
End Sub