Combo box to select Print Areas

Condor

New Member
Joined
Nov 11, 2002
Messages
5
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
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

New Member
Joined
Nov 11, 2002
Messages
5
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

So what is in your ComboBox?
 

Condor

New Member
Joined
Nov 11, 2002
Messages
5
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

Condor

New Member
Joined
Nov 11, 2002
Messages
5
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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:

Code:
Private Sub ComboBox1_Change()

End Sub

4. Change it so that it looks like this:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,321
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top