Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2406
  • Last Modified:

How do I create a mandatory field based on a condition

In Microsoft Excel I would like to make a mandatory field "A2" based on the condition of another field "A1". For instance when I select the value 'yes' from a drop down in A1, I want to make the next field "A2" mandatory and it must be a date format. I've found the following code, but I don't know how to modify to the above process.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Range("A1").Value = "" Then
   Range("A1").Select
   MsgBox ("You must enter a value for Cell A1")
 End If
End Sub

Any help is greatly appreciated
0
Boombox69
Asked:
Boombox69
  • 2
2 Solutions
 
Rory ArchibaldCommented:
Something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Range("A1").Value = "yes" and Range("A2").Value = "" Then
   on error resume next
   Application.EnableEvents = False
    Range("A2").Select
   Application.enableevents = true
   MsgBox ("You must enter a value for Cell A2")
 End If
End Sub

Regards,
Rory
0
 
jeveristCommented:
Hi Boombox69,

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c1 As Range, c2 As Range

Application.EnableEvents = False

Set c1 = Me.Range("A1")
Set c2 = Me.Range("A2")

If Intersect(Target, c2) Is Nothing Then
    If LCase(c1.Value) = "yes" And IsEmpty(c2) Then
      MsgBox "You must enter a value for Cell: " & Replace(c2.Address, "$", "")
      Application.Goto c2
    End If
End If

Application.EnableEvents = True

End Sub

Jim
0
 
Boombox69Author Commented:
You guy's are awesome. I'm sorry I had to split the points.
0
 
Rory ArchibaldCommented:
That's exactly what you should do - thanks for the points. :)
Rory
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now