Each value selected in dropdown fires different macro

What I like to use in such situation is the Print Preview which gives user much flexibility. Change the last line of program to:

Please Login or Register  to view this content.

Merci bien monsieur (here my French stops).
I'll try it out when I've access to the file.
Any thoughts about combining these 2?

marcoAUA

Monsieur, thanks for the fast reply.

I tried your code, put it in modlue 1 and debugger kicked in on
Cancel = True
Compile error; Variable not defined.

Any thoughts?

I think you can delete this line. The error comes probably from the fact that you have the expression " Option Explicit" at the beginning of your macros and this is fine. This means you out to declare any variables. As variable Cancel was not declared, you got the error.
If you look at the original sub from RoyUK, you see that Cancel is a variable parameter from the sub itself. You do not need it in yours.
Regards

Hi again,

This works great.
When the print button was clicked, first I got the PrinterSetUp dialog box and later the print preview.
Since also the print preview gives you the option to change the settings, I deleted.
Application.Dialogs(xlDialogPrinterSetup).Show.

When clicked, you go to preview, from there you can change the printer settings and off you go!

Please Login or Register  to view this content.

Back to the first question, how do I connect this macro to a value from a drop down list (form control), or any other drop down?

Thanks,

marcoAUA.

Right click on your drop down list and select "Assing Macro". From the opened window, select the macro you want to run when the list is used. It can be the same as the print button "SetPrintRangeStudent1" or any other one you want.

The macro will be assigned to the drop down, not to the values stipulated in that drop down. Also the values in this drop down will cange:

the student names might change during the course of a trimester due to whatever reason.

.
Is there a way to make this happen? Do you refer instead of the students name to the cell?

See attached file. I hope it works the way you want it to.
I had to set cell C1 as the cell link to the listbox.
I also added a formula in cell D1 (under your listbox) to get the student name from the rank given by the listbox.
You can see the formula if you click on cell D2 and move up ont time with the arrow.
I modified the macro SetPrintRangeStudent1 to work as you wish.
I commented the macro lines so you could understand how it works or what is its purpose.
You'll have to arrange your sheet very precisely. I mean each range to print is 12 columns wide and the starting point is 3 rows above the student's name cell. You'll have to set all titles properly. Lots of work.
Hope this help

The cell link is to have an easy access to the combobox value to use it in a formula. The combobox allways return the rank of the selected value, not the value itself although you see its text. With this value in cell C1, the formula in cell D1 gets the text value to be searched in the macro.
To get around a value which can't be found, I added an IF statement. It makes sure the Find has found something in order to set the print range and show the page to print. Otherwise, it skips this part of program and ends the sub.
Regards

Perfect!

I guess it is not possible that the drop down does not show blank entries?

THNX a bundle for the help.

the dropbox shows you what is in the source range. You have to manage having no blank entries in that range.
good luck

Thank you so much.

I'll be posting more threads, since the project is not ready yet.
In the mean time, this one is SOLVED.