PDA

View Full Version : [SOLVED] Why does the "Format shape" window appear when i run a macro?



paulked
03-06-2017, 07:07 AM
Hi all.

When i run this...

Sub PrintLeague()
Application.ScreenUpdating = False
Dim i As Integer
Dim rsp As String
Dim lr As Long
Dim shRes As Worksheet, shPrt As Worksheet
Set shRes = ThisWorkbook.Worksheets("Results")
Set shPrt = ThisWorkbook.Worksheets("Print")
rsp = MsgBox("Do you want to include the last three games?", vbYesNo, "Include games?")
shPrt.Rows("22:29").ClearContents
If rsp = vbYes Then
lr = shRes.Cells(shRes.Rows.Count, "C").End(xlUp).Row
shRes.Range("A" & lr - 27).Copy
shPrt.Range("D22").PasteSpecial
shRes.Range("B" & lr - 26 & ":B" & lr - 20).Copy
shPrt.Range("A23").PasteSpecial Paste:=xlPasteValues
shRes.Range("C" & lr - 26 & ":E" & lr - 20).Copy
shPrt.Range("D23").PasteSpecial Paste:=xlPasteValues
shRes.Range("F" & lr - 26 & ":F" & lr - 20).Copy
shPrt.Range("I23").PasteSpecial Paste:=xlPasteValues
shRes.Range("A" & lr - 17).Copy
shPrt.Range("N22").PasteSpecial
shRes.Range("B" & lr - 16 & ":B" & lr - 10).Copy
shPrt.Range("K23").PasteSpecial Paste:=xlPasteValues
shRes.Range("C" & lr - 16 & ":E" & lr - 10).Copy
shPrt.Range("N23").PasteSpecial Paste:=xlPasteValues
shRes.Range("F" & lr - 16 & ":F" & lr - 10).Copy
shPrt.Range("S23").PasteSpecial Paste:=xlPasteValues
shRes.Range("A" & lr - 7).Copy
shPrt.Range("X22").PasteSpecial
shRes.Range("B" & lr - 6 & ":B" & lr).Copy
shPrt.Range("U23").PasteSpecial Paste:=xlPasteValues
shRes.Range("C" & lr - 6 & ":E" & lr).Copy
shPrt.Range("X23").PasteSpecial Paste:=xlPasteValues
shRes.Range("F" & lr - 6 & ":F" & lr).Copy
shPrt.Range("AC23").PasteSpecial Paste:=xlPasteValues
End If
Sheets("Table").Range("E3:T18").Copy
shPrt.Range("H5").PasteSpecial Paste:=xlPasteValues
For lr = 5 To 20
If shPrt.Range("H" & lr) = "" Then Exit For
Next
lr = lr - 1
shPrt.Sort.SortFields.Clear
shPrt.Sort.SortFields.Add Key:=Range("W5:W20"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
shPrt.Sort.SortFields.Add Key:=Range("V5:V20"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With shPrt.Sort
.SetRange Range("F4:W" & lr)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
shPrt.PrintPreview
End Sub
the "Format Shape" window opens.

I am using TextBox's as 'buttons' to call subs, but they don't sit on any of the ranges I'm selecting.

Any ideas?

Many thanks

Paul Ked

paulked
03-06-2017, 07:38 AM
I've narrowed it down to the PrintPreview command.
Whichever sheet I run this from it brings up the "Format Shape" window!
:dunno

Paul_Hossler
03-06-2017, 08:59 AM
I couldn't reproduce it, but do you have an active/default printer set?

Sometimes not having a printer active causes strange things to happen when dealing in the print area

paulked
03-06-2017, 10:14 AM
Thank you for replying.

I do have a a printer set and everything works, apart from the format window.

I've re-started excel and even tried re-booting, but still it appears!

I've asked my wife to bring home her notebook to see if i can replicate it on that.

I'll keep you posted :confused2

paulked
03-06-2017, 05:22 PM
It didn't do it on the other machine, so i guess it's down to a gremlin somewhere on mine.

I'll mark it solved but if (or rather when!) I get to the bottom of it I'll post back.

:dunno