Require User to Enter Value in Adjacent Cell if Option from Drop Down Box is Chosen

[MHau5]

Hello,

Say I have column A where the cells contain values from a drop down list. For this example, we will say that those values in the drop down list are "Option 1", "Option 2", and "Option 3". What I'd like to do is force the user to have to enter a value in column B if the user were to select "Option 1" from the drop down in column A. However, I'd also like to allow them to choose "Option 2" and "Option 3" from column A and NOT have to be forced to enter a value into column B.

Any ideas on how I can make this work?

Thanks in advance!

 

[offthelip]

Put this in the worksheet code for the sheet you want to force :

Dim Krow As Long

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("A:B")) Is Nothing) Then
If Target.Count = 1 Then
  If Target.Value = "Option1" Then
   Krow = Target.Row
   Range("B" & Krow).Select
  Else
   Krow = 0
  End If
Else
 If Krow <> 0 Then
  If Target.Row = Krow And Target.Value <> "" And Target.Column = 2 Then
   Krow = 0
  Else
   Range("B" & Krow).Select
  End If
End If
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Krow <> 0 Then
   Range("B" & Krow).Select
  End If
End Sub

 

[MHau5]

Put this in the worksheet code for the sheet you want to force :

Dim Krow As Long

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("A:B")) Is Nothing) Then
If Target.Count = 1 Then
  If Target.Value = "Option1" Then
   Krow = Target.Row
   Range("B" & Krow).Select
  Else
   Krow = 0
  End If
Else
 If Krow <> 0 Then
  If Target.Row = Krow And Target.Value <> "" And Target.Column = 2 Then
   Krow = 0
  Else
   Range("B" & Krow).Select
  End If
End If
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Krow <> 0 Then
   Range("B" & Krow).Select
  End If
End Sub

Thank you! This is great! How would I alter this code if I wanted to require the user to have to enter a value in column B if they also selected "Option 2"? Just trying to understand the ebs and flows of VBA (which I know nothing about).

 

[offthelip]

very simple more less like you say it: change the if statement to this:

If Target.Value = "Option1" Or Target.Value = "Option2" Then

 

[MHau5]

very simple more less like you say it: change the if statement to this:

If Target.Value = "Option1" Or Target.Value = "Option2" Then

Got it, that makes sense. If I wanted to include more in the function you wrote is it relatively simple? Say for example I have a third column called Column C, and I also want to force the user to enter a value in that cell if they choose "Option 1". Would I just adjust the range from A:C?

 

[offthelip]

It is not quite that simple because the code as written depends on the column you select Option1, 2 and 3 being adjacent to the column you want to force the user to make an entry in . This is exactly what you asked for.
If you want to change the columns from A and B to B and C that is very easy but the code does need changing in 6 places , However if you want change it to two columns that are not adjacent then it needs more radical changes. If you want change to something more complicated where it operates over three columns . e.g. option1 in columnA forces column B while option2 in column A forces columns C , then it gets a lot more complicated but possible