Most Challenging

[kaka4u]

Hi,

I have Macro:

• Which enters image, date and text to header
• Change the header font style to ‘Times New Roman’, font size to 12
• Set print area for all the non blank cells
• Set the worksheet’s font style to ‘Times New Roman ‘, Font Size to 12
• It prints in portrait orientation

Now my issues are:

• I want to have an option before printing to choose between ‘Portrait and Landscape’ orientation
• I want to have an option before printing to have a box to enter ‘Font Size’ whatever I like
• I want to have a save as dialog box with file name as “Attachment_Current Date (MMDDYYYY). So that if I want I can change the file name
• After this it should get printed as PDF on desktop with the same file name we have above

My suggestion:

For above two issues Macro should initiate a ‘User form’ where I can choose between ‘Portrait and Landscape’ orientation with the help of ‘Option button’ and font size with the help of List box (screen shot attached)

Please help me with these issues and have a great Macro to do all the above.
Thank you in advance

Macro & User form Screen shot through link below

Sub Macro1()
' Macro1 Macro
With Selection.Font
.Name = "Times New Roman"
.Size = 12

ActiveSheet.PageSetup.PrintArea = "=" & ActiveSheet.UsedRange.Address
With ActiveSheet.PageSetup
.LeftHeader = "&G"
.CenterHeader = ""
.RightHeader = "&""Times New Roman,Bold""&12Invoice #:&""Times New Roman,Regular""" & Chr(10) & "&D"
.CenterFooter = "&P"
.Orientation = xlPortrait
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.FirstPageNumber = 2
End With
End With
End Sub

http://imageshack.us/photo/my-images/263/userform.jpg/

 

[sunnyland]

Don't have time right now to answer all questions but have a look to see if my answer can satisfy the following:

• I want to have an option before printing to choose between ‘Portrait and Landscape’ orientation
• I want to have an option before printing to have a box to enter ‘Font Size’ whatever I like
• I want to have a save as dialog box with file name as “Attachment_Current Date (MMDDYYYY). So that if I want I can change the file name


Try this:

Private Sub UserForm_Initialize()
'to make sure something is selected on form start up
portrait.Value = True
'I put 10 which is in my list of values
list1.Value = 10
End Sub

Private Sub CommandButton1_Click()
With Selection.Font
.Name = "Times New Roman"
.Size = list1.Value
End With

ActiveSheet.PageSetup.PrintArea = "=" & ActiveSheet.UsedRange.Address
With ActiveSheet.PageSetup
.LeftHeader = "&G"
.CenterHeader = ""
.RightHeader = "&""Times New Roman,Bold""&12Invoice #:&""Times New Roman,Regular""" & Chr(10) & "&D"
.CenterFooter = "&P"
'my optionbutton portrait is called portrait and is set by default in the userform initialize sub
If portrait Then
.Orientation = xlPortrait
Else
.Orientation = xlLandscape
End If

.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.FirstPageNumber = 2

End With
Application.Dialogs(xlDialogSaveAs).Show ("Attachment_" & Format(Now(), "mmddyyyy"))
End Sub