• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro print Combo Box

H

haddad

New Member
Hello,

I am a very beginner in VBA and I need your help to create a (simple?) macro

I have a dashboard which is updated with a combo box.

Do you think it is possible to print this dashboard with option 1 of combo box, option 2, option 3... with one macro?

Thank you for answer
 
Haddad

Firstly, Welcome to the Chandoo.org Forums

Yes it is

Can you attach the file here with a description of what each option should include in the printout?

Also which version of excel are you using?
 
Hui, thank you for your help !

I use excel 2010 but is it possible to only give you the required information to create the macro instead of posting my file? I have sensible information inside... :)

For the options, I want to print all available options of the combobox.

I have 2 sheets :
- "DATABASE" : where I have the list which feed my combo box (range I8:I18)
- "Sales Analysis" : where I have my combobox (named : Drop Down 187) and this combobox updates the range I want to print (range : A30:Q69)

Is that okay for you if we work that way?
 
Do you want to print it every time the Combobox changes or just when a another "Print" button is pressed ?
 
I would like to have a Print button which print my sheet with option 1 of the combobox, then option 2, then option 3 ... to the last option
 
Adding the following code to a code module in VBA
Then linking it to a Button on the worksheet is probably easiest

Code: 
Sub Print_Me()
'
Sheets("Sales Analysis").Select
Range("A30:Q69").Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
  .PrintTitleRows = ""
  .PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = "A30:Q69"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
  .LeftHeader = ""
  .CenterHeader = ""
  .RightHeader = ""
  .LeftFooter = ""
  .CenterFooter = ""
  .RightFooter = ""
  .LeftMargin = Application.InchesToPoints(0.7)
  .RightMargin = Application.InchesToPoints(0.7)
  .TopMargin = Application.InchesToPoints(0.75)
  .BottomMargin = Application.InchesToPoints(0.75)
  .HeaderMargin = Application.InchesToPoints(0.3)
  .FooterMargin = Application.InchesToPoints(0.3)
  .PrintHeadings = False
  .PrintGridlines = False
  .PrintComments = xlPrintNoComments
  .PrintQuality = 600
  .CenterHorizontally = False
  .CenterVertically = False
  .Orientation = xlPortrait 'Change if required
  .Draft = False
  .PaperSize = xlPaperA4 'Change if required
  .FirstPageNumber = xlAutomatic
  .Order = xlDownThenOver
  .BlackAndWhite = False
  .Zoom = False
  .FitToPagesWide = 1
  .FitToPagesTall = 1
  .PrintErrors = xlPrintErrorsDisplayed
  .OddAndEvenPagesHeaderFooter = False
  .DifferentFirstPageHeaderFooter = False
  .ScaleWithDocHeaderFooter = True
  .AlignMarginsHeaderFooter = True
  .EvenPage.LeftHeader.Text = ""
  .EvenPage.CenterHeader.Text = ""
  .EvenPage.RightHeader.Text = ""
  .EvenPage.LeftFooter.Text = ""
  .EvenPage.CenterFooter.Text = ""
  .EvenPage.RightFooter.Text = ""
  .FirstPage.LeftHeader.Text = ""
  .FirstPage.CenterHeader.Text = ""
  .FirstPage.RightHeader.Text = ""
  .FirstPage.LeftFooter.Text = ""
  .FirstPage.CenterFooter.Text = ""
  .FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False

End Sub
 
If your Combobox is a Data Validation Cell I will assume it is in P6
change your code as below

Code: 
Sub Print_Me()
'
Sheets("Sales Analysis").Select
Range([P6]).Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
  .PrintTitleRows = ""
  .PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = [P6]
Application.PrintCommunication = False
With ActiveSheet.PageSetup
  .LeftHeader = ""
  .CenterHeader = ""
  .RightHeader = ""
  .LeftFooter = ""
  .CenterFooter = ""
  .RightFooter = ""
  .LeftMargin = Application.InchesToPoints(0.7)
  .RightMargin = Application.InchesToPoints(0.7)
  .TopMargin = Application.InchesToPoints(0.75)
  .BottomMargin = Application.InchesToPoints(0.75)
  .HeaderMargin = Application.InchesToPoints(0.3)
  .FooterMargin = Application.InchesToPoints(0.3)
  .PrintHeadings = False
  .PrintGridlines = False
  .PrintComments = xlPrintNoComments
  .PrintQuality = 600
  .CenterHorizontally = False
  .CenterVertically = False
  .Orientation = xlPortrait 'Change if required
  .Draft = False
  .PaperSize = xlPaperA4 'Change if required
  .FirstPageNumber = xlAutomatic
  .Order = xlDownThenOver
  .BlackAndWhite = False
  .Zoom = False
  .FitToPagesWide = 1
  .FitToPagesTall = 1
  .PrintErrors = xlPrintErrorsDisplayed
  .OddAndEvenPagesHeaderFooter = False
  .DifferentFirstPageHeaderFooter = False
  .ScaleWithDocHeaderFooter = True
  .AlignMarginsHeaderFooter = True
  .EvenPage.LeftHeader.Text = ""
  .EvenPage.CenterHeader.Text = ""
  .EvenPage.RightHeader.Text = ""
  .EvenPage.LeftFooter.Text = ""
  .EvenPage.CenterFooter.Text = ""
  .EvenPage.RightFooter.Text = ""
  .FirstPage.LeftHeader.Text = ""
  .FirstPage.CenterHeader.Text = ""
  .FirstPage.RightHeader.Text = ""
  .FirstPage.LeftFooter.Text = ""
  .FirstPage.CenterFooter.Text = ""
  .FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False

End Sub
 
No I have insert my combox through developer tab, Insert > Combobox (Form Control).
Then I chose your first macro, right? :]
 
Why should it?

Do you want to step through each selection in the combo box ?
 
This is why files are so important!

Does the Combo Box link to a cell?
Which cell ?
 
You don't need the Combo Box as you can step through the range from the Database sheet directly

Try this code
Code: 
Sub Print_Me()
'
Dim c As Range
Sheets("Sales Analysis").Select
For Each c In Worksheets("Database").Range("I8:I18")
 
  Range(c.Text).Select
  Application.PrintCommunication = False
  With ActiveSheet.PageSetup
  .PrintTitleRows = ""
  .PrintTitleColumns = ""
  End With
  Application.PrintCommunication = True
  ActiveSheet.PageSetup.PrintArea = Range(c.Text)
  Application.PrintCommunication = False
  With ActiveSheet.PageSetup
  .LeftHeader = ""
  .CenterHeader = ""
  .RightHeader = ""
  .LeftFooter = ""
  .CenterFooter = ""
  .RightFooter = ""
  .LeftMargin = Application.InchesToPoints(0.7)
  .RightMargin = Application.InchesToPoints(0.7)
  .TopMargin = Application.InchesToPoints(0.75)
  .BottomMargin = Application.InchesToPoints(0.75)
  .HeaderMargin = Application.InchesToPoints(0.3)
  .FooterMargin = Application.InchesToPoints(0.3)
  .PrintHeadings = False
  .PrintGridlines = False
  .PrintComments = xlPrintNoComments
  .PrintQuality = 600
  .CenterHorizontally = False
  .CenterVertically = False
  .Orientation = xlPortrait 'Change if required
  .Draft = False
  .PaperSize = xlPaperA4 'Change if required
  .FirstPageNumber = xlAutomatic
  .Order = xlDownThenOver
  .BlackAndWhite = False
  .Zoom = False
  .FitToPagesWide = 1
  .FitToPagesTall = 1
  .PrintErrors = xlPrintErrorsDisplayed
  .OddAndEvenPagesHeaderFooter = False
  .DifferentFirstPageHeaderFooter = False
  .ScaleWithDocHeaderFooter = True
  .AlignMarginsHeaderFooter = True
  .EvenPage.LeftHeader.Text = ""
  .EvenPage.CenterHeader.Text = ""
  .EvenPage.RightHeader.Text = ""
  .EvenPage.LeftFooter.Text = ""
  .EvenPage.CenterFooter.Text = ""
  .EvenPage.RightFooter.Text = ""
  .FirstPage.LeftHeader.Text = ""
  .FirstPage.CenterHeader.Text = ""
  .FirstPage.RightHeader.Text = ""
  .FirstPage.LeftFooter.Text = ""
  .FirstPage.CenterFooter.Text = ""
  .FirstPage.RightFooter.Text = ""
  End With
  Application.PrintCommunication = True
  ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
Next

End Sub
 
Last edited:
You must log in or register to reply here.
Back
Top