Restrictive Printing with VB

[Schodoodles]

Hi,
I need to find a way to only print a sheet if certain data is inputted on the sheet, such as Name, address etc.
I have got a button set up at the bottom that prints out specifically what I want to print, and the number of copies I want - but how do I stop it from printing and a error message to come up if not all the fields are filled??
My data entry boxes include normall cells on the spreadsheet and VB textboxes.
Would this involve me verifying each bit/box in an IF THEN ELSE statement and printing on the THEN bit?
ege IF all boxes are full THEN Print it ELSE Msgbox etc etc

Thanks
Scho

 

[KMKfan]

If all the data is input in the same column.

Dim r As Integer
        Dim c As String
        r = '(your first row)
        Do Until r = '(your last row)
            c = "A" & r 'use your column instead of "A"
            If Range(c).Text = "" Then
'Call a message box or userform.
            End If
        r = r + 1
        Loop

You can either use a message box notifying the user that input is missing, o create a userform dialog box (may be a built in one in Excel that suits the need) that prompts the user to enter the missing data. Once the data is entered the page will print.

If the input is entered in varying columns, you could probably set up range names, and put the range names on another sheet in a single column and play with the above code a little to check if each range name has data in it.

 

[parry]

Hi Scho, you can loop through the textboxes and see if they are filled in or not. in fact you can stop at the offending one and set focus to it so the user knows to complete that one. For the cells why not use CountA against the total cells that should be completed. Thats far simpler than looping through all the cells to see if they are filled in.

Sub Example()
Dim Ctrl As OLEObject, blnEmpty As Boolean, Rng As Range

'See if any textboxes are empty
For Each Ctrl In ActiveSheet.OLEObjects
    If Ctrl.OLEType = 2 Then 'A textbox
        If Ctrl.Object.Text = "" Then
        MsgBox "Not all textboxes are completed!"
        Ctrl.Activate
        Exit Sub
        End If
    End If
Next Ctrl

'See if data is filled in for a particular range
Set Rng = Range("A1:A10") 'the cells you would like to look if they are completed

'use countA to find number of
If WorksheetFunction.CountA(Rng) <> Rng.Cells.Count Then
    MsgBox "Not all the cells in the range " & Rng.Address & " have been completed!"
    Exit Sub
End If

'All checks are finished so do print
ActiveSheet.PrintOut

End Sub