PDA

View Full Version : FORCE TO POPULATE CELL



stevembe
03-11-2014, 07:11 AM
Thanks for taking the time to look.

I have two columns, one labelled Current (A1) and the other labelled Proposed (B1). What I am trying to achieve in the columns underneath is force the user to enter data in the Current column if they have entered data in the Proposed column as quite often they leave it blank so basically if the right cell is populated then the one to the left needs to be and if it isn’t a pop up message appears telling them they need to.

Any assistance would be greatly appreciated.

Kenneth Hobs
03-11-2014, 07:34 AM
Right click the sheet's tab, View > Code, and paste.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Cells.Count > 1 Then Exit Sub
If .Row = 1 Then Exit Sub
Select Case .Column
Case 1
If IsEmpty(Range("B" & .Row)) Then
Range("B" & .Row).Select
MsgBox "Data is required."
End If
Case 2
If IsEmpty(Range("A" & .Row)) Then
Range("A" & .Row).Select
MsgBox "Data is required."
End If
Case Else
End Select
End With
End Sub

snb
03-11-2014, 08:57 AM
or


Private Sub Worksheet_Change(ByVal Target As Range)
ScrollArea = ""

With Target
If .Count > 1 Then Exit Sub
If .Row = 1 Or .Column > 2 Then Exit Sub

If .Offset(, 1 + 2 * (.Column = 2)) = "" Then ScrollArea = .Offset(, 1 + 2 * (.Column = 2)).Address
End With
End Sub