Combo box to select Print Areas

[Condor]

Hi all

I'd like to use a combo box to select which areas of my sheet get printed but I'm stuck.

Anyone any ideas?

Thanks for your help.

Condor

 

[Andrew Poulsom]

What is in your ComboBox?

 

[stevebausch]

Do you have the print area creation macros in place? Perhaps buttons are adequate? For the most part, invoking an action is the action of buttons, whereas listboxes/comboboxes simply select, which is then acted upon by a macro.

 

[Condor]

Thanks for replying.

The Print Area Creation macros are in place. I just can't get the link from the Combo Box to them. Normally I would have used buttons but space is limited on the sheet.

Thanks

 

[Andrew Poulsom]

So what is in your ComboBox?

 

[Condor]

It's a calander on the sheet but I want to be able to select & print one month at a time. The combo box has the names of the months but I'm not sure how to link these to the macros that change the Print area to the approprite month.

 

[Andrew Poulsom]

Given that you have a month name, what is your code to set the Print_Area?

 

[Condor]

heres the code for the January & February macro's as an exampleSub JAn()
'
' JAn Macro
' Macro recorded 12/11/2002 by ###
'
ActiveSheet.PageSetup.PrintArea = ""
Range("A1:AE64").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$AE$64"
ActiveWindow.LargeScroll Down:=-4
Range("C4").Select
End Sub
Sub Feb()
'
' Feb Macro
' Macro recorded 12/11/2002 by ###
'
Range("AF1:BG64").Select
ActiveSheet.PageSetup.PrintArea = "$AF$1:$BG$64"
Range("AF4").Select
End Sub

 

[Andrew Poulsom]

I expected things to be a bit more structured than that, but anyway ...

1. Right click any Toolbar and check Control ToolBox.
2. In the Control Toolbox click the Design icon (top left).
3. Right click your combo box and choose View Code. You should get something like:

Private Sub ComboBox1_Change()

End Sub

4. Change it so that it looks like this:

Private Sub ComboBox1_Change()
   Select Case ComboBox1.Value
      Case "Jan": Call Jan
      Case "Feb": Call Feb
   End Select
End Sub

5. Press Alt+F11 to return to your worksheet and click the Design icon again to exit Design mode.

I will leave you to add calls to the macros for the remaining months to the Select Case construct.
This message was edited by Andrew Poulsom on 2002-11-12 10:31