hide rows before printing

[associates]

Hi,

wonder if anyone might be able to help me here. I've a worksheet that contains a whole list of items in stock.

For example
Item Quantity
hot water bottle
maggi
fab
cooking oils
breakfast oats


The above is an example of what might be seen in the spreadsheet. What i'd like to be able to do is before printing it out, i want items with 0 quantity to be shown only. So i decided to hide the rows that have items with no quantity. To do this, all i could think of is to have a button that may contain codes to hide the rows. The problem with that is the button will appear there in the printout. Is there any way of making the rows hidden before printing without using a button to trigger the code?

Thank you in advance

[Greg M]

Hi there,

There sure is! Just use the "Workbook_BeforePrint" event in the "ThisWorkbook" VBA module to trigger your routine for hiding the undesired rows.

Also, by default a button on a worksheet is not printed.

Hope this helps - please let me know how you get on.

Best regards,

Greg M

[Ikaabod]

Open VBA (Alt-F11)
Double-Click on "ThisWorkbook" on the left

Click "(General)" and select "Workbook"
Click "Open" and select "BeforePrint"
The following should pop up:

Please Login or Register  to view this content.

Put your code to hide rows in there. You can delete the part that says:

Please Login or Register  to view this content.

Hope that's what you're looking for.

[associates]

Thank you Greg M and Ikaadbod for your replies.

No, it still prints the whole list out.

Here is the code

Please Login or Register  to view this content.

Then, i go to menu, File and print. It printed the whole rows, desired and undesired ones. Have i missed out on something?

Thank you in advance

[associates]

Hi,

Just to let you know that it actually works. The reason it wasn't working before was because i had it in the design view mode.

However, i'm afraid i have another question to ask. The problem is how do i include/attach these codes(hiding the undesired rows) in the worksheet before it's being sent to other staff using CDO to send mail in excel spreadsheet. I know that i can't send just the worksheet alone but a copy of the worksheet. The problem with the copy of the worksheet is that the code may not be in the worksheet anymore. How do i include the codes in the copy of the worksheets before sending?

Thank you in advance

[Greg M]

Hi again,

If you're sending copies of the workSHEETS to your colleagues, the worksheets will obviously be included in their own "Carrier" workBOOK. Just copy the code from the "ThisWorkbook" module in your "Master" workbook to the "ThisWorkbook" module in the "Carrier" workbook.

Hope this helps,

Greg M

[associates]

Hi Greg M, thank you for your reply.

Sorry that i might have given you the wrong info here. I think i make a copy of the worksheet and send it with a new activeworkbook as shown here in the code.

Please Login or Register  to view this content.

From the code, a new activeworkbook is created and is sent with the copy of the worksheet to my work colleagues. If this is the case, what should i do to ensure that the "ThisWorkbook" module in my "Master" workbook to the "ThisWorkbook" module in the new-created workbook?

Thank you in advance

[Greg M]

Hi again,

It's possible to write code to copy the contents of the "ThisWorkbook" module in your "Master" workbook to the "ThisWorkbook" module in your new workbook, and I might be able to dig out some sample code if necessary.

A simpler method might be to copy your entire "Master" workbook (instead of just "mysheet") and then to delete all sheets except "mysheet" from the new workbook.

How does this approach sound?

Regards,

Greg M

[associates]

Hi Greg M,

Yes, that sounds great. But how do i create a copy of my entire "Master" workbook and delete the whole sheets but "mysheet" sheet?

Greg, i don't want to use sendmail function to send mail but CDO code. This is just to avoid the warning security popup dialog box. At the moment, the codes i show there in the earlier post does the job for me.

Thank you in advance

[associates]

Hi Greg,

I have been trying to search on some codes that would create a copy of the master workbook as you suggested above but to no avail.

Would you be able to give me a bit of direction as how to achieve this?

I'd be very grateful.

Thank you in advance

[Greg M]

Hi again,

I don't have Excel installed on this machine (it's not mine!) so I'm not able to test what I'm about to suggest - here's hoping!

Your code:

Please Login or Register  to view this content.

creates a copy of your master workbook.

To delete everything except "mySheet" use:

Please Login or Register  to view this content.

However, if I've understood you correctly, you want to avoid your users being presented with the "This workbook contains macros ..." dialog box. The workbook WILL contain code, so I don't think you can avoid the message being generated by Excel.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[associates]

Hi Greg M,

Thank you for your reply.

Sorry i didn't mean to keep on posting with the same question again. I agree with you that the following code does create a copy of the master workbook.

Please Login or Register  to view this content.

After sending the spreadsheet to myself via email for a test, i downloaded it and open it. I really hate to say this to you that there is nothing in the "ThisWorkbook" when double-clicking on it in the VB editor. Supposedly, there should be an event called "Workbook_BeforePrint" in there but none.

It seems to me that eventhough we create a copy of the master workbook but it didn't copy whatever events in the "ThisWorkbook" of the master one to the new workbook.

I don't know how this could happen.

I'd really appreciate your time and help, Greg.

Thank you in advance

[Greg M]

Hi again,

Ok - let's take things one step at a time.

First, delete (or comment out) the "Kill TempFilePath & TempFileName & FileExtStr" statement.

Next, run the code to create a copy of the "Master" workbook. (By the way, I'm assuming that the variable "FileFormatNum" in your .SaveAs statement has a value appropriate to a relatively recent version of Excel)

Now open the temporary workbook to see what the copy of the "Master" workbook file actually contains - more specifically, to see if its "ThisWorkbook" VBA module contains code. If so, great - we can address the e-mailing aspect; if not, we've got to try to figure out why not!

Try what I've suggested & let me know what happens.

If you want to post or PM me a copy of your workbook I'll take a look at it.

Regards,

Greg M

[associates]

Hi Greg M,

Thank you for your reply.

I tried to comment out "Kill TempFilePath & TempFileName & FileExtStr" statement" but the copy of the master workbook still doesn't contain code in the "ThisWorkbook" VBA module.

I need to send the copy of my workbook to you.

Please find attached copy of my workbook - mytimesheet.pdf. Sorry i change the extension from .xls to .pdf because couldn't do it with .xls. please change it back to .xls.

Thank you in advance

[associates]

Hi Greg M,

Just wonder if you have a chance to help me look into the copy of my workbook that i attached to the previous post.

Really appreciate your help.

Thank you in advance

[davesexcel]

Hi Greg M,

Just wonder if you have a chance to help me look into the copy of my workbook that i attached to the previous post.

Really appreciate your help.

Thank you in advance

Hi there,
You need to zip your workbook then you will be able to attach it

[stephenw]

I dont know if this helps
I have this code on a print button
It will hide blank rows and only print rows with data, you might be able to adjust it to suit

Please Login or Register  to view this content.

[associates]

Thank you, Davesexcel and Stephenw for your replies.

I have attached the workbook as zip file.

Stephenw, I have no problem with hiding the undesired rows using the button. I can put a button there on the worksheet and have it sent off to our co-worker but try not to because it looks a bit ugly on the printout.

I like the idea of putting the code (hiding the rows) in the "ThisWorkbook" like mentioned in the earlier posts. The problem with that is after making a copy of the master workbook and sent off by mail, it doesn't contain anything in the "ThisWorkbook" of the copied workbook.

for example, in the master workbook, there is the following code in ThisWorkbook in VB editor.

Please Login or Register  to view this content.

after making a copy of that and mailing it, i looked into ThisWorkbook of the copied one in VB editor, no codes is found there whatsoever. tried using sendmail and then CDO codes but to no avail.

appreciated your help.

Thank you in advance

[associates]

Hi,

Sorry forgot to attach the file. here it is

Thank you

[Greg M]

Hi there,

Sorry for the delay in getting back to you with this.

The problem is that you're making a copy of the workSHEET, not the workBOOK. This explains why the "ThisWorkbook" VBA module of the NEW workbook does not contain the code from the "ThisWorkbook" VBA module of the original workbook.

Insert the additional code shown in red into your module. This will copy the required code to the "ThisWorkbook" VBA module of your new workbook.

Please Login or Register  to view this content.

It would be better to move the Dim statement to the start of your routine.

By the way, do you need the code to redisplay the hidden rows once the printing operation has finished?

Hope this helps - please let me know how you get on.

Best regards,

Greg M

[associates]

Hi Greg,

Thank you for your reply.

fantastic. it works great. That's what i wanted. Now, the codes get copied to the new workbook. Sorry for taking up so much of your time to get this sorted out. Can't believe it that it's only a single line of that that'd do the job :D

No, i think i'll be fine. Anyway, i tested it using the event of "beforeclose" under the ThisWorkbook in VB editor and it worked that it re-displayed the hidden rows.

Thank you Greg M, for your time and effort in helping me out. greatly appreciated it.

[Greg M]

Hi there,

Many thanks for the feedback. You're welcome - delighted to have helped.

By the way, you might be interested in the following snippet of code - it allows you to hide rows, print the worksheet, and display the hidden rows immediately afterwards. Also, using the "booSaved" variable means that hiding & then unhiding the rows isn't registered as a change to the workbook, so users aren't presented with the "Do you want to save changes?" prompt unless they've made other changes themselves.

Please Login or Register  to view this content.

Best regards,

Greg M