printing named ranges from a validation list

Hi Pixi,

Check this sample workbook that I am attaching, and let me know if (loosely) it does what you need. If so, let's tailor it to your needs.

Hi BigBas

Thanks for your help. Yes the attached workbook does what I need...it would be useful if a user could select more than one item to be printed at a time (as there could be so many) but if that's a prob (and I'm asking for the moon on the stick) your version is fine.

I did take a look at the code to see if I could amend it but I can't so your help is really appreciated. I'd be greatful too if you could explain the code line by line (sorry...I'm trying to learn this!)

Thanks again
P

Hi Pixifaery,

I am attaching another copy of the workbook, this time using your data. First, please note one thing. I took your worksheet and copied it into a new workbook. For whatever reason, when I was running code on your current workbook, I noticed some corrupt named ranges. So, I copied all of the data into a new worksheet, and tried copying over some of your named ranges ( I got a total of 3). You may need to reformat your sheet (it's printing out on too many pages). I would also confirm that all of the data copied over correctly. Sorry that I had to do that.

Take another look at the userform. I have replaced the combo box with a list box. You can select more than one range now (if you are not familiar with listboxes, if you hold down the CTRL key while selecting, you can select more than one option).

Test out the file and let me know if it works. If so, write back and we can discuss the coding.

Hi Pixifaery,

I'm not sure that I understand your question. Is there any reason that the worksheets are grouped together? I updated the spreadsheet to account for multiple sheets. You do not have to be on the relevant sheet to print out. In the example I will post below, I have included a sheet with dummy data. It includes 3 named ranges, doctors, lawyers, and janitors. When you launch the form from the main (Comm) sheet, these named ranges will still be available to be printed. Test it out. Try printing one range from the comm sheet and one range from the dummy sheet. Let me know if it works. If not, let's amend the code. If so, let's customize it for your application.

Hi BigBas

Sorry, just saw 'activeworkbook' and though it was 'activeworksheet' therefore would only work on the active worksheet. Durh!

Tried printing as you suggested but the following code has a 404 error

Please Login or Register  to view this content.

I don't have printer at home but am networked and think I can still print but will check again tomorrow. I did try printing from the first workbook you sent over and that printed fine.

Thanks
P

Hi Pixifaery,

Just to confirm, use the last workbook I posted as your basis for the project. That workbook should print correctly, and it should work for multiple sheet applications. If it doesn't work, then we can take a closer look at the code and see where it errs.

Just as an FYI, if you don't have a printer at home and still want to test, you can download a virtual printer (the best I have found is CutePDF writer). It acts as a printer, and converts documents to PDFs. It's actually quite handy for many different reasons.

Either way, I'll check this post tomorrow for any updates.

Hi BigBas

Its all copied across but have noticed two things

1. Can only select one report to print at a time (even though on original form it works fine)
2. In the list box there are lots of ghost ranges, have run some code (below) to reveal them but still have lots left - tried printing one and nothing came out (print titles?), tried printing another and the whole worksheet printed.

Please Login or Register  to view this content.

Just looked in 'Insert, Hyperlinks, places in this document and they're all there - seem to relate to filters, print area, print titles, and cols(?) all prefixed WVU

Any ideas?

Hi Pixifaery,

Sorry for the delay in responding, but I was away from my computer all day today.

In response to the issues that you have been having:
1. Likely, the properties of the listbox have changed, only allowing a single selection at a time. To correct this, select the listbox within the userform. A 'properties' window should be visible to the left. If not, press F4 to see it. One of the properties is 'MultiSelect'. Make sure that Option 1 is chosen.

2. I noticed the phantom names when I initially worked with your workbook. If you don't want to delete them, we must find a workaround while keeping them in the workbook. In my opinion, the best idea is to change the name of the named ranges that you intend to print. I would prefix each printed named range with prn (for print, but you can select any prefix you'd like). This way, we can capture only named ranges that begin with prn in the userform. This is likely going to take some time on your part, but I think its the only solution (unless someone else knows a different method).

If you choose to make a change as I've described in section 2, simply change the Userform_Initialize code to state the following

Please Login or Register  to view this content.

If necessary, we can modify the code to NOT display the prn in the listbox (although the names will still contain prn) if necessary for presentation purposes.

Let me know how things turn out.

Hi BigBas

Have changed the list box properties and it works!!

I'm not going to worry about the extra names in the box, considering how long it took me to define them all yesterday, and the fact that the whole model is being migrated to Access.

Last requirement is to e-mail out ranges so have found some of ron de bruins code and will try and change it (g*d I'm funny!).

Would it be possible to explain some of the bits of code you wrote for me? I've had a go but could you check to see if I'm on the right track.

Please Login or Register  to view this content.

Thanks again for all your help

Hi Pixifaery,

I have pulled out each of your comments from the code, and I will explain. Also, if you hit any roadblocks in modifying Ron de Bruin’s code, post back, and I’ll try to help you if I can.


'Set the varibles (can you specify the name of variables to whatever you want?
-- You can specify the name of the variable to anything you want, as long as it is not already part of the programming language. For example, you couldn’t use the variable name TEXT because that is already an Excel function.
'Count number of items in listbox
-- Correct. We determine the number of items in the list box to be used in our loop later on. Note, we subtract 1 from the number of items. We do this because the first item in a listbox is actually item number 0.
'Do not display what code is doing on the screen, or alerts (to speed up code)
--Correct
'Not sure what the i=0 to numselect means?
-- This is part of our loop, and the reason we calculated the number of items in the listbox. The loop starts at the “For I = … line and continues through “Next I”. Our loop basically says to run the code between For/Next over and over for each number. So, in our case, we run the code for I=0, I=1, I=2… I=numselect. Note the role of the “i” variable in the code. We use each “i” to query that item in the listbox.
'If an item is selected in the list box then go to ws with range, clear print area,
'select range as print area, print
--Correct

'Set the variables - NameCnt is a number and PrntArea is a text string
--Correct

'On the current sheet set the print area to the whole sheet (but not sure why?)
--Actually, this line really intends to delete any existing print area. The reason for this is that Print_Area becomes a named range, and I didn’t want any current Print_Areas being populated in the list box.
'Count all the named ranges in the workbook
--Correct
'Populate the listbox with all the names (where i=1 is that to with counting
'the names and telling it to start at the first one and continue until it
'gets to the last?
-- Correct. This is another example of the For/Next loop.

Hi BigBas

Thanks for helping with the explanation, it's really useful. I'm glad to know I'm starting to be able to read the code, admittedly still struggling with compiling it. Don't know what I'd do without the recorder.....

Have spent most of today looking at Ron's code and can see the sections that I need but not sure how to combine. So I thought rather than posting here and getting someone to adjust and not really learning anything I'd try my own approach

What I want to do is e-mail out the named ranges to recipients as specified on a 'distribution list'.

As I mentioned am struggling with Ron's code, but thought that if I managed to save each of the named ranges as a file, I can then use those files to e-mail out using Ron's code (rather than e-mailing different ranges, and each range would also need the headers, from one file to a list of recipients).

So I proceeded to set that up. The point I've reached is needing to save the file and would ideally save it as the named range but not sure if it's possible to pull that information across?

I'll then get it to loop through each of the named ranges and save it. (Did consider printing to file and amending the code you wrote for me) but realised that doesn't do what I thought it did). Haven't written a loop before so thought that would be a good exercise to attempt.

Thanks
P