Mandate Cell on the basis of value in another cell

[Pal Po]

Hello,

I would like to mandate a comment in column D, if user selects a value of 1 (out of a drop down list of 1,2,3) in column C. For example, if the user selects 1 in column C5, then the user must not be able to move to next without filling in a comment in D5. Please help.


Thanks

 

[Joe4]

That could be a little tricky to do.

One option may be to not allow them to enter data directly into the worksheet at all, and instead create a user form, where they have to enter the information and then have VBA paste it into the Worksheet.
You can add any sort of validations that you want to the user form, so it only accepts/paste the data if all your criteria is met.

There are a bunch of tutorials on creating user forms that can be found with a Google Search. Here is one: https://trumpexcel.com/data-entry-form/

 

[Pal Po]

That could be a little tricky to do.

One option may be to not allow them to enter data directly into the worksheet at all, and instead create a user form, where they have to enter the information and then have VBA paste it into the Worksheet.
You can add any sort of validations that you want to the user form, so it only accepts/paste the data if all your criteria is met.

There are a bunch of tutorials on creating user forms that can be found with a Google Search. Here is one: https://trumpexcel.com/data-entry-form/

Thank you but i have to stick to a worksheet.

 

[Joe4]

I think any solution is going to involve VBA. I don't see a way to do what you want without VBA.

 

[Pal Po]

I think any solution is going to involve VBA. I don't see a way to do what you want without VBA.

I agree. I am struggling with the correct VBA code for this.

 

[Joe4]

There may be an easier way, but I think this should work.

Right-click on the sheet tab name at the bottom of the screen that you want to apply this to, select View Code, and paste this code in the resulting VB Editor window.
You can change the password in the code to whatever you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lrow As Long
    Dim cell As Range
    Dim dataChk As Boolean

'   Exit if more than one cell is updated at a time
    If Target.Count > 1 Then Exit Sub
    
'   See which column just updated
    Select Case Target.Column
'       For column C
        Case 3
'           See if value of 1 was entered in column C
            If Target.Value = 1 Then
'               See if column D is empty
                If Target.Offset(0, 1) = "" Then
'                   Lock all cells except column D
                    ActiveSheet.Unprotect Password:="test"
                    Cells.Locked = True
                    Target.Offset(0, 1).Locked = False
                    ActiveSheet.Protect Password:="test"
                    MsgBox "You must update column D before proceeding", vbOKOnly
                End If
            End If
'       For column D
        Case 4
'           Find last cell with data in column C
            lrow = Cells(Rows.Count, "C").End(xlUp).Row
'           Check to see if all values of column C have a value in column D
            dataChk = True
            For Each cell In Range("C1:C" & lrow)
                If cell.Value = 1 And cell.Offset(0, 1) = "" Then
                    dataChk = False
                    Exit For
                End If
            Next cell
'           Unlock sheet cells if no comments missing in column D
            If dataChk Then
                ActiveSheet.Unprotect Password:="test"
                Cells.Locked = False
                ActiveSheet.Protect Password:="test"
            End If
    End Select

End Sub

 

[Pal Po]

There may be an easier way, but I think this should work.

Right-click on the sheet tab name at the bottom of the screen that you want to apply this to, select View Code, and paste this code in the resulting VB Editor window.
You can change the password in the code to whatever you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lrow As Long
    Dim cell As Range
    Dim dataChk As Boolean

'   Exit if more than one cell is updated at a time
    If Target.Count > 1 Then Exit Sub
    
'   See which column just updated
    Select Case Target.Column
'       For column C
        Case 3
'           See if value of 1 was entered in column C
            If Target.Value = 1 Then
'               See if column D is empty
                If Target.Offset(0, 1) = "" Then
'                   Lock all cells except column D
                    ActiveSheet.Unprotect Password:="test"
                    Cells.Locked = True
                    Target.Offset(0, 1).Locked = False
                    ActiveSheet.Protect Password:="test"
                    MsgBox "You must update column D before proceeding", vbOKOnly
                End If
            End If
'       For column D
        Case 4
'           Find last cell with data in column C
            lrow = Cells(Rows.Count, "C").End(xlUp).Row
'           Check to see if all values of column C have a value in column D
            dataChk = True
            For Each cell In Range("C1:C" & lrow)
                If cell.Value = 1 And cell.Offset(0, 1) = "" Then
                    dataChk = False
                    Exit For
                End If
            Next cell
'           Unlock sheet cells if no comments missing in column D
            If dataChk Then
                ActiveSheet.Unprotect Password:="test"
                Cells.Locked = False
                ActiveSheet.Protect Password:="test"
            End If
    End Select

End Sub

Thank you so much. This works just fine.

 

[Joe4]

You are welcome.

The only way it wouldn't work is if they tried to update multiple rows in column C at the same time (i.e. through copy and paste).
Since you have them selecting value from a drop-down box, hopefully they will not try that.

 

[Pal Po]

You are welcome.

The only way it wouldn't work is if they tried to update multiple rows in column C at the same time (i.e. through copy and paste).
Since you have them selecting value from a drop-down box, hopefully they will not try that.

I can see some issues with the solution. Such as I can't change the value from 1 to 2 unless I enter a comment. Once I enter the comment, I can go back and delete it. But overall, it should work. The audience will get the idea that a comment must be entered for the value of"1".

Thanks again.

 

[Joe4]

We can handle that with one minor tweak. We can tell it to keep columns C and D unlocked, and if column C is changed to something other than 1, then unlock all other cells.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lrow As Long
    Dim cell As Range
    Dim dataChk As Boolean

'   Exit if more than one cell is updated at a time
    If Target.Count > 1 Then Exit Sub
    
'   See which column just updated
    Select Case Target.Column
'       For column C
        Case 3
'           See if value of 1 was entered in column C
            If Target.Value = 1 Then
'               See if column D is empty
                If Target.Offset(0, 1) = "" Then
'                   Lock all cells except column C and D
                    ActiveSheet.Unprotect Password:="test"
                    Cells.Locked = True
                    Target.Resize(1, 2).Locked = False
                    ActiveSheet.Protect Password:="test"
                    MsgBox "You must update column D before proceeding", vbOKOnly
                End If
            Else
'           Unlock code to run in case they change column C
                ActiveSheet.Unprotect Password:="test"
                Cells.Locked = False
                ActiveSheet.Protect Password:="test"
            End If
'       For column D
        Case 4
'           Find last cell with data in column C
            lrow = Cells(Rows.Count, "C").End(xlUp).Row
'           Check to see if all values of column C have a value in column D
            dataChk = True
            For Each cell In Range("C1:C" & lrow)
                If cell.Value = 1 And cell.Offset(0, 1) = "" Then
                    dataChk = False
                    Exit For
                End If
            Next cell
'           Unlock sheet cells if no comments missing in column D
            If dataChk Then
                ActiveSheet.Unprotect Password:="test"
                Cells.Locked = False
                ActiveSheet.Protect Password:="test"
            End If
    End Select

End Sub