Pop up message stating empty cells [Solved/Closed]

- Aug 2, 2010 at 03:34 PM - Latest reply:
Posts
5
Registration date
Thursday March 21, 2013
Last seen
April 29, 2013
- Mar 25, 2013 at 09:32 AM
Hello,

I am relatively new to VB and I am trying to help a friend build an application form for work. Basically what I am trying to do is, there are 6 cells which need to be filled out (name, address etc) in this form on excel before printing will be allowed. There is a 'print' button to the left of the form and when it is pressed it would be great to have the cells that should be filled out, to highlight and have a message box pop up stating that fields have not been completed. Does anyone know how to do this? It would be so awesome of you.

Benji

See more 

7 replies

Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- Aug 3, 2010 at 07:38 AM
0
Thank you
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like http://www.speedyshare.com/ , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
0
Thank you
Hello, thank you for getting back to me. I have attached the file in question and let me try to reiterate what it is I am trying to do.

The cells that are red, the grey button to the left and the 'Terms and Conditions' check box are all that need work on. I have made a start with the 'customers name' cell where if it is not filled out a message box pops up prompting the user to fill it in. One of the things I need help with is I need the same thing to happen in all cells in red, as they are mandatory fields. Oh and not forgetting the check box.

Without technical know how I tried the follow layout so when the user tries to print the form, if the fields are not complete it will not print and flag up message telling them to fill in the cell.

If Range("J7") = "" Then

If Range("J9") = "" Then

If Range("J11") = "" Then

If Range("H27") = "" Then

If Range("H30") = "" Then

If Range("H33") = "" Then

And also the tick box needs to be tick so not to generate a message box as well.

Once that is done, the button to the left of the form is what I'd like to initiate the code. So when the user presses print, any fields or the check box that are not filled in will flag up an error message.

Then finally, if all fields are completed then the form should print fine.

It is a basic task for someone who knows what they are doing and it will be a brilliant learning curve for me to see the final code. As you have probably been able to tell already, I have no tech jargon which could make it difficult of you to understand what I am wanting. Do ask if you need any info to help understand my requirements. If you are able to help it would be brilliant. Benji.

File download path:
http://www.speedyshare.com/files/23662893/Service_Receipts_-_for_Zoe.xls

Password:
jokafepovoni
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- Aug 4, 2010 at 02:58 PM
First for checkbox, use a different type of checkbox. If you right click on the "VIEW", "TOOLBARS" and choose "Control ToolBox". And then choose the checkbox

You would need to do some thing like this

    If Range("J7") = "" Then
        MsgBox "Missing J7"
        
    ElseIf Range("J9") = "" Then
        MsgBox "Missing J9"
    
    ElseIf Range("J11") = "" Then
        MsgBox "Missing J11"
    
    ElseIf Range("H27") = "" Then
        MsgBox "Missing H27"
    
    ElseIf Range("H30") = "" Then
        MsgBox "Missing H30"
    
    ElseIf Range("H33") = "" Then
        MsgBox "Missing H33"

    ElseIf ACTIVESHEET.CHECKBOX1.VALUE <> true Then
        MsgBox "Enable Checkbox"
    
    Else
        ' all is OK. code for print
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
    End If
That is amazing rizvisa1, I have done as you said and the form works... ITS ALIVE! thank you for that. Take care of your self
Posts
5
Registration date
Thursday March 21, 2013
Last seen
April 29, 2013
- Mar 21, 2013 at 12:33 PM
This code has been very helpful for me Rizvisa1.. Thanks so much for this one..
I need a similar code..
Consider I have cells from J7, to J15, and I have to print a msgbox, if all of them are blank,what would be the code.
Even if one cell from J7 to J15 is not blank, it is fine, but if all are blank, I need a msgbox.
Your reply will be highly valuable..

Thanks in advance.. :)

Can you please
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- Mar 21, 2013 at 01:09 PM
@shalom1506.
If you truly want to see if J7:j15 is not blank then you can use

   If (Application.WorksheetFunction.CountA(Range("J7:J15")) = 0) Then 
    MsgBox "No Data" 
   Else 
    MsgBox "Data" 
   End If
Posts
5
Registration date
Thursday March 21, 2013
Last seen
April 29, 2013
- Mar 25, 2013 at 09:32 AM
Thanks once more rizvisa1.. It works like a charm. Thanks a lot once again.. :)