Restrictive Printing with VB

Schodoodles

New Member
Joined
Jan 25, 2005
Messages
17
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 :confused:

Thanks
Scho
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

KMKfan

Board Regular
Joined
Mar 8, 2004
Messages
106
If all the data is input in the same column.

Code:
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

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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.

Code:
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
 

Forum statistics

Threads
1,147,622
Messages
5,742,199
Members
423,711
Latest member
luisfreitas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top