Force Cell entry based on specific condition

[JohnCollins]

Help Required please:

I have a tracker spread sheet whereby users are required to enter comments (text) when a specific RAG condition is met e.g.

If condition RAG <> "G" then user must enter a comment and the comment cannot be " " or blank.


Note: I have be able to apply a apply a simple cell conditional format to the cell, which changes the cell format to Red or Amber if the RAG status is "R" or "A", but as yet have not been able to find a way in which the user must enter a comment if RAG <> "G"?



Within my spreadsheet I have multiple worktabs (10 in total) with each tab representing a specific Business unit e.g. Sheet3 = Asset, Sheet4 = Premium etc ....

Within each sheet there are 30 row entries, commencing from L4 : L34, which requires that a comment is entered by the user if L4 <> “G”, whereby the comment cannot be blank or cell left empty.


Within each worksheet, the following cells can be found in exactly the same location (Row and Column) in each sheet whereby:
L4 = RAG Status (R,A,G)
N4 = User specific comments


The formula I have created below (should possibly work but being a novice I doubt it does) and only takes in to account one cell within the workbook, so, what would I need to do in order for it to:

1. Work for all 30 row entries on a Sheet.3 called Asset
   
2. Apply the formula to work over all remaining 10 sheets within the spreadsheet

Sub Comments()
Dim vVal

If (Sheet3.Range("L4" <> "G")) And IsEmpty(Sheet3.Range("N4")) Then

vVal = InputBox("Enter a Comment for N4")

If vVal = vbNullString Then Run "Comments"
End If
End Sub


-- removed inline image ---

 

[pjmorris]

Hi I think you have two approaches which will work well together:

1. Use conditional formating on the cells affected, say L4:N40, with this formula: =AND($L4<>"G",LEN(TRIM($N4))=0)
2. Use Data Validation in Column N with this formula: =LEN(TRIM(N4))<>0

Hope this helps.

Regards

 

[CalcSux78]

Here's a solution with code. You would need to enter it in VB on the sheet of each one that needs these parameters ran.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Commnt As Range
'=================================================
'   If entry within Rng is not blank or "G" then
'   lock all cells on worksheet except for cell
'   requiring a comment.  Once comment is entered
'   unlock worksheet.
'=================================================
Set Rng = Intersect(Target, Range("L4:L34"))
Set Commnt = Intersect(Target, Range("N4:N34"))
On Error Resume Next
If Not Rng Is Nothing Then
    Select Case Target
    Case "G", ""
        Exit Sub
    Case Else
        If Target.Offset(, 2).Value = "" Then
            With ActiveSheet
                .Unprotect
                '.Unprotect pswd
                .Rows("4:34").Locked = True
                Target.Offset(, 2).Locked = False
                'if sheet has password add after Protect
                '.Protect pswd
                .Protect
                .EnableSelection = xlUnlockedCells
            End With
        End If
    End Select
    Target.Offset(, 2).Select
End If
If Not Commnt Is Nothing Then
    If Target = "" Then
        Exit Sub
    Else        'comment entered, unlock sheet to allow navigation
        With ActiveSheet
            .Unprotect
            .Rows("4:34").Locked = False
        End With
    End If
End If
End Sub

 

[JohnCollins]

Help Required please:

I have a tracker spread sheet whereby users are required to enter comments (text) when a specific RAG condition is met e.g.

If condition RAG <> "G" then user must enter a comment and the comment cannot be " " or blank.


Note: I have be able to apply a apply a simple cell conditional format to the cell, which changes the cell format to Red or Amber if the RAG status is "R" or "A", but as yet have not been able to find a way in which the user must enter a comment if RAG <> "G"?



Within my spreadsheet I have multiple worktabs (10 in total) with each tab representing a specific Business unit e.g. Sheet3 = Asset, Sheet4 = Premium etc ....

Within each sheet there are 30 row entries, commencing from L4 : L34, which requires that a comment is entered by the user if L4 <> “G”, whereby the comment cannot be blank or cell left empty.


Within each worksheet, the following cells can be found in exactly the same location (Row and Column) in each sheet whereby:
L4 = RAG Status (R,A,G)
N4 = User specific comments


The formula I have created below (should possibly work but being a novice I doubt it does) and only takes in to account one cell within the workbook, so, what would I need to do in order for it to:

1. Work for all 30 row entries on a Sheet.3 called Asset
2. Apply the formula to work over all remaining 10 sheets within the spreadsheet

Sub Comments()
Dim vVal

If (Sheet3.Range("L4" <> "G")) And IsEmpty(Sheet3.Range("N4")) Then

vVal = InputBox("Enter a Comment for N4")

If vVal = vbNullString Then Run "Comments"
End If
End Sub


-- removed inline image ---

I should have been a little more specific in that:
1) The use enters something into column "N" i.e. a data value / number. The result of this automatically generates the "RAG" status of "R", "A" or "G"
2) Column "N" has a conditional format formulae that fill cell "N" with a red or amber if the RAG = "R", or "A"
3) However, How do I force the user to enter a comment that relates to the specific row i.e. N4 ?

 

[pjmorris]

Where do you want the comment? Note: the prinicipal outlined in my first post can be adapted to achieve your output. The idea is that you use Conditional Formating to highlight relevant cells when a comment is required, this is reinforced by having Data Validation in the comment cell that ensures something valid is entered - if the formula are correct this could be considered unnecessary because the Conditional Formating wont clear until the comment is correct.

 

[JohnCollins]

Peter,
I followed you instructions, but it failed to work. When the user enters the value in to "M4" and presses the return button, the cursor is taken down to M5 awaiting for input from the user. Cell "N4" is correctly formatted with a colour, highlighting the fact that the user needs to do something. However, the data validation check appears not to automatically work as the cursor is now at M5 enabling the user to re-enter the next data set. If the user highlights / selects cell M5, then they are able to enter data and the check is performed. If they do not, then they are able to save worksheet with no comments being entered.

 

[pjmorris]

where do you want the comment? in M4 or N4?

if in N4 the custom data validsation formula is: =LEN(TRIM(N4))<>0
if in M4 the custom data validsation formula is: =LEN(TRIM(M4))<>0

if the comment is in N4 then the Custom Format Formulas should be: =AND($L4<>"G",LEN(TRIM($N4))=0)
if the comment is in M4 then the Custom Format Formulas should be: =AND($L4<>"G",LEN(TRIM($M4))=0)

Hope this helps.

 

[JohnCollins]

Peter,
By default the spreadsheet is blank and upon opening all cells are locked down with the exception of M and N. Users, by default will enter the data in M and often not enter any comments. Thus, if the RAG <> "G" a comment is required to be entered. Note: Whilst column N will be filled in with a specific colour i.e. "RED" the system appears not to enforce the validation rule requesting the user to enter something.

Column: L M N
Line RAG Data Comment
4 R 20 <<User Must Enter Comment as RAG = "R" and derived by formula >>
5 G 2 <<No Comment Required to be entered a RAG = "G"
6 A 7 <<User Must Enter Comment as RAG = "A" and derived by formula

If you want will attach the sheet for you to review, thanks

 

[pjmorris]

John,

This is a works computer so I'd rather not receive the actual spreadsheet. From your post above I think you want to be able to enter Data in Column M and a comment is required in Column N if the RAG in Column L is not 'G'. Thus the data in column M is irrelevant in regards whether a comment is required (unless it automatically drives the RAG value). If I've understood this correctly then:

1. Click and drag from L4 to N30 if that is the region in which RAG & Comments will appear.
2. Select 'Conditional Formating' from the Styles Tab of the Home ribbon.
3. Click New Formatting Rule.
4. Click on 'Use a formula to determine which cells to format'.
5. In the 'Format Cells where this formula is true' box enter: =AND($L4<>"G",LEN(TRIM($N4))=0)
6. Set the format to whatever you want. Click OK,

7. Select the cells N4:N30.
8. Click on Data Validation in the Data Tools tab of the Data ribbon.
9. Under Allow, choose Custom.
10. In the Formula section enter: =LEN(TRIM(N4))<>0 Note that if the first cell of the range is N5 say, then this formula becomes =LEN(TRIM(N5))<>011. After entering any warning message on the Error Alert tab of the dialogue, click OK.

If this doesn't work I suggest you start with a clean workbook, do exactly as I've suggested above. Then try populating column L with RAG values and see what you can and can't do in Column N, which ought to help you debug your real sheet.

Hope this helps.

Regards (PS. Now time to go home! so can't help again until tomorrow)

 

[JohnCollins]

Hi CalcSux,

Thanks for the code, but as I'm in experience with using VB within Excel, could you advise as to exactly where this code should be placed. I can see all the various work sheets displayed and if I select the appropriate worksheet, do I just copy an save the code and save the spreadsheet with as an Xlm file ? If, so I have done this, but the code appears not to work as it does not appear to unlock the sheet or cells (N34) or force the user to enter a comment in to N34 if L34 = "R" or "A" ?