Excel macro w/20 IF functions to select correct named range then print 1

[olympus]

Help please. I'm new to VB. I'm using Excel 2002. I need to write a macro that will run by the user clicking on a command button on sheet2. I have ~20 different items that can be selected in cell b3 (by a drop list)on sheet one. I would like to have the macro select the named range that is equal to the name in cell b3, sheet1 and then print "selection".

For example:

One of the possible values of cell b3, sheet1 is "Dopamine". I would like the user to be able to click on a button on sheet2 that will select the named range "dopamine" and then print this selection. If possible I would also like to have a second option that, instead of printing, will show the print preview for the range whose name matches the name in cell b3. Any help would be appreciated, as this is my first time working with VB. Thanks in advance.
John

 

[TonyJollans]

Hi olympus,

(Assuming you have created your button from the Control Toolbox) ... in Design mode, double click on your button. This will take you to the right place to put your code, which should simply be ..

[blue]Me.PageSetup.PrintArea _
    = Me.Parent.Names(Me.Range("B3").Value).RefersToRange.Address
ActiveWindow.SelectedSheets.PrintOut[green] ' Or .PrintPreview[/green][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[olympus]

Tony, Forgive me if this is a silly question, as I am new to VB. Will the above code select the named area to print based on the 1 of 20 possible values for cell b3 on sheet1? Thanks for your help.
John

 

[xlbo]

should do and that's a very clever bit of code from Mr Jollans there - why don't you try it and see ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[olympus]

Geoff, Thank you. I'm out of the office till Monday. But I'll be sure to try it first thing Monday morning. I never imagined that this could be accomplished so easily. You guys are great! Thanks.
John

 

[olympus]

Tony, I tried your code today and it worked perfectly. You saved me hours of frustration. Thank you!