Printout via Listboxes on UserForm

[ScotyB]

G'day, everyone,

in the attached workbook, I'm trying to set up a user form (UserForm13) to print data for monthly reports and because any number of people could be doing this (some who are terrible with computers, lol), I thought this might be the easiest way to go.

What I'd like to happen is this:

1. User selects Start Date (from the ListBox that uses a named range)
2. User selects Finish Date (from similar ListBox setup)
3. Once the finish date has been selected, a message box (vB YEs/No) asks if they want the records sorted
4. If NO, Excel simply prints out ALL rows on the "Data" sheet that fall between the two dates (inclusive of the start and finish dates)
5. If YES, Excel sorts the data first by Column A (Major, Minor, Misc) then by Column B (Date) THEN prints all the rows involved

I'm not sure if the ListBox is the best way to do it or if a ComboBox would be better for the start/finish dates or even if there's a more elegant solution but I'm open to all suggestions.

Many thanks in advance.

AJ

[davesexcel]

Where is the Finish Date?

[ScotyB]

Dave,

the start date and finish date are in their own listboxes on userform13, both dates in their respective listbox being drawn from named ranges on the dropdown sheet - does that make sense?

Cheers,

AJ

[ScotyB]

Is it okay to bump this up?

[ScotyB]

So, I've been looking around for some code that looks close to what I think would work and I wonder if a modification of one of these work...

Please Login or Register  to view this content.

or this...

Please Login or Register  to view this content.

Thx again to those taking a look for me.

Cheers,

AJ

[davesexcel]

Here is another way you may be interested in,

Please Login or Register  to view this content.

You will see The Userform initialize populates the List Boxes. You will have to delete the RowSource from the properties box.

Your attached example
Click the button on the Data Sheet.

[ScotyB]

Dave,

that worked a treat when I ran it from your workbook sample but I got a few errors when I copied the code to mine but I think that's because I forgot to copy your Module 1 code over as well - I'll tinker with it over the next week to make sure I've got everything.

One quick question though - the userform initialise does indeed populate those list boxes (that's great) but it doesn't do it the format asked for (ie DD-MMM); instead it does it in the month/day/year format of 4/2/12 and after looking at the code I'm not sure why?

Thank you very much for having a look for me, Dave, I appreciate your time and effort.

Cheers,

AJ

[ScotyB]

Dave,

I've reattached the workbook as I keep getting a 1004 error and I can't see why. I also noticed that in the sample you gave me, the following code was in there and I wondered if it was just you testing stuff or do I need to include it in my workbook somewhere?

Please Login or Register  to view this content.

Siigh, it's gonna take ages to get my head around this vBA stuff...

Cheers,

AJ

[ScotyB]

Dave,

just wanted to let you know that I seem to have solved the 1004 error; I'm not 100% certain that it will work all the time but I'll try some more dummy data and see how it goes. The problem appeared to be with the activesheet command and once I'd changed the name to that of the specific sheet where the data was stored, everything seemed to work well again. I still have the problem of dates in the listboxes showing in the wrong format (ie: 1/22/12 instead of 22-Jan-12) so if you have any ideas on that, I'd be grateful.

So, for now, I'd just like to say thanks again for all your help and if I don't hear from you by, say, this time next week on the date format issue, I'll mark this thread as solved and start a new one as needed.

Cheers again,

AJ

[davesexcel]

You need to reference the sheet you are filtering, the code thinks you are filtering the sheet from where you have opened the userform.
Maybe this works for you.

Please Login or Register  to view this content.

You can delete the msgbox code, it was part of the testing.

[ScotyB]

Thx, Dave,

that piece of code worked well also and since my next move is to have a YesNo message box, I can use your code for one option and the bit I was able to modify for myself as the other; that way I can see two different ways of doing things.

Cheers again,

AJ