Don't want cells left blank when using Data validation

[trucker]

I am using Data Validation and I don't want the user to go past a cell without putting in an entry. When I leave the box (ignore blank) unchecked it doesn't do anything after I have protected the document.

Help

 

[Mark W.]

You could get someone to stand over them with a whip. :D

 

[Jaafar Tribak]

Hi Trucker,

Instead of using Data Validation let's try these Worksheet Event Procedures.

Right click the worksheet,choose View Code and paste the below code.

Note that I assume the cell is A1.

Public OLDVAL As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("A1") _
    And IsEmpty(Target) Then Target = OLDVAL
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    OLDVAL = Target.Value
End Sub

 

[trucker]

I tried that and that didn't seem to work either. my cell is c4 I changed that but still when I pull up the template as a new document it will let me tab past the "Corporate Name" field without inputing anything...

 

[shades]

Did you originally apply this VBA to the template itself, or to the document that appears after choosing the template. If the latter (which it sounds like to me), then you didn't make the change to the template itself.

 

[Jaafar Tribak]

Hi Trucker,

Right click the worksheet Tab,Choose View Code and Paste the Following Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address > [c4].Address Then
    If IsEmpty([c4]) Then
    MsgBox "Cell C4 Must Have A Value." _
    , vbCritical, "There Is A Problem!"
        [c4].Select
    End If
End If

End Sub

Hope this helps.

 

[Jaafar Tribak]

Hi Trucker,

Right click the worksheet Tab,Choose View Code and Paste the Following Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address > [c4].Address Then
    If IsEmpty([c4]) Then
    MsgBox "Cell C4 Must Have A Value." _
    , vbCritical, "There Is A Problem!"
        [c4].Select
    End If
End If

End Sub

Hope this helps.

 

[trucker]

I LOVE YOU!!!!!!!!! :P :P

 

[trucker]

Okay what do I do if I want to have this feature on a lot of cells?

 

[Jaafar Tribak]

Hi,

The below code works for all unlocked cells in a protected worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

For Each cl In Me.UsedRange.Cells
    If cl.Locked = False Then
        adr = cl.Address
        Exit For
    End If
Next

Application.ScreenUpdating = False
Application.EnableEvents = False

    If Not Target.Locked Then
        If IsEmpty(Target.Previous) Then
            If Target.Address <> adr Then
                Target.Previous.Select
            End If
        End If
    End If
Application.EnableEvents = True

End Sub

Hope this helps.

 

[ajphelps]

What if I want to apply this to multiple cells, not all unlocked cells. Some cells are ok to leave blank.

Hi,

The below code works for all unlocked cells in a protected worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

For Each cl In Me.UsedRange.Cells
    If cl.Locked = False Then
        adr = cl.Address
        Exit For
    End If
Next

Application.ScreenUpdating = False
Application.EnableEvents = False

    If Not Target.Locked Then
        If IsEmpty(Target.Previous) Then
            If Target.Address <> adr Then
                Target.Previous.Select
            End If
        End If
    End If
Application.EnableEvents = True

End Sub

Hope this helps.