Denote User Entry Cells

[Danny99]

I have multiple spreadsheets in a single workbook, each of which when completed by the user, will become final documents for review either by priniting or by viewing within excel. Cells requiring user input can be surrounded by auto populated cells based upon the user inputs. I am looking for a way to denote the cells that require user entry. However, I do not want to use a cell highlight as the highlight will appear in the printed final copy. Also thought about conditional formatting, but if the sheets are updated, then the cells will already be populated. I tried placing a comment within in each (with no comment text) as a way to note cells users must fill in. This does not look too bad, but would like to review other options.

Thanks in advance,

Danny

 

[njimack]

I do not want to use a cell highlight as the highlight will appear in the printed final copy

Not if you go to File>Page Setup>Sheet>Print Black & White. however, this isn't an option if certain parts of your file SHOULD be printed in colour.

An alternative might be to draw rectangles around the user-input cells, then goto Format Autoshape>Properties, and untick Print Object.

 

[Sumeluar]

How about...

Sub Incomplete()

On Error Resume Next
For Each sht In Worksheets
Sheets(sht.Name).Name = Sheets(sht.Name).Cells(3, 6)

Next sht

Dim ChkRng As Range, c As Range

    'With Sheets("your Sheet name here")  'Change to the sheet of choice
    With ActiveSheet
    Set ChkRng = .Range("R6,R7,R8,R9,R10,R11,R12,R13,R14,R15,R20,R26,R27,R28,R29,R30,R31,R32,R33,R34,R35,R44") 'Change to the range of choice
    
    For Each c In ChkRng
    If IsEmpty(c) Then
    Cancel = True
    
    ActiveSheet.Select
    'Sheets("your Sheet name here").Select  'Has to match the sheet of your choice above
    c.Select
    
    'MsgBox ("All cells in the range " & ChkRng.Address(0, 0) & " must have an entry before saving."), , "Action cancelled"
    MsgBox ("The formis not complete." & " Please check and make sure that all the required data has been entered."), vbInformation, "FormDelivery Cancellation"

    Exit For
    End If
    Next c
    End With

    Range("P3").Select
    
End Sub

I hope this helps you.

Regards!
Sumeluar

 

[Danny99]

Neil - Thanks, but the printed output will have colors, and there are a few objects in some of the sheets, so I need to have print objects enabled.

Sumeluar - Thanks, but am only a novice in vba and really need it to be "canned" for me to try. I will give it a try! Only issue I forsee is that there a few free text fields, where the user may not use each line.

Danny

 

[Smitty]

Well,

You can use worksheet protection to limit entry to just those cells, an added benefit is that then there's a quasi-tab order as well.

In the past I have used CF (Value = "") to color cells, and when there's data the shading disappears. Even a trained monkey can get that one.

Finally, you can use the Data-->Validation Input Message to let users know what to enter. You can also set the types of things that can be entered.

Hope that helps,

Smitty

 

[njimack]

there are a few objects in some of the sheets, so I need to have print objects enabled.

If you right-click a shape, you can set the properties for that shape only.