problem column (B)

[Guest]

Procedure opens column (F) to wrap text (from shrink to fit)
Turns print to lanscape
Supposed to count column (B) to determine what to print (works in other
places)
remove print area from worksheet
Column (F) back to shrink to fit.
Question am I trying to do to much in one procedure. I don't know.
Will enclose entire procedure with my attempts blocked.
It prints all records not useing (B) as counter Also no grid lines as needed.
This is the biggest procedure I have ever tried.
If anyone can sort this out I will be eternaly gratfull .
Thank All
here it is

Private Sub OptionButton32_Click()
'Print Announcer Parade Sheets
OptionButton32.Value = False
'With Sheets("Announcer")
' pCnt = Application.InputBox("How Many Copies from 1-9", Type:=1)
' If pCnt < 1 Or pCnt > 9 Then Exit Sub
' ActiveWindow.SelectedSheets.PrintOut Copies:=pCnt, Collate:=True
' Worksheets("Announcer").pagesetup.PrintGridlines = True
' .pagesetup.PrintArea = "A2:F" & Cells(Rows.Count,
"B").End(xlUp).Row
' End With
Sheets("Announcer").Select
Columns("F").Select
With Selection
Application.ScreenUpdating = False
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.MergeCells = False
Application.ScreenUpdating = True
End With
' Sheets("Announcer").Select
' With Sheets("Announcer")
' pCnt = Application.InputBox("How Many Copies from 1-9", Type:=1)
' If pCnt < 1 Or pCnt > 9 Then Exit Sub
' ActiveWindow.SelectedSheets.PrintOut Copies:=pCnt, Collate:=True
' Worksheets("Announcer").pagesetup.PrintGridlines = True
' .pagesetup.PrintArea = "A2:F" & Cells(Rows.Count,
"B").End(xlUp).Row
' End With
Application.ScreenUpdating = False
With ActiveSheet.pagesetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
Application.ScreenUpdating = True
With Sheets("Announcer")
pCnt = Application.InputBox("How Many Copies from 1-9", Type:=1)
If pCnt < 1 Or pCnt > 9 Then Exit Sub
ActiveWindow.SelectedSheets.PrintOut Copies:=pCnt, Collate:=True
Worksheets("Announcer").pagesetup.PrintGridlines = True
.pagesetup.PrintArea = "A2:F" & Cells(Rows.Count, "B").End(xlUp).Row
End With
ActiveSheet.pagesetup.PrintArea = ""
Columns("F").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = True
.MergeCells = False
End With
End With
Range("G4").Select
End Sub

 

[Guest]

Your code look great, no problem with the size of the macro. Your WITH
statements and END WITH are out of sequence. You hae two END WITH together
at the end. You also seem to have the code out of order

from:
ActiveWindow.SelectedSheets.PrintOut _
Copies:=pCnt, Collate:=True
Worksheets("Announcer"). _
<= Prints
PageSetup.PrintGridlines = True
.PageSetup.PrintArea = _
"A2:F" & Cells(Rows.Count, "B").End(xlUp).Row <= Page Area

to:

ActiveWindow.SelectedSheets.PrintOut _
Copies:=pCnt, Collate:=True
.PageSetup.PrintArea = _
"A2:F" & Cells(Rows.Count, "B").End(xlUp).Row <= Page Area
Worksheets("Announcer"). _
<= Prints
PageSetup.PrintGridlines = True


I would try stepping through the code and switch back and forth from
worksheet to VBA and watch which cells are being selected to test the code.

1) Go to VBA window and select the line below with mouse
OptionButton32.Value = False
2) Press F9 - line will change color
3) go to worksheeet and activate the Control Button
4) step through code using the F8 button

Go to VBA window

 

[Guest]

Thanks for the direction the f8 f9 i was not aware of. Guess that comes from
lack of schooling
Thanks Again