Using a drop down list to select print range

[PatrickMinto]

Good Day, I have a macro to print named ranges which are entered in a message box. I would like to use a drop down list to select the ranges from so as to eliminate errors due to typo errors. I know how to populate and validate the range names for the drop down list but do not know how to incorporate this into my macro for printing?
The named ranges are names of months - Mar to Dec each range consisting of 20 values. The drop down list is populated with the names of the months and I want to choose a range to print from the drop down list.

The first part of the macro is as follows:-

Option Explicit

Sub PrintResults()
'
' Keyboard Shortcut: Ctrl+Shift+Q

'check functionality status

Dim PrintRange As String
Dim screenUpdateState

screenUpdateState = Application.ScreenUpdating

Application.ScreenUpdating = False 'turn off screen updating to stop flicker & increase speed

ActiveSheet.Unprotect Password:="1234"

PrintRange = InputBox("range to print") 'Input box asks for named range then prints the range you entered

Application.PrintCommunication = False

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$4"
.PrintTitleColumns = ""

End With

Etc, etc

I would be most grateful for some help here.

Regards

[kev_]

Perhaps something like this
3 named ranges created
RefersTo for each name reflects required print range

Test in attached file by selecting a value from dropdown in M1 and clicking on Print button

SelectPrintRange.jpg

Print Button returns message box detailing selected print range

SelectPrintRange2.jpg

Please Login or Register  to view this content.

[PatrickMinto]

Thank you Kev I will try working on that, and will come back to you if I get it right
Much appreciated

[kev_]

There is more than one way to achieve what you want. I tend to aim for the simplest approach. But I do not know what else you have in mind and so my first suggestion may not necessarily be the best option for you

[PatrickMinto]

Hi Kev, Yes what I had in mind is that only 1 macro is needed to print from the values in the drop down list. The drop down list is a list of named ranges ( MAR to DEC)each with a unique range. So I wanted the macro to be triggered by the value selected in the drop down list. Is that possible?

So I have a Forms Button (Prints Months) on the same sheet as the ranges and so I would like that when the Button is pressed , the drop down list comes up and the selection can be made and that will trigger the macro to print range selected.

My macro presently does the job with a Message box where the operator enters the month range and the macro prints the range.

This is the present macro up to the page set up:

Please Login or Register  to view this content.

Thanks and regards

[kev_]

Please add code tags etc to both your first and second posts

This is Rule 3 of our Forum RULES. Use code tags around code.

Posting code between tags like this...
[CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE]
.... makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then select code text and click the # icon at the top of your post window. More information about these and other tags can be found here

thanks

[PatrickMinto]

Hi Kev,
Thank you I have resolved my query and my code is working fine - the Print Button is using the values in the Drop down list, and I have dispensed with the Message box.
Kind regards

[kev_]

Glad you are fixed up
Please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.