Click here to print sheet

[pboost1]

Hello everybody. I created a spreadsheet that has dropdowns and cells for people to fill in information. My supervisor has now asked me to set it up so that the form won't print unless all necessary information is filled in. I figured the easiest way to do this is create a button for printing the form.

I selected the cells I want as the "button". Now here is problem, I would like the button to be greyed out until all the necessary cells are filled. How can I have the "button" able to be pressed, but only be active if all the cells contain information?

Any help on this would be greatly appreacited. Thank you in advance.

[HaHoBe]

Hi, pboost1,

if the cells are empty/blank you could use WorksheetFunction.CountBlank to get the number of empty cells. If the user enters manually or by using DropDown the Worksheet_Change-event could be used to monitor the button.

If the cells show a standard text instead of the necessary action you could build a range to check and loop through all cells of the range and compare the value of the cell to the standard text and add 1 to a variable. If the variable is equal to the number of cells in the range the button could be activated.

Another way would be to get this as the starting part of the button and exit the sub if the information has not been entered (show a message to the user) while continuing if the necessary information has been entered.

HTH,
Holger

[jwlamb]

in cell X10000 or what ever, add in a formula =IF(OR(A1="",D2="",G5=""),0,1). Change A1, D2, G5 to every cell that you need populated. Then add this to the VBA for that sheet, not "ThisWorkbook".

Please Login or Register  to view this content.

[pboost1]

Hi, pboost1,

if the cells are empty/blank you could use WorksheetFunction.CountBlank to get the number of empty cells. If the user enters manually or by using DropDown the Worksheet_Change-event could be used to monitor the button.

If the cells show a standard text instead of the necessary action you could build a range to check and loop through all cells of the range and compare the value of the cell to the standard text and add 1 to a variable. If the variable is equal to the number of cells in the range the button could be activated.

Another way would be to get this as the starting part of the button and exit the sub if the information has not been entered (show a message to the user) while continuing if the necessary information has been entered.

HTH,
Holger

I am trying to figure out what to do with your suggestion. Is there any way of possibly providing a sample. Thanks for the help you have provided.

[pboost1]

in cell X10000 or what ever, add in a formula =IF(OR(A1="",D2="",G5=""),0,1). Change A1, D2, G5 to every cell that you need populated. Then add this to the VBA for that sheet, not "ThisWorkbook".

Please Login or Register  to view this content.

Hello, I copied your code, and adjusted it to where I need. I also have the button setup with a macro to print. I must have something wrong as the button prints regardless of what that value is.

Just to ask, am I suspose to have a macro for printing set in the button? Thanks for the help.

[HaHoBe]

Hi, pboost1,

maybe consider not to fully quote the posts you are answering to according to the forum rules.

Is there any way of possibly providing a sample.

The code here entereed into ThisWorkbook checks the cells A1, B2 and C3 and will allow printing only when they are filled:

Please Login or Register  to view this content.

Ciao,
Holger

[pboost1]

Hello HaHoHe, I copied your code to my workbook and it is still allowing me to print. I downloaded your spreadsheet and am not seeing a difference in what you wrote and what I copied. Am I doing something wrong? I will try to make a sample worksheet and upload it. Thanks again for the help.

[Arkadi]

Could try putting this code in the worksheet part of the macros. Go to VBA, double click the sheet you want to monitor, and paste this code. Change the contents of the "set range" to match your cells, and the button name as well.... This will hide the button for you.

Please Login or Register  to view this content.

[pboost1]

Here is a sample similiar to what I have at work

[Arkadi]

Assuming the Item fields need to be populated, the attached sheet might do what you want, it hides the print sheet button until all 5 items are filled.

EDIT: Just updated file with a method that will disable and enable without hiding...

[HaHoBe]

Hi, pboost1,

attaching a macro-free workbook may be showing that you didnīt insert a workbook level event into the workbook where it should be used.

Ciao,
Holger

[pboost1]

Thanks for everybodys help on this. With all that has been submitted, I belive I was able to get what I needed. I can only work on the work spreadsheet as work so I have limited time to play with the spreadsheet. I will leave this open for a couple of more days and if things work the way I want it to, then I will close it out. Thanks again;

[pboost1]

I am uploading a sample of what I was able to come up with. I cannot seem to get the visual basic to state the name of the information being requested instead of the cell number. Also, A slight change was made and now when a reason is needed on two cells, we need the drop down filled out and the reason (if aplpicable). Is it possible to have it state the name and not allow printing unless the reason is filled out as well.

[HaHoBe]

Hi, pboost1,

Also, A slight change was made and now when a reason is needed on two cells, we need the drop down filled out and the reason (if aplpicable)

How will you do so with merged cells?

Please Login or Register  to view this content.

Ciao,
Holger

[pboost1]

Thanks HaHoHe. what you have provided works great. The title show up for what is needed instead of the cell reference. Thanks a lot.

[HaHoBe]

Hi, pboost1,

as you have merged the columns what about an entry on different rows (at least that was what I understood from your post)? Does the code perform like you want?

Ciao,
Holger

[pboost1]

my boss would have liked that, but they were more concerned about having what needs to be filled out if anything is missing. Thanks again.